How to Primary Key begin 1 after "delete all data".

[smtazulislam]

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]

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.

 

[theDBguy]

NOTE : Manual I know, Please VBA code.

Hi. Just curious, how do you do it manually?

 

[smtazulislam]

this is the primary key, as same primary key have 16 tables 16 primary key.

 

[smtazulislam]

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]

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]

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]

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.

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]

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]

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.

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]

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]

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]

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]

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]

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.

UtterAccess.com

www.utteraccess.com

 

[zeroaccess]

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]

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]

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]

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]

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.