Jet database engine error has me stumped!!!

[trifest]

Ok here's the scenario. I have a form that has 2 subforms so it is pulling from 3 tables. the tables have the following record counts in them

table 1: 70,000 records
table 2: 16,000 records
table 3: 18,000 records

These are all tied into a data entry form. Here's my problem. I'm getting "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time." I've researched and it says that it is due to corruption.

Here's what I've done. It's set up with cascading deletes from the main table so I started checking blocks of data and just when I think I have a block of data narrowed down, I error out again. It seems that if I reduce the number of records in half I can't recreate it no matter what block of data I use. Does this make sense? I've tried compacting and repairing and using JetComp.exe but no luck.

Please somebody, my client is ready to kill me and I can't figure this out. Any help would be appreciated.

 

[jdegeorge]

I've heard that Access starts to act funny when tables get 65,000 records in them. It's a lot for it to process. Even though you've split things into 3 tables, you seem to still join them together for your queries. Is there a way you can archive some of the data and not include it? That might help.

Jim DeGeorge

 

[trifest]

hmm sounds like a good point. do you have any evidence or articles to back that up. I'm only asking because my client doesn't believe that it could be that. I'm under the gun and if you could supply some documentation or expert opinion that would be great.

 

[jdegeorge]

I don't have any articles on that off hand. My mainframe developers have always told me this and I believe them. It's not that the tables can't hold the data, but rather that the acess queries, etc. can't handle it. Rather, they use SQL Server for all coding. Sorry I don't have more details for you.

Jim DeGeorge

 

[psemianonymous]

I've had that error message twice. Both times it was related to a memo text field corrupting (a SINGLE line). If you can find that single corrupted line (the memo will show #Error), delete/re-enter the line and you're good to go.


For more details, see the Corrupt MDBs FAQ, which specifically deals with this error and several ways to identify which record is corrupt.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[trifest]

how would i go about finding that record. I have over 100,000 records so it'd kind of difficult right now.

 

[psemianonymous]

If you're familiar with VBA, the FAQ has a way to loop through each record. If you're not, well, you can do it the long way, which involves Copy/Paste and Excel. I don't think Excel can handle that many records. Pasted from the FAQ itself:


Damaged records within tables
Preliminary steps
Create a new MDB. Minimize the objects window as Access wastes significant time redisplaying that window for each new object. Import all objects and relationships except for the table in question. Import just the definition of that table.

Locating damage records via scrolling
However one, or more records within a table may be corrupt. Especially memo and ole fields. Scroll down the table until Access pukes. Then start back a ways and slowly go down until you find the record which causes the problem. Cut and paste those records into another table. Then start from about thirty records, or however many records you can see on the screen plus a few, past the record which puked, scroll up a record at a time to ensure only one record has a problem and then keep on going down.

Locating damage via append queries as described by Peter Miller
If you can open the database, and simply can't resolve this problem with one table, your solution is relatively simple. Import just the table definition for the corrupt table (from the import dialog, make sure to choose the table definition only option rather than table def and data) into a new database. Then create a link to the corrupted table in the old database. Now create an append query to append all fields from the old table to the new table. Don't specify each one. Just use the tablename.* option. Now run the query. How far did it proceed without an error? Specify a key field in the query and limit it to a certain range of values. Exclude the key field value for the corrupted record(s). Your uncorrupted records will now be appended to the new database.

Locating damage via code
A third method to locate minor corruption, especially in memo fields is to write some code which loops through the records looking for an error when accessing the memo field(s)

With rs
   Do While Not .EOF
      If IsError(!tableMemo) Then
         Debug.Print !tableID
      End If
      MoveNext 
      Loop
End With

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm