Multiple applications all writing to same DB 1

[OrthoDocSoft]

Dear folks,

FYI: Using Access 2000 format.

I have a VB6 application which resides on multiple computers, all of which "point" to the same database on a client's network, and update it frequently. This is still in development, and I am beginning to notice errors like this:

"Jet stopped because two different programs tried to update record at the same time."

The way this app is written makes it so this CAN be true, since all of the copies of this application on the network write to the one DB.

How do I "lock" the database by one copy of the application, so that other copies have to "wait" until the first copy is finished updating, before the other copies can update in their turn?

In pseudocode, I'm looking for something like

While MyDatabase.InUse = "True" then

   'wait until db gets clear

wend

MyDataBase.Update

If all instances of this application had that bit whenever they tried to update, it would seem updating would be orderly and I wouldn't get update "collisions."

Any suggestions?

Thanks,

Ortho



"you cain't fix 'stupid'...

 

[Remou]

That message is a symptom of corruption:

http://support.microsoft.com/kb/182867/en-us

You may wish to read:

http://msdn.microsoft.com/en-us/library/aa189633.aspx

 

[OrthoDocSoft]

Remou,

I added "wait" bits around the db updates to slow things down, and that "solves" the problem at least temporarily. I can't trust this as a permanent fix though. This test tells me there is NO corruption. And as I mentioned, the multiple program copies running simultaneously are all "attacking" the same DB, so simutaneous updates can occur, thus correctly invoking this error message.

I looked at the record locking bit you provided and tried changing the record locking status of the DB several different ways. None seemed to work. I even changed the locking to adlockpessimistic. No help.

Furthermore, if the record is locked, is there a way to know that so that you can "hang around" and .update when the record becomes unlocked? (See my pseudocode example above.)

Thanks for your help so far,

Ortho

"you cain't fix 'stupid'...

 

[dhookom]

When I read threads like this, I often assume the issue is with the table structure. Why are there more than one users attempting to update the same record at the same time? IMHO the granularity of your records should be sufficient to eliminate most possibilities of two or more users attempting to edit the same record at the same time. Proper normalization of tables might be needed.


Duane
Hook'D on Access
MS Access MVP

 

[Remou]

It is possible to use ADO to get records. You can then take advantage of the various locking options available for ADO. You can also mark the table as 'in use' when you get the record. In faq700-184 MichaelRed gives an example of locking.

However, like Duane, I am puzzled that a VB6 application is experiencing such a problem in that I would expect an almost instantaneous update of any record and that a record would be flagged as in use if it was being edited.

 

[OrthoDocSoft]

I was reading in one of my database books and came across the 'transaction' bits:

MyConnection.BeginTrans

MyRecordset.update '(this, I was already using, obviously)

MyConnection.CommitTrans

I added these bits and that seems to have helped. I no longer need the "wait" part to eliminate the collision. Does anyone know whether this was probabaly "key" to do?

Thanks,

Ortho


"you cain't fix 'stupid'...

 

[PHV]

Yes, the transaction way is a good one.
BUT I too wonder why your application exhibit such behaviour ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[OrthoDocSoft]

Well, PHV, not to belabor the issue, but I think the undesired behavior is because I have "several apps", or, more precisely, several copies of the same app, all "working on" the DB at the same time, and running simultaneously on my test network. But if you are telling me that updates to an access DB by even multiple apps all running at the same time are designed to avoid the very collisions that did occur, then I hear you. I also noticed that I was doing some unnecessary .updates, which I removed, but the undesired behavior "went away" with only adding the "transactions" bit.

Appreciate all of your help!

Ortho

"you cain't fix 'stupid'...