js said:
Hi,
I used "select col1, 0=col2 into tb1 from tb2" to create a table.
Can I control col2 like a autonumber field?
Later on, If I want to update tb1's col2 begin with a number, how to do
that:
col1 col2
xxx 5
xxx 6
xxx 6 <--assume you meant "7"
Hi js,
You don't say what version of Access.
From MSDN Help File:
*******************************
AutoNumber enhancements. The Jet 4.0 ANSI SQL-92 extensions add support for
customizing the seed and increment values of AutoNumber columns. The syntax is as
follows:
column AUTOINCREMENT (seed, increment)
You can also use the synonyms IDENTITY or COUNTER instead of AUTOINCREMENT.
Jet 4.0 SQL provides the only way to alter the seed and increment values of
AutoNumber fields. The Access UI and ADOX provide no mechanism for
customizing AutoNumber seed or increment values
**********************
So...in code you could do something like:
(assuming Access 200x)
Dim strSQL As String
'start tb1 with only col1 from tb2
strSQL = "Select col1 into tb1 from tb2"
CurrentProject().Connection.Execute strSQL, dbFailOnError
'add autonumber field col2 with seed of 5, increment by 1
strSQL = "ALTER TABLE tb1 ADD COLUMN " _
& "col2 AUTOINCREMENT (5, 1)"
CurrentProject().Connection.Execute strSQL, dbFailOnError
Please respond back if I have misunderstood
or was not clear.
Good luck,
Gary Walter
