Access DB already in use error

[The_Kurgan]

Is there a way around the "file already in use" error? There are times when the code below gets halted when the DB is already open. Several people may be running this code at the same time and there's a chance this can happen.

'get data from Master table
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim stDB As String, stSQL As String, stProvider As String
stDB = "[URL="file://\\fimcfs001\CORP\Accounting-Shared\ReportingReporting\Access\xxx.accdb"]\\fimcfs001\CORP\Accounting-Shared\ReportingReporting\Access\xxx.accdb[/URL]"
stProvider = "Microsoft.ACE.OLEDB.12.0"
'Opening connection to database
With cn
    .ConnectionString = stDB
    .Provider = stProvider
    .Open             '<-------------------------------------------- HALTS HERE
End With
'Create the Recordset object
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
'1st - see if data has been saved to Master table
strSQL = "SELECT * FROM Master WHERE ID_Number = '" & Review.ListBox1.List(Review.ListBox1.ListIndex, 1) & "';"
rs.Open strSQL, cn, adOpenStatic, adLockBatchOptimistic
If rs.RecordCount = 0 Then GoTo NoneFound
rs.MoveFirst

 

[Joe4]

If you have multiple people using an Access database, the database should be split into separate front and back ends, and each user should have their own copy of the front-end to work from.
Then you should avoid this issue.

See: https://support.office.com/en-us/ar...database-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc

 

[The_Kurgan]

Thanks Joe. I had looked into that and attempted a test run. I noticed that during the operation of that code, the backend piece still appeared to lock. That is, a "locked" shadow icon appeared briefly in the folder (xxx_be.laccdb). That's the same thing that happened before splitting, so I assumed it would react the same and give the error. Or is that not the case?


The tricky part of debugging this is that this error doesn't happen frequently, so it's difficult to duplicate.

 

[The_Kurgan]

I was thinking... I have the DB set to compact on close. Is it possible that the error is being produced because someone is attempting to enter data while the DB is compacting?

 

[Joe4]

I am not sure, as I have never tried to run Access VBA code from Excel.
What I would recommend doing is trying to run this from a Front-End copy of the database that no one else is using.

Is that the end of your code, or is there other stuff happening underneath it. If it is not updating any values, I would think that there shouldn't be a problem. If it is, if someone else has those records open (the ones it is trying to update), that will cause a conflict/issue.

 

[The_Kurgan]

Yes there is more code, but I didn't think it would be pertinent. There is record-updating going on, I am closing-out the connection, etc. I probably should have posted the whole module.

I'm starting to think that it may be the Compact on Close. I just tested with a user. I had the DB and relevant table open while he ran his operation and it worked fine. I've structured the data so that it should be impossible for users to update the same record.

I appreciate your time and expertise!