Alter Table to add Autonumber column

[moolie]

have a table.
with data in it.

Using an alter table query to add a new column that is datatype autonumber. But it's not working.

command looks like this:

CurrentDb.Execute "ALTER TABLE [v50040-Batch] alter column [BatchID] Autonumber"

error: there is not field name autonumber is table v50040-batch

any help???

 

[dodge20]

There are two methods to create an AutoNumber field from code. One requires you to run a SQL DDL "Create Table" statement, and the other uses VBA to append dbAutoIncrField flag to a new field's Attributes property.




Function fCreateAutoNumberField( _
ByVal strTableName As String, _
ByVal strFieldName As String) _
As Boolean
'
' Creates an Autonumber field with name=strFieldName
' in table strTableName.
' Accepts
' strTableName: Name of table in which to create the field
' strFieldName: Name of the new field
' Returns True on success, false otherwise
'

On Error GoTo ErrHandler
Dim db As DAO.Database
Dim fld As DAO.Field
Dim tdf As DAO.TableDef

Set db = Application.CurrentDb
Set tdf = db.TableDefs(strTableName)
' First create a field with datatype = Long Integer
Set fld = tdf.CreateField(strFieldName, dbLong)
With fld
' Appending dbAutoIncrField to Attributes
' tells Jet that it's an Autonumber field
.Attributes = .Attributes Or dbAutoIncrField
End With
With tdf.Fields
.Append fld
.Refresh
End With

fCreateAutoNumberField = True

ExitHere:
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Function
ErrHandler:
fCreateAutoNumberField = False
With Err
MsgBox "Error " & .Number & vbCrLf & .Description, _
vbOKOnly Or vbCritical, "CreateAutonumberField"
End With
Resume ExitHere
End Function
' ********* Code End ***********

Dodge20

 

[JonFer]

Moolie

You are using "alter column" instead of "add column" and in Access97 at least they used "AutoIncrement" as the field type. I think the reserved word "column" is optional so you can just use "add BATCHID AutoIncrement"