RESET AUTONUMBER

[Johnny42]

COULD THIS BE USED BY vba TO RESET AUTONUMBER ?

USE MyDatabase
GO
DBCC CHECKIDENT (MyTable, RESEED, 1)
GO

 

[RoyVidar]

I don't think so - have you tried?

One way that would normally work, is

currentdb.execute "ALTER TABLE TableName ALTER COLUMN AutoNumberFieldName COUNTER(N,1)"

Replace TableName and AutoNumberFieldName with the names you use, and N here, is representing starting number, you'll probably need 1?

I don't think it'll work if your autonumber field is used in relationships with other tables, you'd probably need to remove those first.

And please, get a new keyboard where the Caps Lock key isn't stuck;-)

Roy-Vidar

 

[flyover789]

maybe this also helps:

http://www.tek-tips.com/viewthread.cfm?qid=1050735&page=3

fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[Johnny42]

Sorry for the caps....Will this work on an MSDE?

 

[RoyVidar]

Don't think so, I think perhaps your code is closer, perhaps single quotes around the table name?

[tt]DBCC CHECKIDENT ('MyTable', RESEED, 1)[/tt]

Autonumbers are an Access thinige;-)

Roy-Vidar

 

[Johnny42]

Thanks..
DBCC CHECKIDENT ('MyTable', RESEED, 1)
works fine

 

[redapples]

Public Function TabReseed(cnn As Connection, tName As String, fName As String, iSeed As Integer, iInterval As Integer) As Boolean
    Dim str As String
    str = "ALTER TABLE [" & tName & "]" & _
          " ALTER COLUMN [" & fName & "]" & _
          " COUNTER(" & iSeed & "," & iInterval & ")"
    cnn.Execute str
End Function

Want the best answers? See FAQ181-2886

http://www.redapples.v21hosting.co.uk