Problem accessing data from open database

[scottb777]

I have two MS Access 2003 databases on the same server but in separate shared folders.
I’m attempting to setup up a repetitive process that will access all fields and records from a table in MS Access database “B” in order make a table in MS Access database “A”. The user that will perform this process has write access to database “A” but read-only access to database “B”. Security is set at the folder level - there is no security setup at the database level. Both databases are set to “Shared” (not “Exclusive”).
If someone else has database “B” open and my user runs a make table query in database “A” that references a link to the table in database “B”, an error occurs stating that the user “Could not use database “B”; file already in use” (error code 3045). Is there a way to accomplish this without providing my user write access to database “B”?

[Stewart Ross]

Sorry but there isn't a way to open the database in shared mode if a user has it open read-only that I know of.

The problem is that if a user's rights are read-only then Access cannot create a lock file (a .ldb file) for that database. This leads to the read-only user being treated as having exclusive access to the database, preventing others from opening the database in shared mode.

You will need to provide read-write access to resolve this. If you don't want your currently read-only user to have access to the tables and so on you'll need to separate the front-end (user-oriented) parts of the database from the back-end tables and ensure in the new front-end that you remove the default access to the database window and the standard menus which allow users to open the database window if it is closed. This is not entirely foolproof, but it can be acceptably secure for most purposes.

-Stewart

[scottb777]

Thanks Stewart. We are going to move the table (and a few others) to a SQL database that can be accessed by both MS Access databases.