MS Access Backend – Server Backend Keeps Getting Inconsistent

corruptionms access

I built an Access database. It was originally meant to be for 6 people but I see that it's up to about 20, so that could be the problem. Anyway, the backend gets into an inconsistent state a couple of times a day. It is usually possible to resolve this by opening the backend at which point it offers to recover it and usually does so.

In response to a question in the comments: What happens is that the frontends are no longer able to read any of the tables in the backend and so throw an error.

When I open the back end is states the following:

Microsoft Access has detected that this database is in an inconsistent state, and will attempt to recover the database. During this process, a backup copy of the database will be made and all recovered objects will be placed in a new database. Access will then open the new database. The names of objects that were not successfully recovered will be logged in the "Recovery Errors table.

The only option is to press "OK". Usually, this can be done in situ, but when I have been looking at the file on a VPN I sometimes find it spins for a little while. In that case, I can copy the inconsistent file to my harddrive, open it, press OK, and let it do its thing. It usually only takes a couple of seconds. At that point it says:

Microsoft Access has recovered this database. Examine the database to verify that there are no missing database objects.

So far this has been successful apart from once when I had to go to a slightly older backup.

The database is not particularly complicated (I don't think) but it does have some VBA.

The basic idea of the database is that we are tracking lots that need to move through a set of processes but that get broken up. So we might receive 1000 items, and then have 300 of them go to prescreening, and later another 100, and so on through all the about 8 steps.

The way I've done this (which I'd be happy to change – I just don't know what to change) is to have a small submission table, which tracks the lots as they come in, and a larger table (called preorder for reasons which don't matter) which tracks the line items.

The preorder table has quite a large number of fields (about 75). But of importance to this is the quantity field and the status field. The status field tracks the items through the process (so from step 1=Receiving to 8=Shipping or whatever). The quantity tracks how many of this particular item there are from this batch with the other properties at this status.

Then there are forms for each of these steps with very similar VBA behind them. One of the fields will be basically "quantity to move to the next step" (this is one of the fields in the preorder table that is initially defaulted to 0). The user fills in this quantity (and some additional information potentially) and presses a button to process the step.

The VBA:

opens a recordset (rs) for the preorder table where the status is 1 (say) and the quantity to go to the next step is >0.
It also opens an appendonly recordset of the preorder table (rs2).
Then it goes through the records in rs, adjusts the quantity down, and appends a new copy of the record with the new quantity and an updated status.

The below is an example from one of the screens. Others are similar, although there are some nuances baked in that may or may not be important.

Private Sub btnProcess_Click()
    DoCmd.Hourglass True
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM tblPreorder " & _
                            "WHERE (((tblPreorder.StatusID)=1) AND ((tblPreorder.PSQuantity)>0));", dbOpenDynaset, dbFailOnError)
    If rs.EOF Then
        MsgBox "No records found for processing"
        rs.Close
        DoCmd.Hourglass False
        Exit Sub
    End If
    Set rs2 = db.OpenRecordset("tblPreorder", dbOpenDynaset, dbAppendOnly)
    rs.MoveFirst
    Do Until rs.EOF
        myQuantity = rs("Quantity").Value
        PSQuantity = rs("PSQuantity").Value
        rs2.AddNew
        For Each fld In rs.Fields
            SFld = fld.Name 'to catch special fields
            Select Case SFld
                'special cases
                Case "ID":  'do nothing
                Case "Quantity":
                    rs.Edit
                    rs(SFld).Value = myQuantity - PSQuantity
                    rs.Update
                    rs2(SFld).Value = PSQuantity
                Case "Comment":
                    If Len(Trim(rs("PSComment")) > 0) Then
                        rs2(SFld).Value = Trim(rs("Comment")) & vbCrLf & Trim(rs("PSComment"))
                    Else
                        rs2(SFld).Value = rs(SFld)
                    End If
                Case "StatusID":
                    rs2(SFld).Value = 2 'Changes the status from 1 to 2
                Case "DateChanged":
                    rs2(SFld).Value = Now()
                Case "EmployeeID":
                    rs2(SFld).Value = UserID()
                Case "Location":
                    If rs("PSLocation") <> "" Then
                        rs2(SFld).Value = rs("PSLocation")
                    End If
                Case "PSDate":
                    rs2(SFld).Value = Now()
                Case "PSEmployeeID":
                    rs2(SFld).Value = UserID()
                Case "PSQuantity":
                    rs.Edit
                    rs(SFld) = 0
                    rs.Update
                    rs2(SFld) = 0
                Case "ReleasedFiles": 'do nothing
                Case Else:
                    rs2(SFld).Value = fld.Value
            End Select
        Next fld
        rs2.Update
        rs.MoveNext
    Loop
    rs.Close
    rs2.Close
    c = Me.CurrentRecord
    Me.Requery
