AutoNumbers

[Andrew]

I have been using sample data whilst developing a database, but now want to
reset the autonumber so that I can enter the real data. how do I do this?

 

[Jack Cannon]

That's easy.
Delete all your sample data then just to a Compact and Repair.
That will reset your autonumber.

Jack Cannon

 

[a a r o n . k e m p f]

In SQL Server, I can do this using the command 'TRUNCATE tblEmployees'

of course, SQL Server also lets you set the SEED and INCREMENT values
for Identity Fields-- this is much much much more powerful than a
simple Jet AutoNumber.

Furthermore, Jet AutoNumbers aren't reliable-- they ask you to
constantly come up with some algorithm.. to get around some jet bug.
In SQL Server, you have a lot more power and freedom- because you
could set these with a trigger for example (and Jet, because it
sucks-- doesn't support triggers)

 

[Andrew]

Thank you - I have been trying to find out how to do this for the last 6
hours.. You are a life saver.

 

[Tom van Stiphout]

On Sun, 15 Feb 2009 11:26:04 -0800, Andrew

But keep this in mind: you are apparently assigning meaning to the
autonumber value. That is nearly always a Really Bad Idea. This is a
FAQ; it should not take you much time at groups.google.com to learn
why.
Oh, and ignore Aaron, one of our resident trolls.

-Tom.
Microsoft Access MVP

 

[Jack Cannon]

I agree with Tom and thank him for pointing this out. Assigning meaning to
an autonumber field is "nearly always a Really Bad Idea". In your particular
case there is certainly nothing wrong with resetting it before entering real
data. I do the same thing even though there is no good reason for it. Just
be very carefully about assigning any real meaning to it.

Jack Cannon

 

[Aaron Kempf knows nothing]

In jail, I can do the command 'TRUNCATE my backside'

of course, they also let you set the front down

 

[Tom Wickerath]

Aaron,

of course, SQL Server also lets you set the SEED and INCREMENT values
for Identity Fields-- this is much much much more powerful than a
simple Jet AutoNumber.

JET also allows one to set the SEEL and INCREMENT values, by using some very
elementary VBA code. This has been pointed out to you previously.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Michel Walsh]

Mr Aaron Kempf is wrong on that point.


Can also be done through data definition language (DDL):

============ In the Debug (Immediate) Window ===================

CurrentProject.Connection.Execute"ALTER TABLE table42 ALTER COLUMN f1
AUTOINCREMENT(10000, 50)"

===========================================================

would reset the seed at 10000 and the increment at 50, with JET.



You cannot typically run that alter table command in the sql view of a
query, though. You can run it in the Debug (or Immediate) Window.




Vanderghast, Access MVP