How can I open an Access DB via ADO so that I can write, but others can only read?

Question

From the documentation, I would expect adModeShareDenyWrite to be the way, but it's not working right.

I'm using an Access database via ADO. My connection string says Mode=8, which is adModeShareDenyWrite. But when I try to delete a row from a table, I get:

Unspecified error, Description:Could not delete from specified tables., Source:Microsoft JET Database Engine

In other words, the setting is preventing ME from updating the database using my OWN connection.

I found a couple other posts on the web reporting the same thing, the adModeShareDenyWrite setting used with Access not working as documented.

I am looking for a solution that doesn't involve an administrator changing permissions. It needs to be something that my program can control.

My motivation here is to minimize the chances of database corruption. One of the causes of mdb file corruption documented by Microsoft is two apps writing to the same db. So, I want to make sure that only one app can have a write connection to the db. Others can read, but should fail when they try to write. Whoever makes a connection first wins.

Answer 1

One solution is to give them access to a copy of the database. They can change whatever they want, but it won't keep past your copying it over with the master.

Answer 2

I suppose you access here an MDB file from a client interface, whatever it is, and others can also connect to the same file at the same time. When you use adModeShareDenyWrite in your connection mode, it means that you can still share the data with others (no locks of any kind on tables or records in the MDB file) but it can't be modified (this is why you get an error).

One solution would be to manage your connection parameters, with something like that:

(where you have a user object with a '.role' property, or anything equivalent ...)
if activeUser.role = "admin" then
    m_connectionMode = adModeWrite
else
    m_connectionMode = adModeShareDenyWrite
endif

Then you can open your ADO connection with the parameter m_connectionMode. Administrators will be given the right to insert/update/delete while other users will ony be able to view the data. This means you have somewhere in your program, or ideally in a table, some data saying who is what in your application.

EDIT: Following multiples comments with Corey:

You won't be able to do what you want to do in a straight way. My proposal: when the app accesses the database, it checks for a special file in the .mdb folder (whatever the file is).

If this file exists, the app opens a "read-only" connection.

If this file does not exist, the app creates the file (you can create one for example with "transferDatabase") and open a read-write connection. Once you quit the app, destroy the file.

Answer 3

Corey Trager wrote:

I am looking for a solution that doesn't involve an administrator changing permissions. It needs to be something that my program can control.

Well, if the problem is due to NTFS permissions being read-only for the user, there isn't a thing you can do to make the MDB writable. You don't specify where the MDB is stored, on a server or on a local hard drive, but in either case, for a user to have WRITE permissions on the MDB, NTFS permissions have to be set to allow it (for a share on a server, it has to be allowed both on the SHARE and on the underlying file). If it's a local file, the best solution is to make sure that you're storing the file in a location in which user-level logons have full WRITE permission. This would be anywhere in the user profile, and just about nowhere else.

That said, I'm not really suggesting that this is the source of your problem (I really can't say one way or the other), just pointing out that if it is the cause, then there's not a damned thing you can do programmatically to work around it.

--
David W. Fenton
David Fenton Associates

Answer 4

Cory Trager wrote:

My motivation here is to minimize the chances of database corruption. One of the causes of mdb file corruption documented by Microsoft is two apps writing to the same db. So, I want to make sure that only one app can have a write connection to the db. Others can read, but should fail when they try to write. Whoever makes a connection first wins.

Why are you worrying about it? Jet is by default a multi-user database engine. If somebody else is updating a table, the data pages involved will be locked as read-only (in the state they were before the write began).

There is no realistic reason to fear corruption from mere multi-user interaction. Corruption of Jet databases usually happens because of dropped connections or an interruption of the connection during a write (such as users who force quit an app that is not responding as fast as they want).

I think your fear of corruption is misplaced.

On the other hand, you should still be able to open with an exclusive lock, and I'm not sure why it's not working. Have you considered using DAO instead of ADO to manipulate Jet data? Given that it's the native data interface (instead of a generic interface layer), it ought to be easier.

--
David W. Fenton
David Fenton Associates

Answer 5

If you have multiple users connecting to an access database across a network you might want to consider upgrading to SqlServer instead of using Access.