Solved

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

Posted on 2014-11-20
11
3,013 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

734 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