connection control/passive shutdown Access 2000 and forward

I'm trying to use  Access 2002 (with an Access 2000 database format) and make it so no one else can log on.  I'm using the "Connection Control" that micosoft talks about, but it doesn't work for me.  
I run the code below, and after running it, I can go to another computer, and open M:\Access10\OrderTrack_Data.mdb and open tables with no problems.  

Any ideas would be greatly appreciated.
Thanks LJG


Public Function axa()
    Dim cn As New ADODB.Connection  

    cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cn.Open "Data Source=M:\Access10\OrderTrack_Data.mdb"

   ' Restrict other users from opening the database  Passive Shutdown   =    1  Normal = 2
     cn.Properties("Jet OLEDB:Connection Control") = 1
   
    Debug.Print cn.Properties("Jet OLEDB:Connection Control")
    cn.Close
 
End Function
LVL 2
LJGAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Data-ManCOOCommented:
Could you just set the open exclusive option (Tools | Options).  This will stop someone else from opening the database.

Mike
0
NArentzCommented:
Hi,

The exclusive option will only prevent others from opening the database while you have it open. After you close it they will be able to open it. The simplest way to prevent others from using your database is to put a password on it - Tools - Security - Set Database Password. If you have a front end/back end set up, you will need to password both databases and relink the tables using the back end password.

The code you are trying to use needs to be set for the current connection, to take effect. You are setting the property for a new connection and then closing the connection but you are still left with your current connection. You will need to call this code when opening the database. Please remember that when connection control is invoked, users who are currently connected to the database remain unaffected until they disconnect. So this could mean that someone else locks you out of the database if they open it first! The database password may be a better option for you.

Here is the code to use the current connection.

Public Function axa()
   
   ' Restrict other users from opening the database  Passive Shutdown = 1,  Revoke = 2
     CurrentProject.Connection.Properties("Jet OLEDB:Connection Control") = 1
   
    Debug.Print CurrentProject.Connection.Properties("Jet OLEDB:Connection Control")
 
End Function

Cheers,

NArentz
0
LJGAuthor Commented:
I'm sorry I was not clear.  I have multiple front ends connected to one backend.  When I want to compact/Repair the database, I want to stop any new user from logging on to the backend.  The perfect process would go as follows:
1) Lock new users out of the data database.
2) Check who is in the data database (get them out if necessary)
3) Compact/Repair the data database.
4) Allow all users to get in.

I have #2 & #3 working fine with a push of a button, but I would like to get #1 & #4 working.

NArentz, if I understand what you are saying I have to set code to do it.
1) Microsoft says:  http://support.microsoft.com/default.aspx?scid=kb;en-us;198756  
”The connection control (also known as passive shutdown) feature prevents users from connecting to a database. This capability is useful for a database administrator who needs to acquire exclusive access to a database to perform maintenance, for example, compacting the database, or who needs to make updates to the database schema or applications.”

“When connection control is invoked, users currently connected to a database will remain unaffected until the disconnect. At that point, they are unable to reconnect until connection control is revoked.”

2) Even if the Connection Control doesn't work the way Microsoft says, I can't get your code to work.  If I lock the backend (on the file server) by means of my code and open the a front end on another machine and run the same code without the locking line and read the status I get #2 .  This indicates it's unlocked.
Debug.Print cn.Properties("Jet OLEDB:Connection Control")

Any ideas would be greatly appreciated.
LJG
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

NArentzCommented:
Hi again,

OK I understand better now what you are trying to achieve. I have just refreshed my memory from the Access Enterprise Developers Handbook (worth their weight in gold!). What you are doing will only work if the users are opening the same front end file. If each user has their own copy of the front end file (as they should) then this will not work.

A solution for a situation like yours is: the front end file has a hidden form that opens when the database does and periodically checks a value in a table in the back end. When the value in the back end is set to a particular value the user is asked to stop work and the database can be closed via code. This back end table value can also be used when the database opens to prevent the users from using the database when maintenance is underway. The users can then be allowed back in when the value is set back to allow access.

Hope this helps,

