Re: Non-unique KeyID's (Autonumber) in Access 2000

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


"Graham R Seach" wrote:

> 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
>
>
> "Robert1105" <Robert1105@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:CF4EBC25-91E3-4C8F-A76C-5E836BE002F4@xxxxxxxxxxxxxxxx
> > 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
>
>
>
.

Relevant Pages

  • Re: Non-unique KeyIDs (Autonumber) in Access 2000
    ... Microsoft Access 2003 VBA Programmer's Reference ... > database to a new clean database. ... > Dim WS As Workspace, DB As Database, TempDB As Database ... we are seeing a number of tables where the autonumber is being ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Non-unique KeyIDs (Autonumber) in Access 2000
    ... Microsoft Access 2003 VBA Programmer's Reference ... > to the next highest number, this will reset the stored Autonumber to the ... >>> database to a new clean database. ... >>> Dim WS As Workspace, DB As Database, TempDB As Database ...
    (microsoft.public.access.tablesdbdesign)
  • Re: book publishing database--design question about tabbed forms
    ... Building a database is like building a house... ... if you care about that Autonumber then best to ... which my query and tabbed form became essentially read-only--unable to be ... click a desired tab. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Set value of one field depending on result of next record in q
    ... The autonumber issue should not be a problem. ... table" query to deal with the issue that S Clark offered a solution with. ... > See http://www.QBuilt.com for all your database needs. ... >> Michael Bond ...
    (microsoft.public.access.queries)
  • Re: Renumbering records
    ... Autonumber is just a special kind of default value, ... represent the sequence in which the records were added. ... I wanted to maintain that order and I thought the database ... That would be a regular replica, ...
    (microsoft.public.access.replication)
Quantcast