Getting a shared database to always open as exclusive 2

[Abi1]

My database is on a shared drive, and I need to ensure that only one person can use it at at time. There is an /excl switch I am supposed to be able to put in the command line, but it doesn't seem to make any difference.
Is there a database property I can alter so that it always opens as exclusive, no matter what the users default is?

Thanks in advance,

Abi

 

[Terpsfan]

Other than explicitly checking the Exclusive box when doing a File ---> Open within Access, the only other way I know of is this code:

DBEngine.OpenDatabase("databasename.mdb",True)
True signifying that the database is to be opened in exclusive mode.

 

[tlbroadbent]

Abi,

Access startup options don't work if you are not specifically executing Access on the command line. Sometimes we start Access by opening the database by double-clicking it and can do essentially the same thing in a shortcut. However, when using this method, Access will not recognize command line options.

Instead of this:
c:\data\mydatabase.mdb /excl

Use this in the shortcut:
c:\path\msaccess.exe c:\data\mydatabase.mdb /excl

Make sure you change "path" to the correct path for MSACCESS.EXE. I realize you may be doing this already but thought it was worth mentioning. Terry
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Abi1]

Thanks for the replies, but I can't get either method to work!

For the code method, where do I put the code? I've tried in a module in a new database, but it gives an error. The box that appears under the code as you type suggests putting As Database at the end (this gives an error too).

For the command-line method, it says there is an invalid
path. The other problem I may have with this method is that the database is on a shared drive, but each user has Access on their machines. There are several versions of Access in use too, and they may not all have msaccess.exe in the same path.

Help!

 

[tlbroadbent]

If you use the command line function, you'll need to verify the correct path on each user machine. Personalization is the only way around the problem. You could use a setup program to create the shortcuts on each machine.

I've devised a way to open the database exclusively using code but I'm not sure it will work if your site uses various versions of Access. I'll post the code here and you can use it if you want.

The code works in Access 2000. If you use an earlier version, you will need to modify the code.

Basically, the functions determine if the database is open in EXCLUSIVE mode. If not, the code determines the path to the Access program and starts Access, opening the database in exclusive mode.

Create the following functions in a VBA module. Add a call to the function OpenDatabaseExclusive() in your startup form On Load event or in a macro (AutoExec?) that executes upon opening the database. Let me know how it works if you use it.

Function IsCurDBExclusive() As Integer
'This function comes from Microsoft at

http://support.microsoft.com/support/kb/articles/Q210/3/59.ASP

'The access 97 version is at

http://support.microsoft.com/support/kb/articles/Q117/5/39.asp

'It requires the Microsoft DAO 3.6 Object Library. From VB, check references and make sure the DAO 3.6 reference is checked ON.

'Purpose: Determine if the current database is open exclusively.
'Returns: 0 if database is not open exclusively.
' -1 if database is open exclusively.
' Err if any error condition is detected.

Dim db As DAO.Database
Dim hFile As Integer
hFile = FreeFile

Set db = CurrentDb
If Dir$(db.Name) <> &quot;&quot; Then
On Error Resume Next
Open db.Name For Binary Access Read Write Shared As hFile
Select Case Err
Case 0
IsCurDBExclusive = False
Case 70
IsCurDBExclusive = True
Case Else
IsCurDBExclusive = Err
End Select
Close hFile
On Error GoTo 0
Else
MsgBox &quot;Couldn't find &quot; & db.Name & &quot;.&quot;
End If
End Function

Function OpenDatabaseExclusive()
Dim AccPath As String, rc As Double, dbPath As String
Dim db As DAO.Database

If IsCurDBExclusive() = False Then
Set db = CurrentDb
dbPath = db.Name
db.Close
AccPath = SysCmd(acSysCmdAccessDir) & &quot;msaccess.exe &quot;
rc = Shell(AccPath & dbPath & &quot; /excl&quot;, vbNormalFocus)
Quit
End If
End Function

------------------------------------------
Another option would be to have every user open another database (StartUp.mdb?) with only one function in it - no tables, forms, etc. Include an AutoExec macro to execute the function. The function would be similar to the OpenDatabaseExclusive above. The test for exclusive use is removed.

When users open the StartUp.MDB, the code would run, determine the correct paths and start Access to open the production datbase exclusively.

Function OpenDatabaseExclusive()
Dim AccPath As String, rc As Double, dbPath As String
Dim db As DAO.Database

Set db = CurrentDb
dbPath = db.Name
db.Close
AccPath = SysCmd(acSysCmdAccessDir) & &quot;msaccess.exe &quot;
rc = Shell(AccPath & dbPath & &quot; /excl&quot;, vbNormalFocus)
Quit

End Function Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin