Save a recordset in Access 2000

[rider99]

Hi,
After converting our DB from MS Access 97 to 2000, I encountered an error message whenever I tried to save a brand new record. The error message is" The Jet database engine stopped the process, because you and another user are attempting to change the same data at the same time."
It looks like a sharing and locking issue, but I've set no lock on the record, and I was the only one testing the system, nobody is on the application. When I tried to debug it, the application stuck at the following commend:
DoCmd.RunCommand acCmdSaveRecord
When it is stuck there, the error message keeps popping up many times. I looked up all kinds of reference on such commend but couldn't solve the problem, when I purged half of the data from the DB,it worked, however Access 2000 is supposed to hold twice as much data as Access 97 does. Please help.

 

[thornmastr]

A few points here. Access is not fibbing. The other user is you. You usually get this error when you are using a continuous form, or the like, to find a record. The user then double clicks the record or clicks a button and gets a brand new form with that record available for updating, which happens, you write, and the update fails for obvious reasons. The solution is to close the other form when the editable form opens.

If this is not correct, tell me something of the logic flow. Somewhere, that record is in use at least twice.

By the way, get rid of runcommand to save your record. It is out of date. Try
Me.dirty = false

Robert Berman

 

[rider99]

Thanks for the answer. We are tackling the right area, but I have a few questions:
1. Why can Access 97 handle this, and 2000 can't?
2. If I put Me.dirty=false. The record will be automatically saved?
3. The other thing I forgot to mention is that the record was saved although I got that error message. So it seems that the runcommand worked, but got timed out.
To add more information, I don't have multiple forms open, after launching access, I open one form for order entry, and this form is bounded with a query, which works on two big tables. I receive that error message when I click <save> on that one order entry form.
I agree with you that the other user is I. But I don't know how to get around it. Thanks again.

 

[thornmastr]

You really got my interest after your last reply. I think I have the answer to the problem, but let me give you some background. On my development system I have both A97 and A2,2K. Using A97 I set up a similar situation which, since I used the same record to pull that record into a form used for editing. I got the same result that you did with one tremendous difference. It made no difference if I used me.dirty = false or your method of updating. The record would not take the update. It consistently failed to do the write which is just what it should do for very obvious reason, yet your program does the update which told me my assessment of your problem is totally, absolutely wrong. Sometimes I hate the scientific method. You never know when you are right and always know when you are wrong.

I attacked my reference library with a vengeance. It’s hard enough to admit I’m wrong, but not knowing the right answer becomes a personal affront. The correct answer to your problem depends on your answer to this question.

Is your back end SQL Server? If it is, the solution is easy. See MSKB article Q318882.

If your back end is not SQL Server, well, you won’t like the answer. Does the word “corruption” make you sweat? Again, see MSKB article Q182867. It will even tell you how to fix it which may work. Emphasis here on the word “may”.

If for some reason you can’t get to these articles, tell me which one you want, give me your email address, and I will send it to you.

I would really appreciate your keeping me in the loop with this problem.

Robert Berman

 

[rider99]

Fabulous!! Problem solved. I checked Q182867 and realized that the problem is in the data type. I have two fields with data type&quot;Memo&quot; in the table that underneath the form, OLE or MEMO data type are long value column, the jet engine attempts to read the long value page when reading the row of data. This also explains why when I purged the data to half, the problem disappears, because there is much less rows for the engine to read while it read other pages. After I changed the type from &quot;memo&quot; to &quot;text&quot;, it works beautifully. Thank you so much.