Solved

Weird error messages in Access front-ends when updating DB table on timer event

Posted on 2014-04-30
10
533 Views
Last Modified: 2014-04-30
Hi

I have since a couple of days 2 Access front-ends which run the the whole day waiting for users to trigger their processing. The trigger is through an field value in separate records (one per app) in a table in the Access backend. So the 2 apps have each a form timer event every 15 seconds which does the following:
- resets the timer inerval to 0
- checks if the processing flag field is set to "Yes". If yes, it does the corresponding processing and resets the processing flag to "No" and goes to the next step, if not it goes to the next step
- stores, in a dedicated row in one table, a heartbeat (current date/time) so that other apps know that is still alive
- resets the timer to 15 secs
- exits the timer routine

So these 2 apps are frequently accessing the 2 tables, but each of them access a different record in these 2 tables, their "own" record, which bears the name of the app in one field which they select upon.

Since I put these 2 apps in production, they run, but they get lots of errors, always the same errors, but kind of randomly distributed:
- "The database has been placed in a state by user 'Admin' on machine 'VDAT001CH' that prevents it from being opened or locked", in the routine that stores the heartbeat
- "Could not use <db name>; file already in use",  in the routine that stores the heartbeat
- "Error: Too many active users. ", in a routine that reads the heartbeat record of the other app to check if it is running
- "Error: Object required", in the Form_Timer routine

As I have record-level locking set and of course shared backend enabled, I just don't understand why these 2 apps get bogged down by each other. Because I resume and exit all the corresponding routines when errors happen, the apps continue to run, but I find a large number of errors in the apps logs (yet another table where all apps log what they do, including trapped errors).

The apps are not sharing any routines, they have their own copy of a series of generic modules and their own specific module and behind-form code. The only thing they share are these 2 tables, and that sems to go badly...

I open all table using CurrentDB.OpenRecordSet(<SQL statement>, dbOpenDynaSet).

Can someone tell me why there are so many collisions between these 2 apps on these 2 tables ?

Thanks
Bernard
0
Comment
Question by:bthouin
  • 5
  • 3
  • 2
10 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40031664
Do you really need to check this every 15 seconds?  How often will a user trigger one of these events?  If you can extend that to 30 seconds or a minute, it might resolve your problem.

For updating your heartbeat table, are you opening a recordset, or simply running an update or append query?  I would recommend the latter.
0
 
LVL 57
ID: 40031693
<<As I have record-level locking set and of course shared backend enabled,>>

 Couple of comments:

1. Without actually seeing the code and the procedures, it's hard to say what's going on.

2. Record level locking is basically broken.  First, you need to open the DB with ADO in order for it to be turned on.

 Second, not all operations are record locked.   Index updates and DML operations are always page locked regardless of settings.   In addition, LVP (Long Value Pages) are also always paged locked.   So if you have a memo or OLE field in the record, you'll never get record level locking.

3. I've never had great luck with modifying form timers.   I always found it better to leave the timer set for a specific interval (say 5 seconds), then check how long it's been since my "last run" by using date/time functions.

4. If your not explicitly closing a form with acSaveNo, you may be trying to save something that is considered a design change.

5. If you have compact on close set and are closing the DB's, they could be colliding.

6. If directory security is not set correctly for the FE's and BE's, an .LDB may not get created correctly.  If not, then the DB is opened exclusive regardless of settings.

Jim.
0
 
LVL 1

Author Comment

by:bthouin
ID: 40031849
Hi Dale and Jim

@Dale:
>> If you can extend that to 30 seconds or a minute<<
I don't think that'll change much. actualy, one app has 30 secs timer interval, the other 15 secs, but if I change that (I did), I get the same errors. And yes, I need thiis kind of interval.
>>For updating your heartbeat table, are you opening a recordset<<
Yes, I am, and I'm closing it as soon as I have read or updated the heartbeat record. I don't see why a query would be better. You tell me.

@Jim
>> 2. Record level locking is basically broken<<
Ah, well, thank you for informing me, that'll explain everything ! I'm using Access 2007, BTW.
>>you need to open the DB with ADO<<
More bad news ! I'm not using ADO anywhere in my apps. However, I'm using DAO in a "generic" manner, i.e. just official SQL command, no rs.Find and stuff like that.
That's the first time I hear that record-level locking doesn't work unless you use ADO, and that's a very useful info.

3. The processing, when it is triggered, can last a couple of minutes, so I have to stop the timer before doing that, and restart it upon completion. I have always used that logic in all of my applications which use a timer, and so far I never had trouble, except with these 2 apps.

4. / 5. The 2 tables involved have no indexes, but one has a 3-field primary key. No memo or OLE fields.
No form closing while the apps are running, no compact and repair on exit.

