Solved

connection control/passive shutdown Access 2000 and forward

Posted on 2004-10-25
541 Views
Last Modified: 2008-01-09
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
0
Question by:LJG
    6 Comments
     
    LVL 18

    Expert Comment

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

    Mike
    0
     
    LVL 2

    Expert Comment

    by:NArentz
    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
     
    LVL 2

    Author Comment

    by:LJG
    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
     
    LVL 2

    Expert Comment

    by:NArentz
    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
     
    LVL 2

    Accepted Solution

    by:
    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
     
    LVL 2

    Author Comment

    by:LJG
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: MongoDB Object-Document Mapper for NodeJS

    NodeJS (JavaScript on the server) is awesome, but some developers get confused about NoSQL when it comes to working in Node with MongoDB (NoSQL database). Do you need a better explanation of how to use Node.js with MongoDB? The most popular choice is the Mongoose library.

    In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    846 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    3 Experts available now in Live!

    Get 1:1 Help Now