On Error Resume Next:
    DoCmd.GoToRecord acDataForm, Me.Name, acGoTo, c
    MsgBox "Items moved to BNC Request"
    DoCmd.Hourglass False
End Sub

My questions:

If you see anything obviously wrong, of course, let me know. It works like a charm when it's just me testing it. I've never managed to replicate the issue, but I can see that it's happening in the production version.
I've followed the advice on a couple of websites on avoiding this (e.g. https://www.techrepublic.com/article/get-it-done-top-10-ways-to-prevent-access-database-corruption/). I'm basically tinkering and trying things because I don't fundamentally know what the issue is. E.g. I switched from recordset to DAO.recordset in the above code. It didn't seem to make a difference, so I might go back. Everyone is using Access 365 and the backend is Access 365 so it seemed to make sense to use an Access specific recordset. I close all the recordsets (rs.close); I compile the VBA; I have saved the frontend as an accde; All users use the accde from their own computers; All users are using wired connections; I have version control so everyone is using the latest frontend.
The main question, I suppose: How can I diagnose this? I could easily imagine putting a new table in to track who's hitting the process button and when and which one. I'm probably going to do that to see what happens in the lead up to a corruption. But what are your recommendations for what to track and are there any other tricks and tips to get to the root of this?

Code specific questions:

Recordset or DAO.Recordset or some other thing?
dbFailOnError or dbSeeChanges or some other thing?
rs.Edit … rs.Update on the couple of fields that get updated as I loop (current code)? Or one rs.Edit … rs.Update on either side of the loop?
Should I be putting a manual hold or something to prevent people from running similar code simultaneously? It offends me a little to do so, since I kind of imagine that the people who make MS Access will do a better job than me at that sort of thing. But I could probably set a flag somewhere that literally makes people wait their turn. I'd still be worried about a race condition.

Wider database questions:

Are there other secret settings that need to be adjusted to minimize this issue? Everyone is using MS Access 365. Name AutoCorrect Options, Filter lookup options, caching, data type support options. I don't really know what these do and I suspect there are gotchas everywhere! I had a similar database that I used for years without issue, so I was lulled into a false sense of security here.
Have I structured this completely wrong? I'm okay to do a whole bunch of work – it would just be nice to have a good sense that it would solve the problem!

Best Answer

It looks like this is a so far unfixed problem with Access

Microsoft document the following workaround :-

Click Windows Start Press the Windows key and then type Command. Right-click on Command Prompt and choose Run as administrator.

Copy and paste the following commands into the command prompt window:

REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters /v DisableLeasing /t REG_DWORD /d 1 /f

NET STOP SERVER

NET START SERVER

Close the command prompt window.

see https://support.microsoft.com/en-us/office/access-reports-that-databases-are-in-an-inconsistent-state-7ec975da-f7a9-4414-a306-d3a7c422dc1d

Related Solutions

MS Access – Protecting Backend File

What about setting special permissions to the folder containing the backend file. This page has a table that might help.

Ms-access – pass a single record from a recordset to a module

Direct answer regarding Recordsets:

For Recordset objects used by Access (DOA -the default used by Access, or ADO) the short answer is "No". Regrettably there is no concept of a "Row" object in these older data models, so you cannot pass all values of a particular row independently of the Recordset.

You could pass an entire Recordset object once it is positioned at a particular record. A function could retrieve the necessary values (via the Fields collection) then leave the Recordset unaltered so that the calling function could continue looping through all the rows.

UPDATED Recommendation after comments

Honestly, I would try eliminating the stored calculated values if the system proves efficient enough to do all calculations dynamically. The only way to determine this is to try it. If it proves much too slow (which can honestly become much more annoying than the current system), then I would revert back to a stored, calculated value with a recalculate button and all.

Since the calculated value is not dependent upon other database or external queries, it should be efficient enough to use a parameterized function as you have already written. The benefit to such a function is its flexibility: It can be called from another function and method, from within a query, or from a form control element.

