We help IT Professionals succeed at work.

error 3197:  "The Microsoft Jet database engine stopped the process because you and another user...

Ekalb
Ekalb asked
on
...are attempting to change the same data at the same time."  Not true.

This error occurs in a pretty basic point-of-sale module, just at the point when a product is selected from a dropdown list on a subform.  We've done exhaustive testing and believe me, it's frustratingly random - yet all too frequent. It's killing us.  #Error #Error #Error shows up in all the fields & you have to tap dance your way out of the program.

We have a FE/BE system. We don't have a memo or an OLE field on the form that causes this error. Our Access 2k program runs on 98, ME, and 2000 platforms in single-user environments. We updated Jet engine with the latest patch, installed all service packs and releases. We have run tests for hours but we can't reproduce the error on a certain sequence or under certain conditions. We know the db is corrupted once the error shows up, because it grows 50%in size after the error. At times we are able to go on working on the program as nothing has happened whereas other times it keeps giving the error and forces us to exit the program each time.

It is now happening on a regular basis, so it's not a reasonable solution to exit, Compact & Repair. We are out of ideas. Please HELP!
Comment
Watch Question

Commented:
hi Ekalb,

have u tried to export all the objects into a blank/new database?
this might solve the problem, since the db is corrupted.

cheers
Ricky

Commented:
or, u can first try to use the /Decompile switch: (make a backup first!)

e.g

C:\Program Files\Microsoft Office\Office\MSAccess.exe /decompile "C:\your folder\yourdb.mdb"


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

more info : http://www.microsoft.com/office/access/support/97.htm

Author

Commented:
Paurths - I have tried exporting all objects to new database - to no avail.  We're seeing this behavior on several different machines with totally separate backends, so don't think a corruption is causing the problem. I'll check out the decompile link - thank you.

Author

Commented:
The front-end we distribute (where problem is occuring) is an MDE.

Commented:
One thing that can cause this type of error is if you use code where you open/close recordset in the DB without exlicit set the variables free after the code has run

ex:

function perove()
dim db as database
dim rs as recordset
..
..
..

set rs=nothing
set db=nothing
close
end function

Do you use DAO or ADO ?

perove

Commented:
Do you have any OLE or MEMO field in the tables where the corruption occurs?

Ive seen this cause a corruption "beyond repair" before, and my client had to re-create the table and try to rescue the data he could

perive

Commented:
that should be this article (memo and / or ole in A97): http://www.microsoft.com/office/access/support/97.htm

Author

Commented:
Perove - no Memo or OLE fields in the tables.
On your theory of setting variables free - could that make the error occur intermittent, where you get #Error on a sequence once, then the exact sequence 30 seconds later works fine? (That's what we see.) Or would it occur every time?

Commented:
Ekalb,
Cannot tell for sure, but you really, really should (either it is causing the error or not) do this.

Should not be to much work to be sure that all the "datavariables" are set to nothing.

One more thing, does the databse really get corrupt (ie. do you have to repair it to get it back to work?) Or will it work (it just get bigger) if you resatrt the program.?

perove





Author

Commented:
Perove - Sometimes it'll work fine as soon as you close & re-open the program, so not sure if it's even really getting corrupted. Sometimes it's bad & you have to C&R.
BRONZE EXPERT

Commented:
have you tried the popular /decompile option?

i know that it's not the option you're looking for, but compact and repair does not always fix the same errors as /decompile will. it's worth a shot, right?

first, be warned that the /decompile option has the potential to destroy your database, so make a backup before you go gung ho! :)

decompile will take ALL of the p-code, the compiled access code, out of your database. you then need to open a module and choose compile [db name]. (you need to recompile the code you stripped using /decompile).  i've seen this fix a lot of "funky" issue with my own apps such as random GPF'ing causing the app to quit and corrupting the db. after decompiling, it works like a champ again.

to use /decompile you need to go to a command prompt, find the path for your msaccess.exe file and use this format:

[path]\msaccess.exe [Your db name].mdb /decompile

so if your access was under c:\Access and your db was in C:\MyDb\DB.mdb, you would type:

c:\Access\msaccess.exe c:\MyDb\DB.mdb /decompile

if you need more help with decompile, just ask. i'm not promising it'll help, but i've seen it fix more than one issue for me...

dovholuk
Ekalb,

Just passing through, and thought I'd add my two cents worth.