NArentz
0
NArentzCommented:
I have managed to find the instructions for the hidden form solution. Here they are (courtesy of ourworld.compuserve.com):

Access database using a hidden startup form as described in the following steps:

1. Create a small form with no controls on it. Leave the form as sizeable, non-popup, non-modal form.
2. Set the TimerInterval property to 300000 to cause the OnTimer event to fire every 5 minutes (you can lengthen or shorten this interval as necessary).
3. Create a table in your server/back end db, which only the administrator can write to, but every user can read (with an Access back end make the table hidden for safety). Create only one field called LogOff with a yes/no datatype. Add a value of No for the first and only record that will exist in this table.
4. In the OnTimer property of the form discussed earlier, add code which will query the value of your Log Off table value, and issue a message to the user to log off. For more advanced capabilities you can make your code warn the user first and then if the user doesn't log off, automatically log them off.

DAO timer code example:

Private Sub Form_Timer()
     Static MsgSent as Integer
     Dim LogOff as Integer
     Dim Db as Database
     Dim LO as DAO.Recordset

     Set Db = CurrentDB()  'Note the table must be attached 'for CurrentDb() to work or use a connect string to the server db.
     Set LO = Db.OpenRecordset("tblLogOff", DB_OPEN_SNAPSHOT)
     LO.MoveFirst
     LogOff = LO!LogOff
     LO.Close
     Db.Close

     If LogOff = True Then
        If Not MsgSent Then
        MsgBox "The Application will be shutting down in one (1) minute for maintenance, please log off immediately."
        Me.TimerInterval = 60000 'change to one minute
        MsgSent = True
        Else
          Application.Quit  'Log them off now.
        End If
    End If
End Sub

ADO timer code example:

Private Sub Form_Timer()
     Static MsgSent as Integer
     Dim LogOff As Integer
     Dim LO as ADODB.Recordset

     Set LO = New ADODB.Recordset
     LO.Open "tblLogOff", CurrentProject.Connection, adOpenStatic, adLockReadOnly
     LO.MoveFirst
     LogOff = LO!LogOff
     LO.Close
     Set LO = Nothing

     If LogOff = True Then
        If Not MsgSent Then
        MsgBox "The Application will be shutting down in one (1) minute for maintenance, please log off immediately."
        Me.TimerInterval = 60000 'change to one minute
        MsgSent = True
        Else
          Application.Quit  'Log them off now.
        End If
    End If
End Sub

5. When you start the application, add a command to your autoexec macro to open this form in a hidden status.

There are a few options in implementing this method which you may want to consider:

1. Rather than checking for a value in a table in the server/back end db, you may want to have your timer event check for the existance of a small text file which the administrator would place in a specified directory on the server. This has an advantage of not requiring the client app to query the server db in the event that the server/back end db has somehow become corrupt and the query to the LogOff table fails. You can simply use the Access Dir() command to check for the existance of this file.

2. Rather than using a message box to issue the message to log off you may want to create a second message "form" which you pop up and close after a short period of time automatically using its timer code. The advantage to this is that message boxes are  modal windows and they cause your code to suspend until the user closes them. So if the user is away from their workstation when the "Log Off" command is issue, if they don't click on the the message box button, the code never continues.

3. Add a function, similar to the Log Off function from the timer event code behind the form, to a general db module so that you can run the code from your autoexec macro, prior to reattaching tables, in order to query the value of log off when the user attempts to start the database. This could eliminate their ability to attach to the server/back end data while the maintenance is be run.

Cheers,

NArentz
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LJGAuthor Commented:
NArentz
Thanks so much for your answer.  I didn't  understand that the code was presuming everyone was using the same front end.  I spent a couple of hours ont it.  The form setup will work and I will implement it in the future.  I sure appreciate all the work you went to for me.

For right now, I'm checking to see if anyone is on the database, if not renaming the backend, copy the backend for backup, and then repairing/compacting the back end.  By renaming the backend, noone can log on, but your solution is definetly better, and I'll implement it.

Thanks so much
Larry Gordon
Business & Computers.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.