Solved

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

Posted on 2014-11-20
11
2,842 Views
Last Modified: 2014-11-26
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!
0
Comment
Question by:antonioking
  • 5
  • 5
11 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40454967
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.
0
 

Author Comment

by:antonioking
ID: 40454984
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40455000
What version of Access are you using?  Are you using workgroup security from 2003?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:antonioking
ID: 40455005
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)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40455011
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.
0
 

Author Comment

by:antonioking
ID: 40455015
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40455050
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.
0
 

Author Comment

by:antonioking
ID: 40455058
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)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40455275
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?
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40455343
<<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).

Jim.
0
 

Author Closing Comment

by:antonioking
ID: 40467761
Thanks for the explanation!
I'll rely on a backup if a user messes up the access file!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question