?
Solved

MS ACCESS DB Error 3197

Posted on 2007-04-09
39
Medium Priority
?
657 Views
Last Modified: 2008-02-01
There are no other users that are using this DB, it just fails to open.  How do I correct it?  It will not compact or repair.  I have removed all permissions and access to it with no success.

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.
0
Comment
Question by:bsumner007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 14
  • 8
39 Comments
 
LVL 75
ID: 18876344
This can occur for multiple reasons, for example:
Lets say you are on a form.
Somewhere in code, you independently run a process (update qry, etc) that changes some data in the underlying table for that form, or something similar.  Then, on the form, you save you edits ... and that message pops up.  That's one possible scenario ...

mx
0
 
LVL 75
ID: 18876368
However, if it will not Compact and Repair ... that does not sound too good.  What exactly happens when you do a C&R?

You might try JetComp.exe ... just Google it and will come right up ... MS link.  I rarely see jetcomp fix anything, but hey ... worth a shot

Also ... from the VBA editor ... go to Tools>>References and see if any are **Missing

If not, then try to Compile ...

let us know what happens ....

mx
0
 

Author Comment

by:bsumner007
ID: 18877233
JetComp did not work.  After I try to C&R it will not even start the process.  It just gives a dialog box that says:
The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 75
ID: 18877279
Well ... to do a C&R ... none one can be using the mdb at that time ... which is sort of what the error message IS implying ... but ... humm.  Seems like some sort of corruption.

can you upload to http://www.ee-stuff.com/Expert/Upload/upload.php ... removing any sensitive data of course?  

mx
0
 

Author Comment

by:bsumner007
ID: 18877428
Just uploaded it....
Thanks
Bruce
0
 
LVL 75
ID: 18877524
I need the link ee stuff gave you ...

mx
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18877674
<aside>
while it is easier if the link is posted for you, you can search ee-stuff by Q# or even the full link path to this Q.
</aside>
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18877687
of course ee-stuff could not find any files for this Q ... bsummer?

is there an old .ldb file in the same folder as the .mdb file even when you have all instatnces of your app closed? If so delete the .ldb file and try again.

Steve
0
 

Author Comment

by:bsumner007
ID: 18877710
I'm having trouble uploading it, it keeps saying the file is too small, it is 3.5MB zipped so I'm a bit confused.  I'm sure it's on this end as I have never used this feature before....will keep trying...

Bruce
0
 

Author Comment

by:bsumner007
ID: 18877768
MX,
Do you have a personal e-mail address that I can send this to?  The upload is not working for me...

Bruce
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18877792
did you repair/compact before zipping?
did you fill in all the available fields on the form?
ee-stuff is picky and does not always tell you what is really wrong ... almost always says file too small if there is any problem at all :-(
0
 

Author Comment

by:bsumner007
ID: 18877812
It will not R&C, all fields were filled in on the upload page.....weird!
0
 

Author Comment

by:bsumner007
ID: 18877849
Here's what I'm typing in the upload fields:

Question:http://www.experts-exchange.com/Database/Miscellaneous/Q_22499755.html
File:C:\DOL Master DB.zip
Comment:
Good luck! http://www.experts-exchange.com/Database/Miscellaneous/Q_22499755.html
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18877857
try removing the spaces from the file name
0
 

Author Comment

by:bsumner007
ID: 18877903
Removed spaces, still does not upload...
0
 

Author Comment

by:bsumner007
ID: 18877992
It must be a firewall on our network here because it should work but is not....
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18884130
0
 
LVL 75
ID: 18884258
Full details on decompile: <Weird - I swear I posted this already - oh well)

Follow this procedure:

0) **Backup your MDB**
1) Compact and Repair
2) Execute the Decompile >> you database will reopen
3) Close the mdb
4) Open the mdb ... and then do a Compile (from the VBA Editor >> Menu >>Debug>>Compile<YourProjectName>)
5) Close the mdb
6) Compact and Repair one more time.


Here are sample 'links'.  Adjust your paths and file names accordingly:

