A Split mdb and randon table crashes...

[gmlwong]

A Split mdb and random table crashes...

One of my databases is having far too frequent problems lately...one or another primary table is crashing and always in the same field.

THE PROBLEM: the table crash occurs in many fields; always dates and numbers, but the one that seems to drive the errors throughout the record is the [Diary] field which is a memo data type field. It displays "#Error" in it and no changes are allowed to correct it and I can't simply delete the bad record and re-enter it.

Date fields that were fine, change to 1/1/1899, number fields go all over the place; Currency fields go negative that were positive, etc...

To fix this, I've been able to rebuild and copy all existing records minus the bad record into a replica table and move on and that's been okay but I'd just like to know if there is something we can be doing to prevent this all together...

THE DATABASE CHARACTERISTICS:

All users running WIN2000 (sp4) and Access2000 Professional (sp3); so I don't think it's an OS issues or version issue...

A split database shared with 12 network users; only thing I can think of is the problem is a data-sync network issue...?

The record count is well within the 1GB limit; currently only 85MB backend and a 35MB front end; compacts fine, runs very well otherwise...

I've seen this problem occur when I and another user are in the same table; user is entering or editing a record while I'm in the front-end copy viewing all records and other times, this problem does not occur so I don't think it has anything to do with user permissions or access to the same table because there are 12 users in this same database writing and editing record data to the same backend copy...

Very frustrating situation...can it be any of the underlying VBA that is running to cause a memo field to blow up?...anything you can come up with is greatly appreciated; Mr. Hartman, Vass, Miles, AutoEng, ghudson, Idjit, ....thanks!

 

[FoFa]

I'm not saying this is your issue, but we had another application that kept having DB corruption (C-Tree DB, not Access). We also had a MSAccess DB with a VB front end (not MSAccess VBA, but VB) that was being corrupted a couple times a week. Faircom (The C-Tree) said to turn off Oppertunistic Locking on the Windows Server. Once we did this the data base corruption quit on both databases. How ever this was heavly used as a file server (300+ users) along with these DB's. Just thought it worth a mention.

 

[Kodo]

Have you updated to the latest Jet version?

Are you using row level locking?

 

[gmlwong]

Digging further...

Thanks FoFa and Kodo:

We isolated an error message box that appears when this happens; if anyone has run across this, please let me know what can be done to prevent this from displaying:

In the course of beginning a record cancellation, which is a part of our standard data entry, a user pointed out the following error message for me and when the user selects to overwrite his changes, the entire backend table blows up with those randomly placed currency and date fields with wild values and "#Error" in the memo [Diary] field...

THE ERROR MESSAGE reads:
"Write Conflict. Another user is either viewing the same record that is currently open or has made changes to the same record that is open.":three buttons are displayed for the user to either accept changes made and overwrite the existing record, or exit without saving record changes and lose any changed data, or exit and save the changes of the alternate user.

I advised that my user not save the changes he would have liked to have saved and just get out of the recognized record. That saves the underlying table from becoming corrupt so at least we know how to prevent the table from blowing up.

What I'd still like to know is how this is happening in the first place...

This database resides on an up-to-date and secured Windows 2000 Professional Server PC and all networked user's have (sp4) for their MS Office Suite, which includes the latest Jet Engine version.

 

[DBL]

I know you've said the database is split but does each client machine have it's own version of the frontend or is everyone accessing the same frontend on the server? I know I had a similar problem and that was the cause. Once everyone has their own frontend the problem stopped.

DBL

 

[gmlwong]

Each client does maintain their own version of the main front-end database.

I do update this with new features and changes on a weekly basis and they go out and update to this newest version but at no time are there any users in and using the same master front-end version that I use...

See, I'm running out of hair to pull out...guess I should be lucky it's just grey and not gone yet?$?$?!

I wasn't expecting the error message described above so I'm at a loss now. I thought for sure it had to be a permissions issue but these problems have only occurred recently and this database has been up and running just fine since May '03...

