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?

Answer 1

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.