OpenCurrentDatabase in exclusive mode doesn't seem to be working?

[DPlank]

I've got a script in QTP which uses vbscript and opens an Access instance (2010 in most instances, but a couple machines are using 2003) then uses opencurrentdatabase to open my database in exclusive mode in order to execute a vba macro. I explicitly have set the default opening mode on the database in Access to be Exclusive, it's set to lock all records as the default behaviour because I have multiple machines running queries and updates against it - it's supporting an automated process.

I want only one machine able to perform any update at one time, as when there are 10 running at the same time, Access seems to throw a wobbler and occasionally decides that the db can't be opened by one machine or another. However, despite the configuration changes, I can still run two instances on different machines, both of which still appear to connect and execute the macro simultaneously without encountering the expected "Database is locked exclusive" type error that I've added code to try and catch.

My code looks like this:

 LOG_Write "Opening Db..."
	Err.Clear
	On Error Resume Next
	oApp.OpenCurrentDatabase sDatabase, True
	While Err.Number<>0 And iTries<=MAX_TRIES 
		Err.Clear
		LOG_Write "Db currently locked; waiting " & iDelay & " seconds before trying again. Number of retries so far: " & iTries
		Wait iDelay
		oApp.OpenCurrentDatabase sDatabase, True
		iTries = iTries + 1
	Wend
	
	If Err.Number <> 0 Then
		ErrorMsg = "Too many retries: " & iTries & " - tried waiting for a total of " & iTries * iDelay & " seconds."
		LOG_Write ErrorMsg
		MISC_CheckForWork = False
		Exit Function
	End If
	On Error GoTo 0
	
	LOG_Write "Running allocation macro..."
	oApp.Run "Allocate", sThisWorkStation, sTableName, CustomerData, ErrorMsg
	LOG_Write "Macro completed..."
	oApp.CloseCurrentDatabase
	LOG_Write "DB Closed."
	oApp.Quit
	Set oApp = Nothing

My expectation was that if one machine had the DB open, no other would succeed in the OpenCurrentDatabase command, resulting in it entering the loop until it successfully ran the command. The reason I need to open it exclusively is that currently the Allocate method (which selects the first unused record and sets it as allocated so no other machine will try to pick it up) seems to be allowing multiple different machines to be allocated the same record when the command is run at the same time (or within a second or so).

I'm open to suggestions to make things work!

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[Darrylles]

DPlank,

This looks like a complicated solution, however, I understand that you may have specific requirements.

I have an 'inkling' that your issue is with what you call an 'unused' record.
How are you identifying this?
Also, where is your check for 'locked exclusive' error? It seems that you're checking for ANY error (Error <> 0). Are you sure that your check is specifically for 'locked exclusive' (thus your belief that 'locked exclusive' is failing)?

I also have this kind of requirement, and simply use a table with a boolean flag in it - if the flag is set - any app (or instance) attempting to connect is set to instantly close - the moment that it has opened.

I've never had any data problems with 'attempts' to run the application whilst an update is occurring - I imagine that that would only occur if data-writes were attempted (and that's not possible, because it's immediately closed).

ATB,

Darrylle





Never argue with an idiot, he'll bring you down to his level - then beat you with experience.