Results 1 to 4 of 4
  1. #1
    Solm is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    2

    Database gets in inconsistent state inconsistently

    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!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i get this a lot because many people are using the same db on the network.
    If this is your case, then give everyone their own copy of the FE on their PC.

    My inconsistent state errors went to zero afterward.

    (microsoft has a bad bug with WinOS 7/10 and a db on the network)

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If users are sharing a common FE file then you will get issues.
    As ranman has suggested, each user should have their own local copy of the frontend, otherwise there is little point in having it split.

    And just to clarify it SHOULD be split for multiuser use!
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Solm is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    2
    Just an update, I asked the team to use separate files, and no errors were reported since then.
    Thank you very much for the suggestion, it saved me a bucketload of headache.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 08-08-2019, 11:05 AM
  2. Inconsistent access database
    By gold1957 in forum Access
    Replies: 2
    Last Post: 07-22-2019, 03:18 PM
  3. Replies: 2
    Last Post: 08-21-2018, 07:46 PM
  4. Inconsistent State - All VBA Missing
    By PicoTTS in forum Access
    Replies: 3
    Last Post: 02-09-2017, 06:47 PM
  5. Replies: 11
    Last Post: 06-04-2014, 10:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums