autonumber corruption after mdb conversion

  • Thread starter Thread starter Claude
  • Start date Start date
C

Claude

Hi all,

I've done the following today :

We still have some Access 2.0 MDB files in production and I wanted to
convert it to ACCESS 2000 format (and of course also adapt the little
applications still using it with DAO 3.6).

The conversion process worked fine (but a little bit long). I have now my
DBs in the Access 2000 format.
Good, so I run my (modified) application, insert some new records and,
Surprise, I got some duplicate records with Autonumber fields value
!!!!!!!!!!!!!
I take the MS ACCESS 2002 application, and do the same manually : Open the
table, go to the new row, and enter some values in the new reocrd(s fields,
What I've got then is a strange number for the Autonumber field : -139564.
adding some new records, I've got the same errors asin my application.
Autonumber ??? doesn't it to be unique values ?????????

I, then, browse to the MS support KB. and discovererd than the JET 4 is in
cause and must install the SP7. Ok, I've downloaded it and installed it. But
it doesn't change anything else.
Strange, but I'd like to go further,
I restart the conversion, with the Jet 4 SP7. and now it seems to work
without duplicate records, but the value of the autonumber is now starting
at (eg) 242660. (my last autonumber generated when my db was in the Access2
format was 1265).

What can I do to have my autonumber field to follow the last valid entry
????????

The same thing occurs if I covert my Access 2.0 to Access 2002 format, but
the next autonumber was even greater (603456)

Thanks
Claude
 
Hi, Claude!

Compact your database (Tools -> Database utilities ->
Compact database). After it AutoNumber field will
continue with N+1, where N is the maximum number in the
AutoNumber field. Be sure that the AutoNumber field's new
value is taken by increment, not by random. See the
properties of the AutoNumber field.

Karcsi
 
The function below resets the AutoNumber after import. It will not sort out
problems created by new records being entered since then.

The function relies on the ADOX library to reset Seed property of the
AutoNumber field in each of the non-system tables in your database. It has
not been tested, so please back up your database before applying.

Instructions:
1. Back up your database.

2. In the Database window, click the Modules tab, and click New.

3. Choose References from the Tools menu, and add a reference to:
Microsoft ADO Ext. 2.6 for DDL and Security

4. Paste the function below into the new module.

5. Check it compiles (Debug menu). For exmaple, word wrap in email may give
problems.

6. Open the Immediate window (Ctrl+G)

7. Enter:
? FixTable()
You should see a list of the tables that are being changed, in this pattern:
Tablename Autonumber field old seed => new
seed.

8. When no longer needed, remove the reference to the ADOX library, and
delete the new module.

----------code begins--------------
Function FixTable()
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim varMaxID As Variant
Dim strTable As String

Set cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
strTable = tbl.Name
If tbl.Type = "TABLE" Then
If Left(strTable, 4) <> "Msys" And Left(strTable, 1) <> "~" Then
For Each col In tbl.Columns
If col.Properties("Autoincrement") Then
If col.Properties("Seed") < 0 Then
varMaxID = DMax("[" & col.Name & "]", "[" &
tbl.Name & "]")
If Not IsNull(varMaxID) Then
Debug.Print tbl.Name & Space(40 -
Len(tbl.Name)) & col.Name, col.Properties("Seed"),
col.Properties("Seed") = CLng(varMaxID) + 1&
Debug.Print " => " & col.Properties("seed")
End If
End If
Exit For
End If
Next
End If
End If
Next

Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
End Function
----------code ends--------------

Explanation:

Version 4 of the JET engine (Access 2000 and 2002) supports altering the
Seed for an autonumber, which is why they got it wrong, of course. They have
not updated DAO to handle the new JET features, but you can get at it
through ADOX. They've really got in a muddle over this, but the above code
seemed to work here, and is more efficient than having to visit every table
by hand.

The code:
- visits each Table in the Catalog of the CurrentProject,
- skips the queries (which are tables to ADOX) and the system/temp tables,
- loops through the columns until it finds the autonumber,
- checks if the seed is below zero,
- gets the highest value in the table,
- adds one, assigns the result to the Seed.
 
Thanks to both of you.

Karcsi, I've done this twice, in both Access version (2000 & 2002), one
during the conversion process and a second one when compacting, but neither
does the trick.

Allen, I'm just finishing a little VB program that does exactly the same
thing.

Claude
 
Back
Top