the autonumber reset solution (1 Viewer)

[antonyx]

http://www.applecore99.com/tbl/tbl017.asp
this site shows how to reset the auto number, it has 2 steps

1. delete all the data
2. compact the database

i can do number 1 obviously, what does compact the database mean, what you gotta do?

 

[mcadle]

Open the database exclusively then click tools - database utilities - compact and repair database.

 

[antonyx]

great

nice one matey

 

[yhgtbfk]

Open the database exclusively then click tools - database utilities - compact and repair database.

Can this be done from visual basic incidently?

 

[WayneRyan]

y,

It can be done with VBA, but the core problem remains ... Why do you care
what the AutoNumbers are. They are just pointers that identify your unique
records.

Use the Search Facility here and look for "AutoNumbers", it's a common topic.

Wayne

 

[yhgtbfk]

y,

It can be done with VBA, but the core problem remains ... Why do you care
what the AutoNumbers are. They are just pointers that identify your unique
records.

Use the Search Facility here and look for "AutoNumbers", it's a common topic.

Wayne

Thats a very good answer Wayne...But you misunderstood what I was asking

Can this (compact and repair database) be done from visual basic incidently?

Sorry to go off topic a bit and confuse you.

 

[WayneRyan]

y,

Not to worry ... I'm easily confused.

http://www.candace-tripp.com/_pages/access_downloads.asp

Wayne

 

[antonyx]

haha

i care drastically about auto numbers, and think they are the most important numbers in the world, and more time should be spent discussing them.

 

[RoyVidar]

If you use autonumbers to maintain the referential integrity of your database, sure autonumbers are important, but I agree with WayneRyan, the actual numbers are irrelevant, they are just unique numbers identifying each record.

The most common confusion when using autonumbers, as you would find thru a search, are that they are not intended to be sequental numbers. They leave gaps if you cancel new records. You cannot reuse "old" auotonumbers (deleted autonumbers), which is one of the reasons for the view shared among, I think a lot of/most Access developers, that the autonumber field should not be exposed to the user (or the other way around), so that there should not be any business meaning assosiated with it.

Sometimes they don't even provide unique numbers, creating dupe value in index..., which is a recognized bug/flaw in Access 2003, and I've read something about it also occuring in some multiusersettings.

Microsofts workaround for the bug, is resetting the autonumbers thru something like this:

strsql "alter table YourTable alter column YourAutoNum counter(N,M)"
docmd.runsql strsql

Where N represents "starting number"
M represents increment

see http://support.microsoft.com/?scid=kb;en-us;884185

 

[mcadle]

I agree. If you properly index the appropriate related fields then the autonumber is really insignificant. I have however had to create a multiple table auto-increment system to track purchases on different cards/different card holders. I finally got the bugs worked out. That is the only time where a auto-incrementing number has mattered, for me at least.

 

[WayneRyan]

y,

When I first started with databases, if you had a "tree structured" heirarchy
you had to refer to a node as "Key1 = x And Key2 = y And Key3 = z". There was
no unique (single) identifier for a particular record.

AutoNumbers conveniently let you refer to a location in the data tree with one
pointer. If some of the data happens to change (last name, employee number)
the link is still valid.

I like 'em, don't wanna see 'em, couldn't live without 'em.

Wayne

 

[paul young]

Deleted and compacted but still old data appeared.

After created a database governed by an autonumber field, I entered a number of records. Each record had a subform governed by a field (in this case, area name). I entered figures into the subform to test it. Then I deleted the records and compacted the database to start again. Next I started a new record with an autonumber resetted at 1. Then I typed in an area name and its old subform data reappeared. How can I start again properly with all previous data really deleted?

 

[Rich]

You have to set the Cascade Delete related records in the relationships window, but then you haven't selected the Enforce Referential Integrity options either