How to Primary Key begin 1 after "delete all data". (2 Viewers)

smtazulislam

Member
Local time
Today, 23:16
Joined
Mar 27, 2020
Messages
806
Hello dears,
I have 16 tables in a database. When delete old all of data from table and Query via VBA code than primary keys (AutoNumbers) all of table (16 tables) become start from last record numbers.
Any example : one table was 30 data's after delete and database have been start from 31.
But I need it is begin number - 1 in new database .....
NOTE : Manual I know, Please VBA code.
 

plog

Banishment Pending
Local time
Today, 15:16
Joined
May 11, 2011
Messages
11,611
First, no you don't. Primary keys mean nothing externally, they are simply used to uniquely identify records. 1, 31, 715, or even RPX&*); its purpose is to ensure uniqueness.

With that said. Compact and Repair.
 

smtazulislam

Member
Local time
Today, 23:16
Joined
Mar 27, 2020
Messages
806
this is the primary key, as same primary key have 16 tables 16 primary key.
 

Attachments

  • Capture.PNG
    Capture.PNG
    29.6 KB · Views: 191

smtazulislam

Member
Local time
Today, 23:16
Joined
Mar 27, 2020
Messages
806
Manually is easily. Goto table Design view. Remove primary key and reopen your table.
Then again go to table design view in insert new row and write previous field as same name. than add new row primary key and delete old primary key row.
Hi. Just curious, how do you do it manually?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:16
Joined
Oct 29, 2018
Messages
21,358
Manually is easily. Goto table Design view. Remove primary key and reopen your table.
Then again go to table design view in insert new row and write previous field as same name. than add new row primary key and delete old primary key row.
Hi. Thanks for the clarification. If you are deleting all the records, what happens if you do a C&R, like what @plog said?
 

isladogs

MVP / VIP
Local time
Today, 20:16
Joined
Jan 14, 2017
Messages
18,186
As @plog has already stated, running a Compact and Repair will 'reseed' the autonumber field.
However, again to repeat, there is no real purpose in doing so and autonumbers are just intended as a unique identifier.
The value isn't important and indeed the order shouldn't be relied on.

Starting with A2010, it is not possible to compact the currently open database using VBA.
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:16
Joined
Sep 21, 2011
Messages
14,047
I would only do it after testing and to start off afresh before I would go live. As mentioned there is no real need normally otherwise.

However here is what I use after testing.

Code:
Sub ResetTableNumber(pstrTable As String, pstrID As String)
Dim strCmd As String, strSQL As String
strSQL = "DELETE * FROM " & pstrTable
strCmd = "ALTER TABLE " & pstrTable & " ALTER COLUMN " & pstrID & " COUNTER(1,1)"
CurrentDb.Execute strSQL
CurrentDb.Execute strCmd
End Sub

I was not aware of the C&R option. :(

HTH
 

zeroaccess

Active member
Local time
Today, 15:16
Joined
Jan 30, 2020
Messages
671
As long as none of those autonumbers are primary keys of lookup values. Hello corrupted data!

Did not know C&R reset autonumbers. Typically I would copy and paste the table (Structure Only), then copy all the records - they will all get new IDs starting from 1.
 

smtazulislam

Member
Local time
Today, 23:16
Joined
Mar 27, 2020
Messages
806
I would only do it after testing and to start off afresh before I would go live. As mentioned there is no real need normally otherwise.

However here is what I use after testing.

Code:
Sub ResetTableNumber(pstrTable As String, pstrID As String)
Dim strCmd As String, strSQL As String
strSQL = "DELETE * FROM " & pstrTable
strCmd = "ALTER TABLE " & pstrTable & " ALTER COLUMN " & pstrID & " COUNTER(1,1)"
CurrentDb.Execute strSQL
CurrentDb.Execute strCmd
End Sub

I was not aware of the C&R option. :(

HTH
......................... ? Sub ResetTableNumber(pstrTable As String, pstrID As String)
What is code in the blank dot place...
can you attached a db please
 

Mike Krailo

Well-known member
Local time
Today, 16:16
Joined
Mar 28, 2020
Messages
1,030
Learned something new but the data has to be removed for the autonumber to actually reset back to one, correct? No idea why that is necessary. Also have no idea what "blank dot place... " is either.
 

smtazulislam

Member
Local time
Today, 23:16
Joined
Mar 27, 2020
Messages
806
As long as none of those autonumbers are primary keys of lookup values. Hello corrupted data!

Did not know C&R reset autonumbers. Typically I would copy and paste the table (Structure Only), then copy all the records - they will all get new IDs starting from 1.
I know that, and previous i reply it. actually this database my client dont know table name or field name. bcoz, EXE file. but they want after reset or delete should be create start number 1 and autonumber. that all.
 

isladogs

MVP / VIP
Local time
Today, 20:16
Joined
Jan 14, 2017
Messages
18,186
Compacting will always reset the autonumber to the lowest unused number.
So if the table is first emptied it resets the value to start at 1.
No need to copy all records into a new table.
 

smtazulislam

Member
Local time
Today, 23:16
Joined
Mar 27, 2020
Messages
806
Hi. Just curious, how do you do it manually?

if you used office 2013 after any version then you can try others system, LIKE Delete all data and re-open your database again then goto Database Toots and click Compact and Repair Database. Then it begin number 1
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:16
Joined
Oct 29, 2018
Messages
21,358
if you used office 2013 after any version then you can try others system, LIKE Delete all data and re-open your database again then goto Database Toots and click Compact and Repair Database. Then it begin number 1
Hi. Yes, I think we established that already. User's shouldn't care or need to know what the Autonumber value is. It's only for the system's use. For example, take a look at this Wiki article.

 

zeroaccess

Active member
Local time
Today, 15:16
Joined
Jan 30, 2020
Messages
671
Compacting will always reset the autonumber to the lowest unused number.
So if the table is first emptied it resets the value to start at 1.
No need to copy all records into a new table.
I guess my use cases are different. I had some cases where I needed to renumber the records, but not delete them.
 

isladogs

MVP / VIP
Local time
Today, 20:16
Joined
Jan 14, 2017
Messages
18,186
I can understand the logic in resetting the autonumber values after deleting all test data prior to releasing an app to clients.
However I can't think of any reason to justify any NEED to do that without first deleting all records.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:16
Joined
Feb 19, 2013
Messages
16,553
But I need it is begin number - 1 in new database .....
another way if you are effectively creating a new database is to create a new blank database then import all objects, but before you click OK, click on options and set import tables to Definition Only.
 

zeroaccess

Active member
Local time
Today, 15:16
Joined
Jan 30, 2020
Messages
671
I can understand the logic in resetting the autonumber values after deleting all test data prior to releasing an app to clients.
However I can't think of any reason to justify any NEED to do that without first deleting all records.
Mostly related to tidying up after testing; before deployment of database.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:16
Joined
Feb 28, 2001
Messages
27,001
If you are showing any synthetic keys to any of your users, you do them a grave disservice. It is a number they do not need to see or know and a number that will never be of value to them in any well-designed database. Therefore it should not matter whether you reset them or not.

Having said that, I have had occasion when testing something to use the DELETE * FROM table; followed by the ALTER TABLE ...ALTER COLUMN .... COUNTER(1,1) method. But it is usually safer, faster, and more reliable to do the DELETE method for all relevant tables followed by the C&R.
 

Users who are viewing this thread

Top Bottom