Check your advanced options. They should probably be as follows:
  Default open mode - shared
  Default record locking - no locks
  Open databases using record-level locking - enabled

- Graham

Author

Commented:
dovholuk - I don't know much about the /decompile option, but this problem is occuring in MDEs - doesn't that eliminate the chance of /decompile helping? Or are you saying doing it to the .mdb before making an .mde might do the trick?  If that's the case, I'd love to know & will give it a shot. Thank you!

Author

Commented:
Graham - we have tried those settings as well as other configurations.

Commented:
see my first comment about Decompile
Date: 10/01/2001 10:36PM PST
BRONZE EXPERT

Commented:
paurths,

my most humble of apologies! *honestly* i didn't see the /decompile switch you mentioned above.

i do apologize. i DETEST it when people don't cite other experts. here i am guilty of this very sin...

dovholuk

Author

Commented:
Paurths & dovholuk - This problem is occuring in MDEs - doesn't that eliminate the chance of /decompile helping? Or are you saying doing it to the .mdb before making
an .mde might do the trick?
BRONZE EXPERT

Commented:
sorry Ekalb, i didn't catch that bit about the .mde before i had posted.

where is the data stored if you are distributing the app? is it stored in a file on some file server or are you using a RDB backend?

any more info you can think of would be useful...

try the /decompile as paurths originally posted. good luck still.

dovholuk

Author

Commented:
dovholuk - we just have a simple Front End/Back End setup.  Front end is MDE, back end MDB.  Both live in the same directory.  I'm still confused - isn't /decompile a moot point with an MDE???  I very much appreciate the help. Increasing points to 500 since I'm still where I started.

Commented:
dovholuk, dont worry about it...  :-)

got to run now, late for work...
BRONZE EXPERT

Commented:
i'd tend to agree with you Ekalb that if you are distributing .mde's i doubt that /decompile is going to help. i'd still try it... ya never know with MS, know what i mean? ;)

i'm starting to think that the issue is with multiple users at the same time. how long have you operated the system? how many people concurrently use the system?

the drop down you say that is causing the problem, is the type set to Dynaset or snapshot? i'd try changing it to snapshot and see if that makes a difference.

is there any code behind that drop down? i've developed apps that mistakenly ran queries against my backend before i refreshed the front. i've seen that goof me up too.

dovholuk
I have a simular problem
access 2000
oracle

in access some records are locked (i have the error : other using has changed ....)

in oracle the record is not locked
in access the record gives error 3197

even afterstopping and starting oracle
even when i am the only one in the mdb

is i delete all records in oracle and then drop the link in access and relink the table and append the records i saved in a table then the lock is gone, but is there no quicker solution.

how can there be a lock when no one is in access but me and oracle is stopped & restarted

i tried compact repair /decompile and have no memo fields
Here are some KnowledgeBase articles that describe the problem, and how to fix it. Unfortunately there is no easy way.

<http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306204>
<http://support.microsoft.com/default.aspx?scid=kb;en-us;Q304561>
<http://support.microsoft.com/default.aspx?scid=kb;en-us;Q247771>

By the way, make sure to delete the LDB files before you go down this track. The existence of the LDB file with a record lock can cause symptoms where no corruption exists.

Author

Commented:
Unfortunately no one has touched on a solution to the problem.  The articles listed describe repairing a corrupted database - our problem is trying to figure out why the error happens to begin with and we don't think it's because of a corrpted database.  We got Microsoft involved and the best they could come up with was a workaround that traps the error, goes NextRecord then PreviousRecord.  Believe it or not that solves the problem when it randomly occurs.  It's ugly as the user sees the screen go through some weird gyrations for a few seconds, but when it's done all is well.  I'd sure like to prevent the error, but we (and apparently Microsoft) are dumbfounded.

What do I do with these questions where I get no answer?  Am I supposed to close them somehow or do they drop off eventually?

Commented:
2 options,

u showed a solution, so it might be usefull to other users who have the same problem. Put a Q in Community support to give u back your points, and PAQ the question. (for future reference)

or just delete it (i think the Delete option is disabled, so u still need to put a Q in CS)

CS: http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
make sure u put a link to this Q in it.

Cheers
Ricky

Commented:
for Ekalb

It's time to clean up this TA, so I will leave a recommendation in Community Support that this question will be saved and the points refunded.
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
Per recommendation, points refunded and question closed by
Netminder
CS Moderator