Tracking Use of Backend .mdb (Access 2K)

Topic

New Reply

We are trying to get a handle on how many times an application’s front and back end mdb database files are accessed. Both are secured with user-level security, sharing the same .mdw file. We have added a simple login audit table to the front-end; the AutoExec macro runs code to append a row with user name and date/time of access. Unfortunately, trying to implement the same approach in the back-end doesn’t work, since AutoExec doesn’t run if the tables are accessed through links in a front-end. The back-end is the main area of concern for us since several other Access applications (outside of our control) link to our back-end for read-only access to some of the tables. We are getting more frequent incidents of users being locked out recently due to an increase in number of users. It would really be helpful if we could get statistics on just how many “hits” the database is taking. We take the occasional peek at the associated .ldb file, but it doesn’t keep an ongoing log, so is of limited use. Any suggestions for tracking usage would be greatly appreciated. Thanks!

Dottie

Reply | Quote

Replies

I was hoping someone had some suggestions for you. Mine are sort of feeble I think, but maybe they will stir something up.
Unfortunately the lock file (.ldb) doesn’t really help – all it tells you is that someone does or did have a connection to the database. It may give some idea of the maximum number of users that have been connected, but that doesn’t mean that they are actively using a table in the back-end. If you wanted to track your own access, you could resort to making your connections dynamically (in code), and putting an entry into a table in the back-end each time a person opened the front-end. But that doesn’t help you much with other users who presumably don’t want you tinkering with their front-end database.
The locking problem you refer to may be something you can fix by changing the settings for locks in the back-end database. There is extensive info available in the MS Knowledge Base white paper on locking strategies. Hope some of this rambling is of use.

Reply | Quote

Wendell,
Thanks so much for the suggestions! I will check out the Knowledge Base articles to see if we can improve on the locking strategy. I have seen their article regarding the bug in Access 2K that prevents row-level locking from working when opening the database from a shortcut. Do you know of any way to avoid using a shortcut when one has to include a workgroup file for security in the startup? Meanwhile, I will read the white papers and try to implement some of their suggestions for avoiding locking problems. Thanks again for your willingness to help; I have learned a lot from your posts on other questions. We’ve been fighting unexplained growth of our front-end until I happened upon your advice to someone else to make it readonly. Works like a charm!

Reply | Quote

Actually, there are two ways to fix row-level locking – one is to apply SR1, which I recommend that everyone using Access2K do, as there are many, many bugs fixed by that. (SP2 on the other hand is a real dilema – the draconian email fix for Outlook offsets several Access bug fixes which would otherwise be very desirable.)

The second way (mentioned in the bottom half of Q238258 is to only start Access from the shortcut, and to then have the user open the target database from the menu. Not terribly attractive, but sometimes any port in a storm is better than none. I should add that we very seldom point users to another system.mdw file – we use a deployment process to download a copy of a master system.mdw file to the local hard drive so everyone works in one common file, but the problem with corruption of the .mdw file because of multiple users is resolved. Bear in mind that in several cases we have from 60 to 120 users.

I also meant to mention that you might want to look at a SQL Server back-end if you have more than a few users hammering the data side with updates. (Reads generally don’t have to involve any locking.) BTW, I like your style of answers to other people’s posts – concise but enough details to work things out, and accurate.

Reply | Quote

Thanks, Wendell, for all the additional suggestions, and for your kind words. Moving to SQL Server sounds like a great idea as we do have a lot of users. Not sure that it is an option right now, but we’ll keep tracking the number of logins and hopefully the numbers will back up our request for a more robust back-end platform. Will check on Monday to see if we have SR1 installed. Thanks again.

Reply | Quote