MS Access VBA data read/write conflicts

Posted on 2012-08-28
Last Modified: 2013-10-17
Hi All,

I’m having trouble with clagging on my MS Access system, with common errors of:

3045: could not use file [database]; currently in use
3704: attempted to open a db already open by user '[CurrentUserName?!] on machine [CurrentMachineName]. Try again
3239: too many active users

I’m afraid I’m self-taught over the last 10 years, and although this system is 40k lines and runs quite well most of the time, I’m hitting the above problems and I don’t know enough about things to necessarily fix myself. So, I’m looking for some advice.

The system runs and is used to run an on-line retail company; it is managing sales and stock levels etc, and its makeup is as follows:

•      12 accdb backend dbs, 2 backend ‘processing systems’, x front-ends – all Access 2007
•      All backend files on single server, front-end(s) are on local machines
•      Each ‘processing system’ is running on a separate User on the server
•      When needed, technical/IT access to these systems is by using Terminal Services
•      Temporary tables are sent out for users’ front-ends, i.e., Front-End users are not connected to the back-ends and are not the cause of the clagging
•      ProcessingSystem1 is on a continuous loop of downloading and uploading sale, stock and order information via FTP and HTTP calls etc. It is also reading and writing these changes to the dbs, and sending out temp tables to the front-ends etc.
•      ProcessingSystem2 is on a continuous loop, and is basically a skeleton system – if ProcessingSystem1 hasn’t processed key downloads/uploads within a set time, it will kick in and process them, else it sits idle.
•      These two ProcessingSystems will never be processing an identical function at the same time, but they will be reading the same tables and dbs.

Now, fairly often (1/d perhaps) one, or less commonly both, of the systems will hit an error and stop (I’ve got Error Trapping on both of them set to ‘all errors’). In debug mode, I can see the error is almost always on a ‘Set rst = dbs.openRecordset(...’ line. This is frequently on a recordset which is only needed to be opened to read, not write, although I haven’t set any options to that effect.

Given that these ‘shouldn’t’ clag, i.e., they’re not trying to write to the same table, and sometimes they’re not even reading the same table - only the same db, I’d like to know what I can do to minimise the occurrences. The ‘error’ can always be fixed by simply hitting F5 and starting it running again, because by then the other system has moved on. Therefore, if it would just wait a few seconds and try again, there wouldn’t be a problem! I’ve got ‘Open databases by using record-level locking’ set, ‘Number of update retries’ is set to 10, and ‘Update retry interval (msec)’ is set to 1000.

•      Is there a way to open a recordset in VBA which would not lock it up? Is this a snapshot? What Database.OpenRecordset Method Types or Options should I be using?
•      And what other ways can I minimise the problem?
•      What are the differences between the errors I’m getting?
•      Bonus question(!) – let me know if there are any advanced [Access] VBA books you would recommend also, thanks.

Many thanks for any and all help

Question by:katerina-p
    LVL 16

    Assisted Solution

    I think the default cursor type on an ADO recordset is "Forward Only" and the default lock type is Read Only.  These will give you the best performance with the least file locking.  You can use this for the stuff where you actually need to iterate through rows in a recordset, like building a page showing items in the cart.  

    Don't use the recordset object for actions that can be reduced to an SQL statement like inserting, updating or deleting rows.  Instead, make the SQL statement and use the Execute method of the ADO connection object.
    LVL 10

    Accepted Solution

    Hi Katerina,

    I don't know if you have error handling in your code, so some of the below might be like teaching you something you already know, apologies in advance if so.

    Regarding the recordset open which fails initially, but then works subsequently when you hit F5, perhaps you could use the error handler in that procedure to retry the OpenDatabase command for you.  The rather incomplete(!!) example below shows how you might use the error handler to trap for that specific error, then retry a number of times, and also allow you the option to retry again or stop the process:

    ' Declare this in a code module
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    ' Example routine
    Sub OpenData
    Dim intRetryCount As Integer
    On Error Goto ErrorHandler
    Set rstData = dbs.OpenRecordset("Blah blah blah")
    Exit Sub
    Select Case Err.Number
      Case XXXX: ' Error number that appears when you get your OpenDatabase error
        If intRetryCount > 5 Then
          If MsgBox("Tried and failed to open the data " & CStr(intRetryCount) & " times; do you want to try again?", vbYesNo+vbQuestion+vbDefaultButton2, "Retry?")=vbYes Then
            intRetryCount = 0
            Err.Raise vbObjectError+90001, "OpenData", Err.Description
          End If
        End If
        ' Delay 5 seconds (delay amount is in milliseconds, so 1 sec = 1000)
        Sleep 5000
        intRetryCount=intRetryCount + 1
    End Select
    End Sub

    Open in new window

    Advanced VBA books - I'm sure the learned fellows here can suggest some ideal VBA titles.  From my own experience, for Access VBA, I swear by my Access 2002 Desktop & Access 2002 Enterprise Developer's Handbooks, a 2-book set from Sybex, whose authors (Ken Getz, Mike Gunderloy and Paul Litwin) are Access gurus, and have been contributing to many (Access) VBA books over the years.  Yes, the books are out of date, I know (we are on 2010 after all) but a lot of the principles are still valid.  If you find a VBA book that has one of those three guys involved, then you are certainly heading in the right direction!!

    Author Comment

    Thank you both for your comments.

    @AlexPace, sorry, should have said - dao for all, not ado. However, what you're saying should still apply? So opening as Set rst = dbs.openRecordset(sSQL, dbOpenForwardOnly [8], dbReadOnly[4]) should have a difference?

    And presumably the execute improvement still counts above opening an dao.recordset?

    @armchair_scouse - I do have some error handing, but not much! and certainly none to handle these errors. That's a good idea - will start to add that Sleep loop - had hoped there would be a way to set more defaults to allow Access to do it itself! Or perhaps learn whether I'm opening the rsts inefficiently. Thanks for your tips on the books!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now