How can I avoid Autonumber skipping in MS Access ? :vb.net 2008

[sam]

Hi,
First, I have to reapologizes for my poor English (both grammar and
spelling).
When I delete a record in MS Access table that have autonumber field and
add other record. The new one will have the number that follow the one
that was deleted. So, when we look at the table (in effect) the
autonumber is skipping. Can we fix that? And how?
TIA
Sam

 

[Patrice]

Hello,

This is an expected and normal behavior. The idea is that an autonumber used
as a primary key should never be reused. If someone tells you to delete
record 10, you won't know if this is the current record 10 or a previous
record 10 you already deleted and whose number were reused...

If you really want the only way is to compact the database (anyway it will
not fill in the gaps between 8, 9 that you deleted, and 10 that is still in
use), so it's basically usefull only when you want to delete all records and
restart from 1...

If you are not in this particular case, just let this work as intended...

 

[Gregory A. Beamer]

Hi,
First, I have to reapologizes for my poor English (both grammar
and
spelling).
When I delete a record in MS Access table that have autonumber field
and
add other record. The new one will have the number that follow the
one
that was deleted. So, when we look at the table (in effect) the
autonumber is skipping. Can we fix that? And how?
TIA
Sam

With SQL Server, you can use dbcc checkident to reset an identity field.
I am not sure what the equivalent command is in Access, however.

The question, however, is whether making the table in proper sequence is
worth both the performance hit and the possibility of error/corruption.
I would state a firm NO to both.

The idea behind autonumber/identity fields is they are derived keys,
which means they have no value to the user other than the fact that they
can look up a record with the key. They are meant to be used once. If
data is deleted, even immediately after inserted, the value is gone from
the list.

What it sounds like you would like is to make the recoreds look prettier
by avoiding holes in numbering. If this is really important to you, you
are probably NOT using the field as a derived key, but have attached
some human importance to the key. If so, you have an architectural
problem, as a derived key sequence should not matter to anyone.

BTW, I would be very wary of playing too much with Access, as it is
prone to corrupt.

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

My vacation and childhood cancer awareness site:
http://www.crazycancertour.com

*******************************************
| Think outside the box! |
*******************************************

 

[sam]

With SQL Server, you can use dbcc checkident to reset an identity field.
I am not sure what the equivalent command is in Access, however.

The question, however, is whether making the table in proper sequence is
worth both the performance hit and the possibility of error/corruption.
I would state a firm NO to both.

The idea behind autonumber/identity fields is they are derived keys,
which means they have no value to the user other than the fact that they
can look up a record with the key. They are meant to be used once. If
data is deleted, even immediately after inserted, the value is gone from
the list.

What it sounds like you would like is to make the recoreds look prettier
by avoiding holes in numbering. If this is really important to you, you
are probably NOT using the field as a derived key, but have attached
some human importance to the key. If so, you have an architectural
problem, as a derived key sequence should not matter to anyone.

BTW, I would be very wary of playing too much with Access, as it is
prone to corrupt.

Peace and Grace,

Thank you for your answers. I get the concept that why it happen but
sometimes it "nice" like I have 10 records and I have deleted the last 2
records. And I add one new record, the new record will have autonumber
of 11. In access, there is a utility that compact and repaire database,
so I kind of wonder if we can do something like that from VB without
invoke Access.

Thank you again
Sam

 

[sam]

Thank you for your answers.
I think I get that concept.
However, on the subject of compacting database, I wrote application that
store image file (using stream object) in Acceess. I noticed that when I
"replace" big image file with smaller one, the size of the database did
not shrink until I compact database with utility in Access. Is it
possible to "compact" it in VB without invoking Access?
TIA

 

[Patrice]

However, on the subject of compacting database, I wrote application that

store image file (using stream object) in Acceess. I noticed that when I
"replace" big image file with smaller one, the size of the database did
not shrink until I compact database with utility in Access. Is it
possible to "compact" it in VB without invoking Access?

I believe MS was providing a command line utility to do that...

A quick search gave me :
http://support.microsoft.com/kb/295334/en-us (jetcomp.exe)

Hope it helps...

 

[sam]

I believe MS was providing a command line utility to do that...

A quick search gave me :
http://support.microsoft.com/kb/295334/en-us (jetcomp.exe)

Hope it helps...

Thank you very much. I will try hope it work with .net.
Sam

 

[Gregory A. Beamer]

Thank you for your answers. I get the concept that why it happen but
sometimes it "nice" like I have 10 records and I have deleted the last
2 records. And I add one new record, the new record will have
autonumber of 11. In access, there is a utility that compact and
repaire database, so I kind of wonder if we can do something like
that from VB without invoke Access.

Compact and repair does not renumber an autonumber, AFAIK. It is primary
to get rid of fragmentation and make sure there are no lost fragments.
In practice, esp. on a large database, compact and repair can lose data,
as it pitches records that are corrupted beyond the point it can save
them. Sometimes, you can still view these records prior to compact and
repair. I am not sure if the latest versions of Access still have this
issue.

As for renumbering, there is nothing in VB or .NET to do it. For compact
and repair, there is nothing either, although someone might have a third
party utility for this feature (doubt it as the market is small, but you
never know).

Peace and Grace,


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

My vacation and childhood cancer awareness site:
http://www.crazycancertour.com

*******************************************
| Think outside the box! |
*******************************************

 

[Patrice]

Thank you very much. I will try hope it work with .net.

Using Process.Start you can launch an EXE file wether or not it has been
build with .NET...

 

[SimeonD]

Compact and repair does not renumber an autonumber, AFAIK. It is primary
to get rid of fragmentation and make sure there are no lost fragments.
<snip>
*******************************************

Hi

Compact and Repair will not regnerate the missing auto numbers.
If all your autonumbers are all missing from the end, (9 and 10 in your
sample) there are ways to reset the next auto number using a query.
http://support.microsoft.com/kb/812718

But I'd avoid that if I was you. Its complex, especially if you have linked
tables. Its generally not a good idea to mess with the Access indexes from
an external program.

Regards
SimeonD