Renumbering MS db ID?

[Mr. B]

I have a small MS db which I update daily with new info. I've noticed that
the ID numbers are increasing sequentially. How can I reset them back to 1
when I purge my data prior to adding in the new stuff?

Here is how I'm currently deleting the info from my db (TSUpdate):


Private Sub ClearMyData()
' Open Connection and Clear Data
OleDbConnection1.Open()
Dim MyCommand As New OleDbCommand("DELETE FROM TSUpdate",
OleDbConnection1)
MyCommand.ExecuteNonQuery()
OleDbConnection1.Close()
MyCommand.Dispose()
End Sub


Thanks in advance!

Bruce

 

[Norman Yuan]

What is the "MS db"? MS Access or MS SQL Server?

If I understand you correctly, you use AutoNumber (MS Access), or
Identity(SQL Server) as ID field value. There is no need to care what the ID
field's value is, as long as it is unique in the table, autonumber/identity
guarrantees that and it is the only purpose autonumber is used for. If you
want the ID value in particular order, you have to roll out your own logic
to achieve that, and it is not an easy task to do.

However, if you regularly delete every thing in the table and really want
the new record's ID starting fro 1, you can compact the database after
deleting, if you use Access DB (there is also a way to do it in SQL Server
to re-seed the idetity field, I believe, but, why bother?).

 

[Mr. B]

What is the "MS db"? MS Access or MS SQL Server?

Sorry... Access

However, if you regularly delete every thing in the table and really want
the new record's ID starting fro 1, you can compact the database after

Compact? Hmmmm... that simple? I'll try it.

deleting, if you use Access DB (there is also a way to do it in SQL Server
to re-seed the idetity field, I believe, but, why bother?).

Well... I just 'thought' that there might (eventually) be a limitation on the
ID number (true?). So... rather now than later, I wanted to address that!

But I'll try the compacting and see what goes from there.

Thanks!

Bruce

 

[William Ryan eMVP]

Check out "Identity" in Sql Server's Books Online. There's an in depth
discussion on how to handle this depending on if there are already gaps or
not

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

 

[William Ryan eMVP]

Oops, just saw you were referring to access. I see Norman already
recommended Compacting the DB. This may also be of interest...
http://www.techonthenet.com/access/modules/next_nbr.htm
http://www.techonthenet.com/access/tables/reset_counter.htm


--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

 

[William Ryan eMVP]

Norman:

I'm not an Access user, but as far as the notion that 'there is no need to
care what the ID field's value is', at least with SQL Server I can think of
more than a few instances where this wouldn't be the case - it really
depends on the app requirements don't you think?. You can use for instance
TinyInt, SmallInt, BigInt, Numeric, Decimal etc. If you had a lot of
deletions and a scenario where you'd never have more than say 100 items in
a given table, you could safely specify the use of a TinyInt value and just
recycle things. If this was a pretty volatile table you could easily go
over 255 necessitating the use of another datatype. We do this currently
with our employee badge system and since the license comes in one block of
100, then 700 then 1000 - recycling the ones makes a lot of sense. I've
also seen a few implementation where they used identity fields to count
concurrent users for licensing purposes and reusing those values was used to
facilitate this counting. I'd agree it's easy enough to use int instead of
tinyint and implement a different scheme for concurrency tracking, but in
those instances it was a fairly straightfoward way to accomplish the tasks.
Similiary if your app is such that you can keep everything sequential, it
would lend itself a little better to iteration and the like. Again not a
big deal and certainly something you could do without, but not necessarily
something I'd write off entirely.
Cheers,

Bill

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

 

[Paul Clement]

¤ I have a small MS db which I update daily with new info. I've noticed that
¤ the ID numbers are increasing sequentially. How can I reset them back to 1
¤ when I purge my data prior to adding in the new stuff?
¤
¤ Here is how I'm currently deleting the info from my db (TSUpdate):
¤
¤
¤ Private Sub ClearMyData()
¤ ' Open Connection and Clear Data
¤ OleDbConnection1.Open()
¤ Dim MyCommand As New OleDbCommand("DELETE FROM TSUpdate",
¤ OleDbConnection1)
¤ MyCommand.ExecuteNonQuery()
¤ OleDbConnection1.Close()
¤ MyCommand.Dispose()
¤ End Sub
¤
¤
¤ Thanks in advance!

See the following MS KB article:

How to reset an AutoNumber field value in Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;812718


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Rebecca Riordan]

Bill, FYI, Access Autonumbers are Longs. There's really no reasonable
danger of an overflow.

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress

 

[Mr. B]

See the following MS KB article:
How to reset an AutoNumber field value in Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;812718

Thxs Paul...

Bruce

 

[Terry Kreft]

If you're using an Autonumber field or an Identity column to represent
something in the real world (such as a badge ID) then you are using it for
the wrong reasons. They should be used as an easy way to achieve a unique
key on the table, that's all.