Solved

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

Posted on 2014-11-20
11
2,584 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)
Comment Utility
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
Comment Utility
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)
Comment Utility
What version of Access are you using?  Are you using workgroup security from 2003?
0
 

Author Comment

by:antonioking
Comment Utility
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)
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:antonioking
Comment Utility
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)
Comment Utility
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
Comment Utility
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)
Comment Utility
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
Comment Utility
<<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
Comment Utility
Thanks for the explanation!
I'll rely on a backup if a user messes up the access file!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now