Hello good people at AccessForums!
I have an issue with my access program and I can't figure out how to solve this.
A small team uses the software to manage invoices. It has a not too complex database schema, the team uses forms to add data to entities in multiple steps.
It uses two access files, a "frontend" one which has the forms and queries, and a "backend" one which stores the data. Backend is only accessed through the frontend file. The backend is stored on a shared drive. The team uses Windows 10 and Access 2016
The problem occurs randomly, sometimes not at all, sometimes multiple times a day. People already working with the program don't seem to be affected, but if new people try to open it, they get an error, saying that the database is in an inconsistent state and can't be accessed. It then proceeds to create a backup and restore the file. If everyone working with the program quits it, and reopens, then new people are able to join in too.
I am aware of this issue, and the team confirmed that the workaround has been applied to the server: https://support.microsoft.com/en-us/...6-d3a7c422dc1d
The frontend file uses 2 methods to connect to the backend:
- Forms with control fields, mostly used for simplified data entry and if certain rows need to be switched to a specific state
- DAO: For more complex processes, usually with transactions. I made sure to close the database object and set it to nothing after it runs.
I would be very grateful for any idea on how to tackle or troubleshoot this problem. I tried to recreate it at home on 3 computers, but I was unable to recreate the error, as we are not even fully sure what triggers it.
This is how a generic DAO process looks in the code:
Code:
Sub executeSql(sqlCommands As Variant)
On Error GoTo errHandler
Dim wrk As DAO.Workspace
Dim db As DAO.database
Set wrk = DBEngine.Workspaces(0)
Set db = wrk.OpenDatabase(CurrentDb.Name)
Dim SQL As Variant
With wrk
.BeginTrans
For Each SQL In sqlCommands
db.Execute SQL
Next SQL
.CommitTrans
End With
exitRoutine:
If Not (db Is Nothing) Then
db.Close
Set db = Nothing
End If
Set wrk = Nothing
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Tranzakciós hiba"
wrk.Rollback
Resume exitRoutine
End Sub
Thanks for any help in advance!