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!
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!
Dale Fye
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.
ASKER
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.
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.
What version of Access are you using? Are you using workgroup security from 2003?
ASKER
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)
thanks for taking the time to help me out
We're using Office 2010, the access file is accdb format (2007 I believe)
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.
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.
ASKER
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.
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.
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.
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.
ASKER
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)
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)
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the explanation!
I'll rely on a backup if a user messes up the access file!
I'll rely on a backup if a user messes up the access file!