Hi,

Record Index Key (autonumber) is corrupted - Can't Compact Repair Database Access-2007.

Some fields become Chinese. What is the easiest way to recover corrupted records (specially index key)?

You may have to create a new blank database and copy the structure and import the data into the tables using append queries.

David

You may have to create a new blank database and copy the structure and import the data into the tables using append queries.

David

Thanks David
Yeah this is the only solution i am thinking to do :(. but the problem is my record index (primary key) will be changed if I import the data to new db, I found that there are some records also been deleted from the main table so the autonumber key will be differ from the original one.

Khalid

you may be able ot manually delete the corrupted items - but its hard to resolve stuff like this.

copy the dbs, as almost everything will make it worse

is the dbs split correctly - this may be a reason for the problem.

Here are some thoughts from a different forum

These are presented in oldest to newest, so you must read top to bottom.

12/14/2009
I am getting an error 3022 - "changes you requested were not successful because they would product duplicate values..." etc. when the following code gets to the .Update line.

Dim rstCOCITemp As DAO.Recordset

'add to check-out/check-in table the number of times it's in quantity

Set rstCOCITemp = db.OpenRecordset("Select * FROM tblCheckOutCheckIn") With rstCOCITemp

.AddNew
!fldCOCIRADID = argRADID
!fldCOCIInventoryID = argPackageID
!fldCOCIInventoryItemID = Null
!fldCOCICheckOut = Null
!fldCOCICheckIn = Null
.Update


fldCOCIRADID, fldCOCIInventoryID, fldCOCIInventoryItemID, are all indexed, duplicates OK, default value zero. db is set to Currentdb.

Any ideas why this chunk of code should generate an error 3022?

MTIA
If there is an autonumber field in the table, try to open the table and add a record manually. Does the autonumber field try to use a value that is already in the table? I have seen this happen a few times. The solution is to reset the initial value for the autonumber field to be one greater than the highest value already in use.
I added a record manually with the same data as the preceding record - no problem there. The autonumber field incremented normally. But I could try your suggestion. How do you reset the autonumber field to be one greater than the highest value already in use?
Oddly, adding the record manually seemed to have 'fixed' the problem.
Odd. It is indeed very strange. The important thing is that it is fixed and now working properly.

Although you no longer need the fix, here is the code that I have used to reset the seed for the autonumber field.

Public Sub FixEmpID()
Dim lngSeed As Long
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT Max(tblEmployee.atnEmpID) AS MaxOfatnEmpID FROM tblEmployee;"
Set rs = CurrentDb.OpenRecordset(strSQL)
lngSeed = rs("MaxOfatnEmpID") + 1
rs.Close
Set rs = Nothing
strSQL = "ALTER TABLE tblEmployee ALTER COLUMN atnEmpID COUNTER(" & lngSeed & ",1)"
CurrentDb.Execute strSQL
strSQL = ""
End Sub