6. I think directory security is properly set, because otherwise the users would not be able to use the interactive applications I developed for them (which BTW do not get such errors), as they access the same Access backend. But when you say "an .LDB may not get created correctly", I must say that I have witnessed since not such a long time that, although applications clearly access the backend, from time to time there is no .ldb file open, which puzzled me. That of course would explain one of the errors I get.

In that case I'm glad that all my apps are anyway going to use SQL server as backend within the next 30 days (I'm just in the middle of the first tests), and that should solve quite a few of the current problems, shouldn't it ? So I won't bother to change anything currently.

Regards
Bernard
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40031897
<<That's the first time I hear that record-level locking doesn't work unless you use ADO, and that's a very useful info.>>

 Here's a link to the MSKB aritcle describing it.   The problem is that Record Level locking was added in JET 4.0, but they never went back and updated DAO because it was an "old" technology.

PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60http://support.microsoft.com/kb/306435

<< but one has a 3-field primary key.>>

 well it has an index then<g>.  But kidding aside, I know what you meant.   That still should be OK though (assuming your not updating the key at all).

<< there is no .ldb file open, which puzzled me.>>

  Something's not right then.  There has to be a .LDB in order for it to be multi-user and shared.  The .LDB is what JET uses to place locks on the DB.

Jim.
0
 
LVL 1

Author Comment

by:bthouin
ID: 40032162
Hi again Jim

Well, my case is hopeless: I am working in huge bank, which, as virtally every large company, uses lots of very old stuff. We are working on Window Server (!) 2003 (!) over Citrix and thin terminals, and both ADO and DAO are version 2.8... So I can forget anyway about record-level locking with such cave-age stuff.

The funny thing is that the .ldb comes and goes, and that's pretty new, but it started much before I put my two "trouble-making" apps in production.

As I said, I hopefully can kiss all these problems goodbye when moving the backend to SQL Server.

You get the points.

Tanks again
Bernard
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 57
ID: 40032197
FYI, you can do pseudo record level locking if you need it by padding out records.

JET can't have a record span pages, so if your record takes up more than 2048 bytes, then only one record fits on a page and viola "record level locking".

This is nice in that you can do it on a table by table basis as required.  Yes, it chews up disk space, but when it comes down to it, it's rare to find situations where you really need it.

The other thing that may be of help here is flagging.  i.e. you can't do that while I'm in the middle of this.  To see how you would implement that, read this:

http://www.experts-exchange.com/Database/MS_Access/A_5328-Resource-locking-in-your-applications.html

Jim.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40032240
I would still change your code to implement an Update or Append query, rather than opening the recordset to update your "heartbeat" table.

You indicate that the process that gets run may take several minutes to run.  Is it running against a table or tables which may be opened elsewhere by another user?
0
 
LVL 1

Author Comment

by:bthouin
ID: 40032356
Hi Jim

Your suggestion with the more than 2048 bytes is brilliant, I'll try that.

I just read your flagging article, and I'm actually doing a lot of that already. For example, when one of my 2 "problem-making" app is really doing its processing (and not only waiting for its trigger), it does not want one particular data maintenance app used by the users to be running. So it "instructs" all the instances of that app to exit. That's done by a combination of:
- every app registering itself in the DB ("I'm running") with user name and date/time
- every data maintenance app using a timer and checking if it should exit, which is indicated by a flag in an app parameters  table. That flag also prevents the start of a new instance of the corresponding app

So when the "server app" wants to do its processing, it checks if there are instances of the related maintenance app running, if yes it sets the "stop" parameter of that app to "Yes", waits for all instances to disappear, which also means that new instances cannot start, and then the "server" app does its work. When completing, it resets the "stop" flag to "No", so that users can again start the maintenance app.

Users of the maintenance apps are given a warning and a minute or so to exit manually, if they don't (they are at lunch or in a never-ending meeting...), the app itself exits.

So that's a good example of resource locking. However, I'm nearly glad I didn't think about using that for my problem-making apps when they set their heartbeats or read their flags, because I would only have agrravated the locking problem, as the tables involved have by definition very short records :-)

Thanks again, I'll keep you posted about the 2048+ bytes stuff.

Regards
Bernard
0
 
LVL 1

Author Comment

by:bthouin
ID: 40032364
Dale

See my answers to Jim.
0
 
LVL 1

Author Comment

by:bthouin
ID: 40032466
Hi Jim

Sorry, one more question: I have added some filler text fields (length 255) to my short records to make them bigger than 2048 bytes, but I have not filled them with data. Is Access dumb and it reserves 255 bytes for each field even if it's empty, or is it clever and "shrinks" them when writing them to the DB, and then my records are still small ?
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 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

13 Experts available now in Live!

Get 1:1 Help Now