MS Access back-end database corruption

Question

I have written an MS-Access application and split the database. In order to improve performance when it is being used by multiple concurrent users on a network (as suggested by this), the front-end creates a persistent connection to the back-end using the following routine:

Public theOpenDb As dao.Database

Public Sub OpenTheDatabase(pfInit As Boolean, Optional databasePath As Variant)
  ' Open a handle to a database and keep it open during the entire time the application runs.
  ' Params  : pfInit   TRUE to initialize (call when application starts)
  '                    FALSE to close (call when application ends)
  ' Source  : Total Visual SourceBook

  Dim strMsg As String

  If pfInit Then
    On Error Resume Next
    Set theOpenDb = OpenDatabase(databasePath)
    If Err.number > 0 Then
      strMsg = "Trouble opening database: " & databasePath & vbCrLf & _
           "Make sure the drive is available." & vbCrLf & _
           "Error: " & Err.Description & " (" & Err.number & ")"
    End If

    On Error GoTo 0
    If strMsg <> "" Then
      MsgBox strMsg
    End If
  Else
    On Error Resume Next
    theOpenDb.Close
    Set theOpenDb = Nothing
  End If
End Sub

Some of my users report repeatedly receiving "unrecognized database format" errors in their back-end databases. The databases have all been recoverable via compact and repair, but this problem is still very frustrating to them.

I read here:

Do not hold connections open: Always remember to close the Microsoft Access database connections after finishing your work. Open Access database connections always have the chance of becoming corrupt if network connections are lost.

Does maintaining a persistent connection between my front-end and back-end increase the risk of database corruption?

There are a lot of situations where the DB could get corrupt. You may have a persistent connection but does the server ever reboot? MSAccess has quite a process to close a database and rebooting the server may not close the application properly. This is just one thing to consider. Ideally, a persistent connection would be best for the times that the DB can be accessed. If you have downtime, lets say outside of business hours, you should close the DB properly to avoid DB corruption due to server power cycles or even anti-virus software scans. — Jim L
I agree that it is always best to close the DB properly, but I can't guarantee that will always happen at the companies where my software is installed. I am asking whether the existence of a persistent connection increases the chance of database corruption. If that is the case, then it will probably make sense for me to remove the persistent connection code. — Lisa Way
Access DB corruption will always be around and is more prevalent when you have numerous concurrent connections. I would recommend housing your data in a SQL database. You can then use MSAccess for a front end. This way, if it does become corrupt you eliminate possible data loss and can just re-install front end. — Jim L
@Lisa Way, maybe a dumb question but I assume that your customers' users use their front-ends locally? — Rene
@Rene Yes, the front-ends are installed locally on each user's computer. — Lisa Way

Albert D. Kallal Albert D. Kallal · Accepted Answer · 2020-03-18T16:13:36

Well like always, too little information without context?

A persistent connection NEVER causes corruption.

Always remember to close the Microsoft Access database connections after finishing your work.

Right, but your persistent connection is NEVER doing any work!!!!

So, if you write some code to open a table, do some work, then you close that recordset and connection. This has ZERO ZERO to do with the code that holds the connection open. That connection is NEVER doing any work, and NEVER updating any data - so the suggestion does not apply here.

So, yes, you most certainly want to clean up your update code, and routines that say open a table, do work, and THEN it should close things up like suggested. But for a connection that just sits there and does nothing and no work? It not going to cause a corruption, and if anything it will reduce corruptions.

As always, a bit of information without context is worse then not having that information at all. So, I borrowed a rope from you? Well, I failed to mentioned that your cow was attached to that rope!!! So, context here is important.

So, what that reads should really say:

Do not hold connections open that are doing useful work. Do your updates or whatever, and then tidy up and close up after you are done. But that suggeest has ZERO ZERO to do with some code that keeps open a persistent connection and does zero work, and hence has zero to tidy up and close up after it done it work. So, the persistent connection not doing any work, not doing any updates, and hence it has nothing to clean up, and has ZERO abilities to keep a pending disk write (table write) open and un-finished should Access crash.

MS Access back-end database corruption

1 Answers