Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

connection control/passive shutdown Access 2000 and forward

Posted on 2004-10-25
6
Medium Priority
?
614 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
Comment
Question by:LJG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:Data-Man
ID: 12405912
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
ID: 12408081
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
ID: 12410669
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 2

Expert Comment

by:NArentz
ID: 12417037
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:
NArentz earned 1000 total points
ID: 12418825
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
ID: 12421901
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

609 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