CODEWITHSUNDEEP
databasevb.netms-access
Michael
Michael

Reputation: 1

The database has been placed in a state by user 'Admin' on Machine X that prevents is from being opened or locked - Single User

I have a bunch of legacy access based databases that I've been using for years without issue - queries have been running between them for years using ODBC/DAO/ADO. Now suddenly in the last few days, I've started getting the "The database has been placed in a state by user...." error on a bunch of them.

I have tried to narrow the problem down, but it seems to be getting worse. I have tried making a local copy of the database file, opening it, and then on the same machine, trying to create an ODBC connection to it, and get the error. I have also tried running successive queries on the database and still get the same thing (copy of the file on my local machine, so there is only my single connection, basically connect to the database, run a query, close the connection, wait 2 minutes, then try to open a new connection - FAIL - so it is definitely not a multi user limit problem or anything like that.

The issue is consistent across multiple platforms (directly in MS Access (2010 and 2013), with Excel (2010 and 2013) queries to the Access DB, and with Windows Forms VB.net applications trying to query the access DB (through datasets, OLEDB, and ADO)

Until this week all of these applications were working as designed and had been for years- I am the only Dev working on this stuff, so I know that nothing in the programming has changed, so it must be an external issue.

The back end databases reside on a shared server drive (server is running Windows Server 2008) - and we have had no other connection issues to the server or network; it is limited to connections to access database files.

Does anyone know if something has changed lately (in the last week or so) with the ODBC drivers? Maybe an MS update?

Thanks in advance!

Upvotes: 0

Views: 7058

Answers (1)

Aousaf Rashid
Aousaf Rashid

Reputation: 5758

It seems that you can fix this issue by buffering the Access binary. Use the Binary.Buffer function in a query that defines your Access database, then reference that query in order to use the binary in a query that pulls each table. Note: I also define parameters for my folder path and file names.

For example:

 //myDbBinary
 let
Source = Binary.Buffer(File.Contents(DataFolder_param & FileName_param),  
 [CreateNavigationProperties=true]))
in
 Source

 // Table1 Query
 let
Source = Access.Database(myDbBinary, [CreateNavigationProperties=true]),
_Table1 = Source{[Schema="",Item="Table1"]}[Data]
 in
_Table1

The source is this

Upvotes: 1

Related Questions