(FYI: Consideration of additional database and/or external queries is important, because if it were dependent upon other query services, these can often require proper connection management, and/or take a long time, etc. This often complicates the solution and can justify the alternatives.)

You could also create another function that takes just the primary key (e.g. ID) for a given record. It then queries the database tables directly just for the given ID value, then calls the original function using values retrieved from the recordset. Such a function can be useful when the full record is not immediately available or convenient. But be careful since using this in a query of thousand or records can freeze Access since it is repeatedly opening and closing a recordset for each function call, often much slower than passing parameters.

'* Assume the original function is RedemptionEstimate(p1, p2, ...)
Public Function RedemptionEstimateFromID(vID as Variant)
  If Not IsNumeric(vID) Then
    '* Avoid problems with null (and SQL injection concerns)
    '* Consider other error handling code
    RedemptionEstimateFromID = Null
    Exit Sub
  End If

  Dim db As Database
  Dim rs As Recordset2
  On Error Goto catch

  Set db = CurrentDb '* Not critical for a single recordset, but good habit if performing multiple queries
  Set rs = db.OpenRecordset("SELECT * FROM DataTable WHERE ID=" & vID)
  If rs.EOF Then
    '* Assuming ID is unique primary key, rs has only one record
    '* Call original function, passing values from query as parameters
    RedemptionEstimateFromID = RedemptionEstimateFrom(rs!Col1, rs!Col2, ...)
  Else
    '* ID not found... Consider other error handling code
    RedemptionEstimateFromID = Null
  End If            
  rs.Close '* Would be closed automatically, but good habit to close explicitly

  Exit Function
catch:
  '* ID not found... Consider other error handling code
  Debug.Print "Error " & err.number & ": " & err.Description      
  RedemptionEstimateFromID = Null
End Function

When displaying on a form with thousands of records, it can be a serious waste to calculate dynamic values for all records in the query. Instead, the value can be calculated just for the displayed record(s). One drawback to this can be that such a field can't be used to filter the query. To calculate the value for the currently-displayed record, add a new control to the form and its Control Source property, add a call to your function passing from values as parameters: = RedemptionEstimate([Col1], [Col2], ...).

Regarding alternatives:

Indeed, this approach is discouraged for volatile values. However, it can also be a valid technique if it is known that values seldom change. In that case, a static table field can be much more efficient and easier to work with than repeatedly recalculating values dynamically. This technique requires a well-established and reliable update process and schedule. It might seem annoying now, but if you could automate such a process that runs outside of normal times it could still be a reasonable approach. Consider answering these questions rather than basing it off the "annoyance" factor :)
- Do these values change for all records on the same schedule?
- Does recalculating values dynamically slow down queries and related user interfaces?
- Could an update process be automated to run outside times that effect other workflows?
- Do you often experience outdated values for particular records with the current process?
- Do such outdated values have seriously negative effects?
To answer this question fully and properly requires that you first specify the requirements and behavior of the form. Do you need to update the source-table values or only view them? Will the form load only a few records at a time or all 10,000 that you mentioned? How long does it take to calculate the "RedemptionEstimates"--for a single row, for the entire set of records?

Creating a temporary table is a viable alternative, but if you plan on updating values back to the original table, you must ensure proper indexes and relationships are defined, etc. Worst case you need to then write extra code to update the tables. But I'd wait to recommend details until you answer the other questions.

Other Comments:

Passing values into a function from a query is a legitimate technique. Perhaps 13 parameters is a lot and it is good that you're exploring possibilities. However, you should be able to resolve the #Error values with proper null handling and debugging. Don't give up on this technique since it might prove to be the best solution.

Sometimes it is necessary to explicitly loop through each row of a recordset, but don't get caught up in loops. The proper technique is to bind a record source to a form or report. There is no need to loop through and explicitly "print" each record on a form. Access is built to do such tasks for you. If you specify a Row Source for the form (or report), the form will automatically open the query and create its own Recordset behind the scenes.

(I discourage explicitly creating a Recordset then binding it to the form yourself. Access technically allows this, but it is buggy. Trying to create and update an empty field in an existing recordset also complicates things and I think requires ADO Recordsets instead of the default DAO. In other words, I suggest not going down this path.)

Best Answer

Related Solutions

MS Access – Protecting Backend File

Ms-access – pass a single record from a recordset to a module

Related Question