Reset Autonumber - a quicker way ?

[Chance1234]

i know how to reset the autonumber on tables , but does anyone have any add-ins or code to do it quicker as i have a ot of tables to do

Filmmaker, gentleman and pearls before swine fan

http://WWW.5YLAC.S5.COM

 

[knownote]

Chance1234,

IF your database is not split, and/or you have a large
amount of existing data, below may be the quickest
"one table at a time" method. Otherwise, someone will
point you to an easier method using an add-in or code.

To absolutely reset an autonumber field: If the autonumber
is a part of a relationship as a primary or foreign key,
delete (temporarily) the relationship. Next, open the
table in design view. Select the field. If a primary key,
deselect key icon. Cut (NOT Delete) selected autonumber
field, insert a blank row, and paste autonumber field back
again. Save and close table.

Repeat for related table(s) if other autonumbers involved
to reset them also (otherwise these related autonumber
fields may not match up). Finally, restore relationship as
it was a few minutes ago.

Hope this was helpful.
John

 

[dhookom]

I am a strong believer that if you care what value an autonumber field contains, you aren't using it as intended. Users of applications should never see an autonumber.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Chance1234]

Personally im a great believer in not using it at all, but currently sorting out some very messed up database's. knownote thats the method i know, looking for a add-in/code that will do that for us.

Filmmaker, gentleman and pearls before swine fan

http://WWW.5YLAC.S5.COM

 

[chris153]

Hi,

If you compact the database all autonumber fields are reset within each of the tables.

 

[MakeItSo]

Chris: ???
Since when does compacting a db have any influence on any field content?

 

[chris153]

MakeItSo???

It should work if you haven't installed Microsoft Jet 4.0 Service Pack 4 (Msjet40.dll version 4.00.2927.4) or later.

Have a look at this link which shows a work around if you have a later service pack.

http://support.microsoft.com/default.aspx?scid=kb;en-us;287756&Product=acc

Give it a try - I look forward to you eating plenty of humble pie !

 

[MakeItSo]

chris:
This applies only to the case when you have deleted the last record and serves for the upfollowing records. It does not reset the autonumber field in general, which is what I believe Chance is looking for.

Chance:
I couldn't figure out the correct statement, but:
You could cycle through your tables via VBA:

Dim tbl As TableDef
Dim sSQL As String
For Each tbl In CurrentDb.TableDefs
   If Not InStr(1, tbl.Name, "SYS") Then
    sSQL = "ALTER TABLE " & tbl.Name & "CREATE COLUMN Newid ....."
    DoCmd.RunSQL sSQL
Next tbl

My problem herewith is the ALTER TABLE statement. I'm not very familiar with it and you need to drop constraints first, before dropping the autonumber column.
background:
With the ALTER TABLE statement, add a new autonumber-ID column, set it as Primary Key and the drop the former Primary key constraint and the former Autonumber field.

Hope this helps,
MakeItSo

 

[LarryDeLaruelle]

I don't believe there is any quick way to reset a number of tables.

After I have finished debugging and testing and am ready to deploy, I rename all the tables (where I have used an autonubmer) to something like Old-tblName.

Then for each, I copy and paste selecting the structure only option. The new table will start autonumber from one.

HTH

Larry De Laruelle
ldelaruelle@familychildrenscenter.org