Excel VBA: Access database engine cannot open or write to the file

Within Excel I have created a macro that runs a query from in an Access db and copy's the data to spreadsheet.
The user does not have write permission for the access db since I don't want them to change the query.

when running the macro I see this error
Run-time error '3051':
The Microsoft Access database engine cannot open or write to the file '<DB LOCATION>'. It is already opened exclusively by another user, or you need permission to view and write its data.

Now the clue is in the error, the user needs write permission.
Is there any way I can run run the query and copy the results to excel without granting the user write permission to the Access db file?

Many thanks in advance!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
That message is probably related to the user not having write permissions in the folder where the Access database resides.  In order to run the query, Access has to open in the background, and when it does so, it has to either create the .laccdb or .ldb file or write to that file, and the user must have permissions to the folder where Access is installed to do this.
antoniokingAuthor Commented:
Hi Dale
The user has write access to the folder, just not the DB file.
I've also tried changing the DB file to read only and granting the user write access. Of course this doesn't work either.
Dale FyeOwner, Developing Solutions LLCCommented:
What version of Access are you using?  Are you using workgroup security from 2003?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

antoniokingAuthor Commented:
Hi Dale
thanks for taking the time to help me out

We're using Office 2010, the access file is accdb format (2007 I believe)
Dale FyeOwner, Developing Solutions LLCCommented:
Then how are you restricting the users access to the database?  Have you added a password?

Is the data in the database pretty dynamic (changes frequently) or static?  If static, would creating a table in an external database, one that you can give the user access to work?  You could run code in the main database application that pushes data into this external, report only database.
antoniokingAuthor Commented:
Hi Dale
The user has write access to the folder, but read only access to the .accdb file.
I have no issue them opening and running queries I just don't want them to have the ability to change the queries.

The accdb file has an ODBC connection to our customer database.
Dale FyeOwner, Developing Solutions LLCCommented:
We are talking around this.  

How does the user have "read only" access to the Access database?  The only way I know of to limit a users access to "read only" is via code implemented in the database application itself.

Are you implementing some form of security in the database application?  For example, checking the users Windows UserID and then locking forms so that the user cannot make changes if they do not have specific assigned privileges?

Can the user open the .accdb file from his/her desktop?  If so, then he/she should be able to execute a query against it from Excel.
antoniokingAuthor Commented:
The .accdb file has read and execute NTFS permissions for the user, this is effectively what I mean by read-only.
The user can open the .accdb and run the queries fine but when loading the query via a macro in Excel the user sees a run-time error (see above)
Dale FyeOwner, Developing Solutions LLCCommented:
Is the database configured for Shared use?

I don't have 2010 on this computer, but in 2007, that is located under the Access Options -> Advanced and is then a radio button that lets you either select "Shared" or "Exclusive".  Make sure that Shared is selected.

Where is the .accdb file located?  Is it on a network drive or on the users computer?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The .accdb file has read and execute NTFS permissions for the user, this is effectively what I mean by read-only.>>

  One thing that is not obvious is that when JET connects to a DB file, even if read-only access is requested, part of the log in process with connecting needs to write some status bytes into the database header page.

 So in general, a user need write access to the DB file as well.

 However with Access alone in the past, it used to work even when the file was read only as you could have a DB on a CD and still read it.  However you do end up with exclusive access.

  What it sounds like to me is that you are attempting more than one connection to the DB at the same time (say two ADO connection objects), which from JET's standpoint is "multi-user" and you can't have with a read-only file.

  A way to check that would be to make the file read/write, start your Excel process, then use the Show Roster Function to return the number of users (or a utility like that), or look in the LDB file and see if more than one slot is filled (the LDB file is a 255 element array of 64 bytes each, 32 for the station name and 32 bytes for the JET user name).


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
antoniokingAuthor Commented:
Thanks for the explanation!
I'll rely on a backup if a user messes up the access file!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.