Access 2000 - Resetting AutoNumber data type

[kevin03]

Is it possible to reset the AutoNumber data type back to 0 or 1 using VBA?

For instance I've heard that using the TRUNCATE SQL statement in SQLServer provides the ability to reset its AutoNumber data type equivalent. I've tried to use TRUNCATE in Access 2000 without success?

[schlauberger]

The only way that I am aware of, is to delete the field and insert a new one with the same name and select Autonumber. Of course if relationships exist the problem is more complex.

[rwendel]

If you want to do reset the autonumber... two things to do...

If you have records that you want to keep as the same 'ID' then simply go to

Tools / Database Utilities / Compact Database

What this does is gets rid of all the excess info on a table, including what the last number used was. What then happens for the AutoNumber field is the next number used is MAX( ID )+1, so if you deleted ALL records, then compacted the database, the AutoNumber field would then be set to 1 on the first record.

[kevin03]

I need to be able to reset the AutoNumber field programmatically using VBA. I dont think you can invoke the Compact Database option programmatically?

I have looked at using DDL Queries and running these through DAO at runtime which can get complex - drop field contraints, drop field, add field, add constraints, etc. However even after doing all this (if you add the AutoNumber field back in your table with the same field name) Access still has a reference to the old AutoNumber field name and if you try to add a new table row in the newly modified table Access uses the old corresponding AutoNumber value + 1!

So I've decided to write my own GetNextAutoNumber function and have a NextAutoNumber table which stores all the next AutoNumber values available for the ID fields of my different tables. Its an extra little overhead but it gets the job done.

[rwendel]

http://support.microsoft.com/default...en-us%3B287756

DEALS EXACTLY WITH THIS ISSUE...

METHOD 2:

these must be referenced:

Microsoft ActiveX Data Objects 2.x
Microsoft ADO Ext 2.x for DDL and Security Libraries

here is the code they say to use:


Function ChangeSeed(strTbl As String, strCol As String, lngSeed As Long) As Boolean
'You must pass the following variables to this function.
'strTbl = Table containing autonumber field
'strCol = Name of the autonumber field
'lngSeed = Long integer value you want to use for next AutoNumber.

Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column

'Set connection and catalog to current database.
Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn

Set col = cat.Tables(strTbl).Columns(strCol)

col.Properties("Seed") = lngSeed
cat.Tables(strTbl).Columns.Refresh
If col.Properties("seed") = lngSeed Then
ChangeSeed = True
Else
ChangeSeed = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing

End Function

You should read the entire article first of course.
Your individual mileage may vary.

Hope this helps!

[kevin03]

The ChangeSeed() method works.

My application is split in two: Client Application Project and Database Project.

I included the ChangeSeed method in a module within my Database Project. From within my Client Application I created an instance of Access containing the Database Project and used the Application.Run() method to call ChangeSeed which successfully reset an AutoNumber ID field to a given long value.

Thanks!

[rwendel]

What are you doing the app side in?