database exclusive use by Admin

[Souris]

I have Excel application to access MS Access database on open work book using
following code

Dim MyPath As String
Dim MyConnection As String
Dim MySQL As String

MyPath = "D:\MY DB\MYDB.mdb"

MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
MyConnection = MyConnection & "Data Source=" & MyPath & ";"


MySQL = "SELECT COUNT(*) FROM tblMyTable WHERE USER_ID = " & """" & 1111 &
""""

Set MyDatabase = CreateObject("adodb.recordset")
MyDatabase.Open MySQL, MyConnection, 0, 1, 1

It seems the database is opened by some one.
Are there any way to check the database is used by some one and opens as
another instance.

Your inforamtion is great appreciated,

 

[Douglas J. Steele]

Do you (or whoever's running the code) have the appropriate permissions on
the folder where the MDB file exists? Even if you're only trying to read
data, you still need Create, Read and Write permissions on the folder (and
should probably have Delete permission as well).

 

[Souris]

Thanks for the information,

Yes, the user has permissin.
It seems that the second person to access the server mdb got message,
I see ldb file exists.

Thanks again,

 

[Douglas J. Steele]

I've heard of permissions being set up incorrectly so that while all the
users have Create permission (so that they can create the ldb file), they
are unable to update files created by someone else.

 

[Souris]

Even user indirect to access mdb file on the server?

There is a mdb file on the user machine which user only has read rights.
User uses the mdb file on the local machine to access mdb file on the server
which user has the full rights for the mdb on the server.

Will it create ldb file to loclk mdb on the server?
Are there any work around for this?

So the user MUST have read, write and delete rights to access all MDB files
to avoid lock MDB files?


Your help is great appreciated,

 

[Douglas J. Steele]

Any attempt to read data from the MDB will cause Access to try to create the
locking file. You cannot control where the locking file will be located: it
goes in the same folder as the MDB.

Opening the read-only mdb file on the user machine will attempt to create a
locking file in that folder on the user machine. When that mdb attempts to
retrieve data from the mdb on the server, it will attempt to create a
locking file on the server (assuming one doesn't already exist), or it will
attempt to update the locking file on the server (if it already exists)

As far as I know, the only exception to this is if you explicitly open the
MDB as Read-Only and Exclusive. Then, and only then, Access doesn't bother
creating the locking file.