working with MsAccess in multiuser environment: error Cannot start

[KT1972]

Hi,

We are using an access database updated by vba in an Excel form flowing
between users. Some of the users have update authority and some of them have
read-only authority. Normally system works well but sometimes it locks the
MSAccess for update. I couldn’t find the reason. Program which adds/deletes
the records 10 second ago starts to give error message: â€Cannot start your
application. The workgroup information file is missing or opened exclusively
by another userâ€. No one has authority to enter design view of
tables/forms/reports except Admin.

Following is the code to connect to the MSAccess:

Dim dbs As DAO.Database
Dim wrkSec As Workspace
Dim rst As DAO.Recordset
DBEngine.SystemDB = “C:\AccessAuthorityFile\SYSTEM.MDWâ€
Set wrkSec = DBEngine.CreateWorkspace("", user, pwd, dbUseJet)
‘ERROR message on this line
Set dbs = wrkSec.OpenDatabase(MSAccessFile)

Help please.

 

[Patrick Molloy]

switch from DAO to ADO
set the recordset open to forward only if yuo just want to read, and set
the connection object = NOTHING as soon as the recordset has the data.
If you read data dynamically you can put a lock on the table .. the database
won't allow updates if somebody is reading data...ie there will be a lock on
the table