I know you've said the database is split but does each client machine have it's own version of the frontend or is everyone accessing the same frontend on the server? I know I had a similar problem and that was the cause. Once everyone has their own frontend the problem stopped.

DBL

 

[DBL]

P.S. It's Ms Hartman

 

[Kodo]

THE ERROR MESSAGE reads:
"Write Conflict. Another user is either viewing the same record that is currently open or has made changes to the same record that is open.":three buttons are displayed for the user to either accept changes made and overwrite the existing record, or exit without saving record changes and lose any changed data, or exit and save the changes of the alternate user.
.

This has to do with record locking. Make sure you're using row level locking on your database and lock on edits. If you're querying other data with SQL in VBA, then make sure that your SQL statements are using adLockPessimistic. This will lock the record for editing while the action is taking place.

Look up BeginTrans/Commit Trans. It might help you solve your problem.

 

[FoFa]

Is another user actually editing the same record? We have had this problem when a popup from a form opened a second form with the same record (special update of fields they said) and basically they (one user) had the same record locked at one time. Just thought it worth a mention, not that you may be doing the same thing, but you have to concider what else they have going on when they get that error.

 

[gmlwong]

Ms. Hartman, ...

My fault.

Sorry Pat.

P.S. It's Ms Hartman

 

[gmlwong]

Row Level Locking...

KODO:

Since this database features both standard Access forms design as well as a wealth of VBA for some more advanced user features and data functions, where in the code would I place the SQL statement for "adLockPessimistic"?

At the very beginning of the main user's form (On Current or On Open event)that causes this problem or on the Properties sheet of the form itself?

This has to do with record locking. Make sure you're using row level locking on your database and lock on edits. If you're querying other data with SQL in VBA, then make sure that your SQL statements are using adLockPessimistic. This will lock the record for editing while the action is taking place.

Look up BeginTrans/Commit Trans. It might help you solve your problem.

 

[gmlwong]

Users...

FOFA:

Although it can happen, in these cases we know no one else was in the same record when the table blew up; twice in 10 days...

I held redundant meetings with all data entry and claims staff to explain just that fact and they have all done an excellent job in preventing this, plus it's only possible for as many as only two people to ever be working on the same record in the first place because only two people head up the main data entry and they are almost never working on the same set of records or have the need to view them, at my request.

I'm sort of lucky in that my users have that "fear" of messing up so when something happens in this database, they let me know ASAP...now, if I could just clone a couple of them maybe I could get back to the golf course once in a while?$?#?!...FoRe?$??!!

Is another user actually editing the same record? We have had this problem when a popup from a form opened a second form with the same record (special update of fields they said) and basically they (one user) had the same record locked at one time. Just thought it worth a mention, not that you may be doing the same thing, but you have to concider what else they have going on when they get that error.

 

[Kodo]

KODO:

Since this database features both standard Access forms design as well as a wealth of VBA for some more advanced user features and data functions, where in the code would I place the SQL statement for "adLockPessimistic"?

At the very beginning of the main user's form (On Current or On Open event)that causes this problem or on the Properties sheet of the form itself?

You need to place it in your execution of the SQL statement.
You'll need to figure out what Cursor Type you want to use too.

USING ADO

set rs=new adodb.recordset
SQL="......."
rs.open SQL, conn, adOpenDynaset, adLockPessimistic

now your recordset is open and you can see changes made to the database, but what ever record you're on in the recordset is locked by you until you close your recordset.

rs.close
set rs=nothing

 

[cooh23]

Did you ever get this one fixed? I am having the exact same problem.

My database crashes sometimes when a user paste notes into the memo field.

 

[cooh23]

Is another user actually editing the same record? We have had this problem when a popup from a form opened a second form with the same record (special update of fields they said) and basically they (one user) had the same record locked at one time. Just thought it worth a mention, not that you may be doing the same thing, but you have to concider what else they have going on when they get that error.

FoFa, this is exactly what i have. The users double click on the field to get a zoom box to enter their notes and then i have a save button that saves and closes the box. but sometimes I can't save and i get the write conflict.

I am using access as the backend and front end.