MS Access VBA data read/write conflicts
Posted on 2012-08-28
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