I have a db that was created in Access 2003. It has been split into a front-end and a back-end. It has been written to (~1200 rec) and all of a sudden last Friday strange things began to happen - Database took up to 6min. to open, certain reports stopped working (I kept getting Runt-Time error '3167'. Record is deleted.), some drop downs on forms stopped working, etc. With dropdowns that were "busted" (show no options when clicked), I took the rowsource from them, opened a new query, and pasted the SQL from the rowsource in it and ran it - this new query produced the correct data for the options of the drop down, but it would not work in the dropdown.

So at that point, I decided to run Tools>Database Utilities>Compact and Repair Database... on the front end. Nothing happened. Then I ran it on the back end, opened the front end and everything (Forms, dropdowns, queries etc) worked correctly, except that the first record in the main form contained a bunch of "#" in all fields.

That seemed funny to me so I opened the back-end and looked at the list of tables and noticed a MSysCompactError Table appeared (attached). Also, the tblPatient had a new record added to the top of the table. It had "#"'s in all the fields and the odd thing was that the record somehow was saved without a key field (auto number).

So, now I am unsure what to do - what happened? I think the DB got corrupted and the compact and repair fixed it sort of. Is that correct?

-Can I delete the MSysCompactError table or does it have to remain in the back end?

-What do I do about that new record in tblPatient - Delete it? It serves no purpose as far as I can tell, although if I do it turns out that tblPatient will be short 1 record when compared to the back-up I did before I started this. I am not sure what record was deleted yet, but I am searching.

Thanks for any help.

Hi there,

I think that the cause of the issue I was facing is explained in this article http://support.microsoft.com/kb/308630 . The table MSysCompactError seems to be created by MS Access just as a display to show you what it found wrong due to the Compact and repair database. I found originally that just deleting the table does not cause any problems; however, I found that by correcting the issues and re-running the Compact and Repair Database function, the table is removed by Access. I also found it difficult to find the Error codes to find out what they mean. I lost the link I found but there is one out there on the internet that allows you to program a module to produce all error codes and their descriptions. I never did that as I found the solution below that worked for me. I hope that this can help others if they find themselves in a similar predicament.

Here is the solution that worked for me.

Backup both front and back end
Open Back end and Compact and Repair Database:

Open back end and select Tools>Database Utilities>Compare and Repair Database...
Under Tables, you will now see a new table was created MSysCompactError

Contains errors the Compact and Repair Database created due to the bad record. It actually changes the "#Deleted" into a number of "#"'s in each field. With "#Deleted" in the fields, the record could not be deleted, but after the Compact and Repair Database function changes the fields into "#"'s it can be deleted.

Delete bad Record in tblPatient

Open tblPatient
Select and delete the record (should be the 1st row now)filled with fields containing "#"'s and no intPatientID (auto number field) value
Close tblPatient

Delete what is now an orphan record in tblTable2

Open tblTable2 and search for record with the correct intPatientID from tblPatient (this is the id that you deleted because the record went bad in tblPatient)
Select and delete the record from tblTable2
Close tblTable2

Re-run Compact and Repair Database on the back end:

Tools>Database Utilities>Compact and Repair Database...
This should remove MSysCompactError table from the database

Close Back end
Open Front end and relink tables:

Tools>Database Utilities>Linked Table Manager
Make sure all links are pointing to the back end we just fixed.

close access
(Optional) Open front end and compact and repair database

I found that by doing this step it reduces the size of the front end (In my case by almost 10MB)
Steps:

Open front end
Tools>Database Utilities>Compact and Repair Database...
Database will open and it should run as normal

Hi

One thing I thought might be applicable and you should bear in mind is that if this is a multi user running on a network or as a stand alone either usiing a wireless connection between the front end and the back end Access is notorius for corrupting you database if the connection is dropped, even if it is only for nano second. The record that contained the #'s may have been created during a read/write process. Did you check to see if it was a new record or an existing record that had been corrupted?

It can also bugger up and any indexes if the connection was droped during a sort or when a query was running.

Wherever possible try to avoid wireless connections with Access.

CodeMaster::cool:http://www.icraftlimited.co.uk