Run this from Start>>Run, enter the following command line:

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile C:\Access2003Clients\GoldstandtSma\App\SmaApp2K3.mdb


"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile C:\Access2003Clients\GoldstandtPdm\App\PdmAppl2K3.mdb

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile C:\Access2003Clients\HMC\App\AimAppl2K3.mdb

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

mx
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18884300
mx ... I would adjust the steps a hair to include a repair/compact after decompile, before re-compile just to make sure any of the unnecessary *stufff* M$ is tracking internally gets dumped also.

Steve
0
 
LVL 75
ID: 18884365
I believe my steps (0-6) already include that ... am I missing somthing?  if you don't think that's clear, let me know ... and I will revise it (seriously) ...

mx
0
 

Author Comment

by:bsumner007
ID: 18884396
Will give it a shot and let you know...

Thanks
Bruce
0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 500 total points
ID: 18884430
I would replace steps 3 + 4 with and instruction to Compact / Repair as this will make the mdb the *cleanest* it can be before you compile again. We certainly would not want to compile any old *crap* in it again :-)
0
 

Author Comment

by:bsumner007
ID: 18884587
Well guys, here's the syntax I used:
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "F:\mm\MM_DP\Access\DOL Master DB.mdb" /decompile

It still says "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same date at the same time"

This is the same error I get in all circumstances....
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18884688
can you import any of the objects from the bad mdb into a new mdb?
0
 

Author Comment

by:bsumner007
ID: 18884812
Tried that too but get the same message....
0
 

Author Comment

by:bsumner007
ID: 18884821
Do any of you guys have an e-mail that I can send you a zipped version of this???
0
 
LVL 75
ID: 18885309
stevbe ... Done.  Good catch.  Funny ... that is *actually* what I do ... somehow I left that step out.  

mx
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18892272
bsummer ... take a look at my profile.

To any lurkers who will now post flames and/or run to an administrator that I am breaking the EE rules ... go read the rules again, very carefully. I promise to post back with any information I find if I can actually help bsummer to actually get the app back in working order.

Steve
0
 

Author Comment

by:bsumner007
ID: 18892519
Steve,
Did it work, not sure our Firewall lets .zip file go out.....

Bruce
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18892536
I have not seen it yet, but public email sometinmes takes a while. You could try tricking it by changing the extension to .txt
0
 

Author Comment

by:bsumner007
ID: 18892699
Actually we have to use.zzz if it was stripped so I'll resend but it is a .zip file
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18898157
I got it but am not hopeful, the standard set of tricks is not helping any. I hope you have a recent backup.
0
 

Author Comment

by:bsumner007
ID: 18899012
Thanks Steve, I did back it up from a previous version but was trying to save redoing quite a bit of work.  No big deal, my main goal was to see if there was a fix to this problem through some sort of back door access or something I haven't seen in Access before....

On another note is there a limit to the amount of files that can be in one Table, the main table  in this DB is approching 100,000 records  Queries and reports seemed to be running fine so I was just wondering.  I know that there is a 65K limit to pasting records from one table to another...I just started building this DB so there is not a lot of bells and whistles in it yet.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 18899119
I think the 65K applies to Excel.

The max size of an Access MDB is 2GB ... and 100K records is no big deal per se ... all things being 'equal'.

mx
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18899709
I tried all the standard stuff which did not help and admittedly I do not have the ability to whack at it with a hex editor to get this in an operable condition. MX has you covered on the size limitations.

Steve
0
 

Author Comment

by:bsumner007
ID: 18900020
Not a problem.....I've begun updating the backup copy I have with routine backups!!!!  If I can figure out how to give you and MX points for your time I'll do that...

Thanks again
Bruce
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18900347
Because we could not provide a real resolution you can post a Q in community support asking for your points back.
0
 

Author Comment

by:bsumner007
ID: 18900845
Well actually you and MX did answer a couple questions, can you post point to more than one person?
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18904525
yes, please see FAQ for instructions ...
  http://www.experts-exchange.com/help.jsp#hi69
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question