Exclusive open mode

[codeman]

Hi,

I wrote an application that is split in aan front end MDE and a
back end MDB for the data. The behaviour i want to establish is that
both the MDE and MDB are opened exclusive. After a user has opened the
MDE no other user can open the MDE at the same time. The MDE uses
attached tables from the MDB. The MDE should open the data MDB
exclusive so no other user can open the MDB while the attached tables
are used by the MDE.

The restrictions are that the solution has to work regardles the
following settings or open methods:
- if a /excl argument is used in the command line or not
- the MS Access option "Default Open mode for Databases" is set to
Exclusive or not
- the user opened the MDE using Open Exclusive or Normal in the Open
dialog

In other words, a user starts the MDE that uses attached tables from
the MDB. After that no other user can open the MDE or MDB while the
first user is stil using the MDE, regardles the local MS Access
settings mentioned above. The MDE should enforce Exlusive open mode on
itself and on the attached MDB.

How can this be done?

Thanx

The Codeman

 

[Wayne Morgan]

One thing I can think of would be to open a recordset of a table in the
back-end (i.e. linked table) in your startup code. Set the lock parameter to
dbDenyRead+dbDenyWrite. When the next user opens the mde, the startup code
will try to open the recordset and will be denied. Use the error as you're
trigger to close the mde file.

Example:
Public Function TestLock()
Dim rst1 As DAO.Recordset, rst2 As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
Set rst1 = db.OpenRecordset("Table1", dbOpenDynaset, dbDenyRead +
dbDenyWrite)
Set rst2 = db.OpenRecordset("Table1")
On Error Resume Next
rst1.Close
rst2.Close
Set rst2 = Nothing
Set rst1 = Nothing
Set db = Nothing
End Function

The "Set rst2=" line will generate error # 3008, "The table 'Table1' is
already opened exclusively by another user...."