Cron job issue with multiple instances

[DataMiner]

I have one Access 2000 database front-end that is used to automate many tasks. This database is supposed to stay running all the time, and only a single instance of it should ever be running at one time. It's been running, fairly successfully, for quite a few years. However, I have several irritating, ongoing problems:

1. I or one of my co-workers forgets and starts up a 2nd instance of the db, which can screw up automation.

2 The database occasionally gets shut down, through power outages, IT messing with my stuff, etc.

To overcome problem #2, I run a program called z-cron, which starts up the db when the PC starts up, and tries to re-start the database several times per day just in case it's gotten closed for some reason.

Z-cron opens the db using the /excl command line, to avoid problem #1.

But as a result of this, we have irritating problem #3: When z-cron tries to start the db, and it's already running, we get left with an instance of Access with msg "could not use, file already in use", which won't go away until someone clicks "OK". So unless someone is babysitting this regularly, we can end up with a half-dozen or so instances of Access all sitting there with "could not use" messages, waiting for someone to click and make them go away.

I'm sure there must be a much smarter way of doing this. Any ideas?

 

[CameronM]

You have a number of options, including created you own launcher database or VB executable to test that the file is not already in use.

Perhaps the easiest option might be to create a batch file, which checks that the Access lock file (*.ldb") does not exist before opening it again. You could then schedule the .bat file through z-cron instead of the command to open Access.

I am not a batch file expert, but this seems to work - obviously you would need to change the file path to MSACCESS and the database.

@echo off
if not exist F:\forum.ldb goto 20
echo file exist
rem no need to open it again
goto end
:20
echo file not exist
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.exe" F\Forum.mdb /excl
:end

 

[DCrake]

The above sounds fine, however what happens if Access is closed down unexpectedly and the ldb file is not removed by Access it will still think that access is running.

 

[LPurvis]

And, more importantly, Exclusively open databases don't create a lock file at all. So there should never be one.

<OT>
I have a mate who lives in Brisbane. He's a great Access/SQL Server guy. Maybe you should all start a club.
</OT>

 

[DCrake]

Another approach would be to simulate the creation of an ldb file. So when the app is opened it tests for the existance of a specified file in a known location. If the file does not exist then it creates a text file in a specified location. When the app closes it kills the file.

So if another instance is opened and it finds the text file it quits otherwise it creates the file and remains open.

David

 

[LPurvis]

Oh there are certainly ways.
You can search for a matching Window title using an API call, or attempt to just grab a running instance with GetObject... (which would launch the application though if it wasn't loaded :-s).

Or, upon startup, have the application create an Exclusive lock on itself...

'Declarations section of Std module
Dim db As DAO.Database
 
'Function called at startup - in splash screen form or AutoExec macro
Function fOpenExclusive()
On Error Resume Next
 
    Set db = OpenDatabase(CurrentDb.Name, True)
 
End Function

If your application is already opened exclusively the above will silently fail.

 

[DataMiner]

LPurvis, this sounds hopeful but I'm not sure how it would work. If the app is already open exclusively, when I try to open it again it will never get this far, will it? Won't I still just get the "already in use" message?

It seems like I would need to NOT open the app exclusively, and then use this piece of code in the autoexec instead....?

 

[LPurvis]

It's intended only as an "Exclusivity guarantee".
If this runs as your app starts up, then even if it wasn't launched with an Exclusive command line parameter (and hence didn't open exclusively), it will now hold an exclusive lock on the MDB. Thus preventing other users from opening it.
If your app was opened exclusively - then the code will fail (but we don't need to heed any error raised by the failure - hence the Resume Next and ignoring the error).

 

[The_Doc_Man]

The problem is that depending on exactly what method is used, locking is either mandatory or advisory. Windows file API calls honor the locks. Certain other methods of locking do not always do so. Therefore, you face two problems.

1. You cannot hope to determine whether the database is actually in use except through functions callable in Access. Third-party products to determine file lock status must be suspect unless they have a specific statement about being able to determine that a give file is locked by a given program. The reason I say this is that if the use of the API is not predictable, you must use the same method to test the locks as you do to lock the locks in the first place, i.e. Access.exe

2. If the system crashes or the network crashes, the file locking status might stilll be compromised because an orderly cleanup did not occur. Further, the .LDB file might even be compromised such that it would not be openable. So in the general case, this is a very difficult cleanup problem.

However, this could be a special case since from your description, the file is opened only locally. Therefore, you should never have a remote connection. I.e. only local file locks and no network locks. In any case, here is how I might approach this problem.

Static solutions are always going to fail due to the situation that can occur following a system crash. You will need a dynamic solution.

In your database, have a little file that you close and open once per minute. Create a startup form that minimizes and hides itself, but that does not ever close. Inside that form, create an OnTimer routine to update some predictable file once a minute. Pick a longer time if you want. Have the OnTimer routine trigger once per minute by loading 60,000 (60 seconds x 1000 milliseconds as a LONG INT) to the form's timer. This is how you start the timer and how you propagate it.

Update the file of choice, which could be a one-line wonder, so that you change its date of last update. Now comes the tricky part. You can do this with VB or VBA code to look at the file to determine its date/time of last update. In VBA, you can test for it to have been updated within the last x minutes by finding it with the File System Object and looking it is modifed date. If you set the update interval to 60 seconds and find a file that has been updated within no more than 120 seconds, you might be OK. If it is more like 1200 seconds, you start to worry. If it is less than 60 seconds, you are doing just fine and your DB is probably open.

Anyway, the idea is that the only way to REALLY know if the database is actively open is to have it do something every so often. Have the DB tell you when it is active, don't use a passive method to determine activity. Since it is a timer database anyway, seems to me it was already going to have some timer-based code in it to trigger other events. So this is no big stretch.

Now, after a restart, you can probably bet that the database will be hosed if it was actively doing something at the time, so how do you fix that? (Rhetorical question...) NEVER run your database. Instead, at startup time, delete the old copy and make a new copy of this file from a protected location. If there IS an LDB file, kill it. Start with a fresh copy each time.

If you need to update the schedule that you are using for the cron-like startups, edit the master file, then manually step in. Stop the run and restart it from a copy of the new database. I.e. simulate a reboot with respect to that one operation.

The only thing left is record-keeping, which you could manage via external files that contain your logging information, perhaps in a linked database or perhaps just raw text files that have a time-stamp and an action report like

09-01-20 12:40:06 Started Doc job ID #231

If you don't have much in the way of record-keeping needs, or if it is meaningful to keep the record of the operation as a .TXT file in the first place, this is pretty easy. The final leg of this is that when you open that logging file for the first time, make the date/time stamp a part of the log-file name. Like, for the entry above, ZCRON_090120124006.LOG

Note that if you had external connections to the database, the above is guaranteed to not work correctly.

 

[DataMiner]

LPurvis, maybe I'm just slow, but I can't see how this will help. The app is already opened exclusively, that's not the problem. The problem is that when the cron job tries to open it again, I get "can't open file, already in use" messages hanging there until someone remembers to come along and click OK to make them go away. The app will never get as far as running the autoexec.

Right now I am pursuing running a cmd line batch file similar to what CameronM suggested. But I will try using the tasklist command, to search for a window with the name of my db's main screen.

 

[LPurvis]

I hadn't appreciated that your problem is really just one of OS tidiness :-s
(I thought there must be some physical problem with users opening the app manually and there then being multiple instances of the application).

Obviously you will want to read what TheDocMan has to say on the subject.
But without knowing what this z-cron of yours is or does it's hard to offer more.

Normally (from another app, with DAO enabled) you can determine exclusivity by a similar attempt to open Exclusively - before then actually launching.
fCanOpenExclusive uses pretty standard code.

Function fCanOpenExclusive(DbFullName As String) As Boolean
'Determines if the passed db can be opened exclusively
    Const cInUse = 3045
    Const cExclusive = 3356
    
    Dim dbe As DAO.PrivDBEngine
    Dim wrk As DAO.Workspace
    Dim db As DAO.Database
    On Error Resume Next
 
    Set dbe = New DAO.PrivDBEngine
    Set wrk = dbe(0)
    Set db = wrk.OpenDatabase(DbFullName, True)
    
    If db Is Nothing Then
        If (Err = cInUse Or Err = cExclusive) Then
            fCanOpenExclusive = False
        End If
    Else
        fCanOpenExclusive = True
        db.Close
    End If
    
    Set db = Nothing
    
End Function

 

[DataMiner]

Thanks so much for all the helpful input. Doc_Man, if I have problems in the future I may pursue your method but for now I'm going as follows:
I've changed my db back to open non-exclusively. This, for some reason, allows z-cron to realize it's already open, so it doesn't try to open it multiple times. To keep dumb users (like myself) from opening it multiple times I've implented a function called winCheckMultipleInstances which I got years ago from

© Graham Mandeno, Alpha Solutions, Auckland, NZ
' graham@alpha.co.nz

I don't know the web address that I got it from but it says it's freely distributable so if anyone wants a copy just let me know. It basically just check to see if there's already a window open with the same name as the one you're trying to open. Then I've added a user warning and automation which closes the 2nd instance down.

This still won't help if I'm dumb enough to open the app on a 2nd PC, but I'm giving myself some credit for brains here....

The other thing I tried was using a dos batch command. There is a command that will tell me if there's an active window with a given name:

tasklist /v /fi "Windowtitle eq Microsoft Access - [Mainten*" > tasklist.txt

If the window is not found, tasklist.txt will be empty. But then I want to have an IF statement in the batch file that, if tasklist.txt is empty, goes ahead and opens my application, and otherwise does nothing. What I can't figure out is the dos syntax for the IF statement.

 

[LPurvis]

Yeah - I mentioned that concept earlier... "You can search for a matching Window title using an API call"

I'm not big on DOS commands I'm afraid. You'll need an older hand perhaps.