[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 627
  • Last Modified:

MS Access VBA data read/write conflicts

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

Katerina.
0
katerina-p
Asked:
katerina-p
2 Solutions
 
AlexPaceCommented:
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.
0
 
armchair_scouseCommented:
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

ErrorHandler:

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
      Else
        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
    Resume

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!!
0
 
katerina-pAuthor Commented:
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!



Katerina.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now