Compact & repair problem

[Caddie]

Hi -

Recently a user asked me to delete a record from our database (through the back end, the database is split and password protected) which I've done many times with no problems.

However this time, the result is a record that has #DELETE written in each field of the record and I am not able to delete this record. So, I used the Compact and Repair function in Access 2007 (the DB is saved as MDB not the 2007 format) and it removed this corrupted record, however it also changed the structure of the DB.

Originally the tblserviceaddress was linked to tblcontractdetails using an auto-generated number that was indexed and unique, but after the Compact and Repair was finished, it removed the indexing of the auto-generated number field along with the associated link to the other table.

I tried to change the property of the ID field to be "indexed (no duplicates)" and it allows me to do it but when I try to relink the tables it gives me the following error:

Microsoft Access can't create this relationship and enforce referential integrity.

Of course I backed up before compacting the db so I've restored it to that, but this does leave me with two questions:

1 - How do I delete the record that contains all of the fields with #DELETE in them

2 - How do I fix this error I am having with Compacting and Repairing?

Thanks!

 

[Ryuk]

Hi, I gather that your MS Access database is getting corrupt when up put it on a shared drive. A Microsoft Access database may get corrupt when in a multi- user environment. Here are the workaround that you can use in order to fix it.

Step 1: Run Command Prompt as Administrator​

Click on the Windows icon and type Command Prompt. Then right-click on the Command Prompt and choose Run as administrator option.

Step 2: Execute Compact and Repair Database Command​

In the command prompt window, type the following command and then press ‘Enter’.

style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#212529'>msaccess <path to database file >\<database file name> /compact
In the command, replace <path to database file>\<database filename> with database path. For instance,

style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#212529'>msaccess "C:\Program Files\Reports.mdb" /compact
This will start the process to compact and repair the faulty Access database file.

Otherwise, You can check out this thread for an alternative solution like using tools to repair the files eg - **************** for excel, repair for excel etc which you will find on the internet easily

 

[ypma]

caddie, if Microsoft Access can't create this relationship and enforce referential integrity it suggest to me that you still have a duplicate ID , It maybe be a duplicate zero id. I would create a Find duplicate query wizard and see what that produces .

Regards Ypma
.

 

[Pat Hartman]

You may need to copy the data in pieces to get rid of the bad record. Run an append query that selects ID's < badID. Then a second append query that selects IDs > badID+1

 

[Isaac]

I can only hope that in the 11 years that have elapsed since Caddie last posted, they have not only solved this database's problem, but even built many new ones and hopefully accomplished many other grand things in life.