Updating the same data at the same time locking .mdb

Topic

New Reply

We have an Access database at europe server somewhere. Weekly we copy this database – making changes and pasting back on server.
Yesterday we had a message
The MA Jet database engine stopped the process because you and another user attempted to change same data at the same time.
When I am clicking Help – it tells me following:
The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time. (Error 3197)
This error can occur in a multiuser environment.

Another user has changed the data you are trying to update. This error can occur when multiple users open a table or create a Recordset and use optimistic locking. Between the time you used the Edit method and the Update method, another user changed the same data.

To overwrite the other user’s changes with your own, execute the Update method again
______________________________________________________________________

Unfortunatelly I can not get to Update method because as soon as I click OK to this message – I can only see the frame of the Microsoft Access.

Does it sound to you as if it was corrupted or maybe there is a way to restore it?
Any help is appreciated, thanks and good day!

Reply | Quote

Replies

I very much doubt that the data was corrupted, but you will need to check to see whose change went in. For these kinds of errors it is also important to know what version of Access you are working with, as record locking strategies are a bit difference in older versions of Access than the newer ones. Also we need to know whether you have a split database, and if you do, whether the tables are stored in Access, SQL Server, Oracle or some other database engine.

Reply | Quote

It is Access2003 and tables are local to Access.mdb – this is the reason we need to manually modify weekly.
Thanks for your help.

P.S. You said >>you will need to check to see whose change went in<<
but all I can see is an error message and than an Access frome with nothing in it.
I can't open it by File-Open New – same error message. Hope it is fixable. Thanks

Reply | Quote

I just wanted to know if I had not provided enough info. Thanks

Reply | Quote

Can you open the database with the Shift key held down?

If so, select Tools | Database Utilitities | Compact and Repair Database and watch closely for error messages.

Reply | Quote

Hans,
I had tried to do Shift-open and error message popped as follows:

Confirm ‘Explore’ Action (title)

Choosing to ‘Explore’ 28 items at once may take a long time and cause your computer to respond slowly.
Do you want to continue?

I had clicked OK and 10 Explorer frames popped up. I have closed tham now and had tried to open .mdb again and same thing had happened – same as before error message about several users were trying to update same records at the same time.

Reply | Quote

You should first select the database then hold down Shift while opening it. If you haven’t selected the database yet, Shift+clicking means that you select a range of files, which is not what you want.

Alternatively, start Access first, then open the database from within Access with the Shift key held down.

Reply | Quote

I will post tomorrow, thanks so much!

Reply | Quote

NO luck, had tried many times.
Is it bad news? Thanks

Reply | Quote

Try the following:
– Start Access.
– Create a blank new database.
– Select File | Get External Data | Import…
– Browse to the problem database.
– Try to select all database objects (tables, queries, forms, reports, macros, modules) into the new database.

If this fails too, your database has become seriously corrupted.

Reply | Quote

What I can do is to:

– Start Access.
– Create a blank new database.
– Select File | Get External Data | Import…
– Browse to the problem database…

and when I select it – I am getting that ‘another user tried to update same record at the same time’ message.

As I understood that is it, right?
We are trying to restore from the previous day but it takes forever. Thanks for your help.

Reply | Quote

As I understood that is it, right?

I’m afraid it is, sorry. Apparently the database became irretrievably corrupt.

Reply | Quote

I am hav ing another .mdb corrupted on the same network drive.
Error ‘can’t fing Object MSysdb’
had tried all suggested previously – no luck!

What can I do to prevent this from happening?

Thanks

Reply | Quote

I am hav ing another .mdb corrupted on the same network drive.
Error ‘can’t fing Object MSysdb’
had tried all suggested previously – no luck!

What can I do to prevent this from happening?

Thanks

If the drive is slow to respond it can cause these problems.
I have also run into a problem when the back-end was too many layers down under the root of the drive (or the mapped root).

Reply | Quote

I am hav ing another .mdb corrupted on the same network drive.
Error ‘can’t fing Object MSysdb’
had tried all suggested previously – no luck!

What can I do to prevent this from happening?

Thanks

There a number of things that can done to reduce Access database corruption. One of the biggest is using a split database design – if your application contains both data and forms in the same .mdb file, you should look at splitting the data out into a back-end file. And you might want to consider putting the front-end .mdb file on each user’s workstation. Another thing is if you are using Access security then you should consider moving the security file out to each workstation as well. And you should look at the locking strategy you are using when records are updated. To go beyond that, we would need to see a cut-down sample database…

Reply | Quote