By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,550 Members | 2,757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,550 IT Pros & Developers. It's quick & easy.

Change the autonumber of a table

P: n/a

I want to change the autonumber of the field in table to 1000
and to preserve the old number from 1 to 22.Is there any code in Visla
basic to do
that?. This concerns my tables Houses where houseid up to now is 22.I
want the next
autonumber to be 1000.
Can somebody help me ?

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
samotek wrote:
I want to change the autonumber of the field in table to 1000
and to preserve the old number from 1 to 22.Is there any code in Visla
basic to do
that?. This concerns my tables Houses where houseid up to now is 22.I
want the next
autonumber to be 1000.
Can somebody help me ?

Add however many records needed to make the autonumber = 999. Change
Table1 and ID to table and field names in your app. You need to add 1
record (which will be 1000) after this routine before you compact the
database otherwise you revert back to 22.

Dim rst As DAO.Recordset
Dim intcnt As Integer
Dim dbs As Database
Dim strSQL As String

dbs = Currentdb
Set rst = dbs.OpenRecordset("Table1", dbOpenDynaset)
Do While True
rst.AddNew
intcnt = rst!ID
rst.Update
If intcnt = 999 Then Exit Do
Loop
rst.Close
Set rst = Nothing

'delete all records with ID greater 22
strSQL = "SELECT Table1.* FROM Table1 WHERE Table1.ID > 22;"
dbs.Execute strSQL
set dbs = Nothing
Nov 13 '05 #2

P: n/a
samotek wrote:
I want to change the autonumber of the field in table to 1000
and to preserve the old number from 1 to 22.Is there any code in Visla
basic to do
that?. This concerns my tables Houses where houseid up to now is 22.I
want the next
autonumber to be 1000.
Can somebody help me ?

Add however many records needed to make the autonumber = 999. Change
Table1 and ID to table and field names in your app. You need to add 1
record (which will be 1000) after this routine before you compact the
database otherwise you revert back to 22.

Dim rst As DAO.Recordset
Dim intcnt As Integer
Dim dbs As Database
Dim strSQL As String

dbs = Currentdb
Set rst = dbs.OpenRecordset("Table1", dbOpenDynaset)
Do While True
rst.AddNew
intcnt = rst!ID
rst.Update
If intcnt = 999 Then Exit Do
Loop
rst.Close
Set rst = Nothing

'delete all records with ID greater 22
strSQL = "SELECT Table1.* FROM Table1 WHERE Table1.ID > 22;"
dbs.Execute strSQL
set dbs = Nothing
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.