Trying to add a data source to MS Excel from 2010 password protected Access database by going to Excel-->Data-->From Access (in Get external data)-->select database.
If I remove the password from the database, I am able to add the data source/query whether or not I have the database open in Access. If I add a password and close the database, I can add the data source/query from Excel. However, if I encrypt the Access DB and open the DB, Excel fails to connect to the database and I can't query.
I set the database password using "legacy encryption method" (as mentioned on other questions pertaining to this problem), but it looks to be a database locking issue. In excel, I set the "open mode" to DB_MODE_READ but no connection is added.
Some error messages I've received while testing:
"You attempted to open a database that is already opened by user 'Admin' on machine..." Not sure what this means - all I want is read access to the database.
"The workgroup information file is missing or opened exclusively by another user." I have looked everywhere, but can't find any information about the workgroup information file - I thought later versions of Access did away with .mdw files.
Regardless, it seems like you can't connect to or query an already-opened, encrypted 2010 Access database (unlike a non-encrypted 2010 Access database or encrypted but closed Access database). If anyone has any input on solving the issue of querying an opened, encrypted 2010 Access database, that would be great!
