We continue to have problems with autonumber's in Access 2000. We have
upgraded our Access Runtime to Jet 4.0 SP8 and we now have a problem
adding
data to a table because the autonumbers are broken.
The problem is creted by a database tool we developed to help fix our
clients databases. This tool copies data field by field from a damaged
database to a new clean database. The following is the code section of
function we use to do this.
Function MILL_ConvertDatabase()
On Error GoTo ERR958462
Dim WS As Workspace, DB As Database, TempDB As Database
Dim TableCurrent As String, TableNew As String
Dim RSC As Recordset, RSN As Recordset
Dim I As Integer
Dim NumFields As Integer
Dim RS_TABLE As Recordset, RS_ERROR As Recordset
Dim QD As QueryDef, TD As TableDef
Dim CurrentRec, MaxRec As Long
Dim skipMemo As Boolean
Dim fieldnum As Integer
'****
Set TempDB = CurrentDb()
Set WS = DBEngine.CreateWorkspace("xxx", "xxx", "xxx")
Set DB = WS.OpenDatabase(TempDB.Name)
Set QD = DB.QueryDefs("AQry")
Set RS_TABLE = DB.OpenRecordset("tblTableList", DB_OPEN_DYNASET)
'****clear out the error log
TableNew = "Error Log"
QD.SQL = "DELETE DISTINCTROW [" & TableNew & "].* FROM [" & TableNew
&
"];"
QD.Execute
'****open recordset for errors
Set RS_ERROR = DB.OpenRecordset("Error Log", DB_OPEN_DYNASET)
'****clear out data pump tables
RS_TABLE.MoveLast
While Not RS_TABLE.BOF
'****clear out the fields in new database
TableNew = RS_TABLE!TableName & "_New"
QD.SQL = "DELETE DISTINCTROW [" & TableNew & "].* FROM [" &
TableNew
& "];"
QD.Execute
RS_TABLE.MovePrevious
Wend
'****Transfer the data in tblTableList
RS_TABLE.MoveFirst
While (Not RS_TABLE.EOF)
'****set some variables
TableCurrent = RS_TABLE!TableName & "_Current"
TableNew = RS_TABLE!TableName & "_New"
'****get the number of fields in the current table
NumFields = DB.TableDefs(TableCurrent).Fields.Count
'****open a recordset for current and new tables
Set RSC = DB.OpenRecordset(TableCurrent, DB_OPEN_DYNASET)
Set RSN = DB.OpenRecordset(TableNew, DB_OPEN_DYNASET)
Set TD = DB.TableDefs(TableCurrent)
'****for each record in current, copy over field by field
If RSC.EOF = False Then
RSC.MoveLast
RSC.MoveFirst
End If
MaxRec = RSC.RecordCount - 1
While Not RSC.EOF
skipMemo = False
CurrentRec = RSC.AbsolutePosition
'**** Add if-statements here to skip over the memo fields in
the
table and record number you specify
'**** Add as many if's as needed
'If RS_TABLE!TableName = "PUT YOUR TABLE NAME HERE" And
CurrentRec = PUT YOUR RECORD NUMBER HERE Then skipMemo = Tru
'**** End if-statments
RSN.AddNew
For I = 0 To NumFields - 1
If Not (RSC(I).Type = dbMemo And skipMemo = True) Then
RSN(RSC(I).Name) = RSC(I)
End If
If RS_TABLE!TableName = "tblSalesOrder" Then
If RSC(I).Name = "strSalesOrderNumber" Then
If RSC(I).Value = "anjee" Or RSC(I).Value = "anjee"
Then
skipMemo = True
End If
End If
Next I ' "Set Next Statement" here if there was an error on
the
previous line
skipMemo = False
RSN.Update
RSC.MoveNext
Wend
RS_TABLE.MoveNext
Wend
QD.Close
RSC.Close
RSN.Close
MILL_ConvertCategories = True
'****check for user reports/queries
result = MILL_ImportUserReports()
'****success
MsgBox "File Pump completed successfully!"
DoCmd.Close acForm, "fdgStatus"
Exit Function
Once we run this function the data is copied to the new set of tables;
however, we are seeing a number of tables where the autonumber is being
set
to a number less than the total number of records. When we attempt to
add
a
record to the table, it creates an autonumber than is the same as an
existing
autonumber value. This application does correctly create an error
message
and does not allow us to complete the record addition. We need to be
able
to
ensure that once we run this function, the next autonumber is unique.
We are not sure if we should go directly to Microsoft on this or if our
routine is doing something to cause this problem.
We attempted is use Graham's suggestion; however, we are having some
trouble
with the syntax. We are hoping you may be able to see a more generic
approach we can use based on the above code segment?
Sincerely,
Robert
:
Robert,
Recreating the database is exactly what caused the problem. All
Autonumber
fields will have been reset, so you will experience similar problems
in
other tables as well. A solution is to create an update query for each
table, which forces a value into the Autonumber field.
INSERT INTO tblMyTable (AutonumberField) SELECT
Max(AutonumberField)+1
FROM tblMyTable
....then you need to delete the record you just added...
DELETE * FROM tblMyTable WHERE AutonumberField = (SELECT
Max(AutonumberField) FROM tblMyTable
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
We have a problem with an Access 2000 table where the KeyID field
was
duplicated. When we look at the table and attempt to append a
record
to
the
bottom of the table, the KeyID number (Autonumber) that is generated
already
exists in our table? I thought this was not possible?
I check the MS KB and found an article relating to older versions of
JET
4.0
that had a problem; however, we have verified that we are running
the
most
current version of Jet 4.0.
We suspect this problem originated when the database was corrupted.
However, we copied the data to a clean table using a custom built
tool
that
copies the data field by field. After this process, the KeyID
(Autonumber)
field seems to be corrupt?
Any ideas on where we can look for the root cause of this issue?
Thank you,
Robert Sombach
