Thank-you.  I will give this a try and see if it works.

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