Alter table statement

[Guest]

what is the SQL Statement to create an Autonumber with Replication ID???

I tried "Alter Table TableName Add Column ColumnName GUID" but the datatype
is integer not AutoNumber....

Please help and thanks

Ed

 

[John Spencer (MVP)]

Sorry to say I was unable to find a way to do this with an SQL statment and had
to resort to using DAO and code to do this. I am assuming that you are trying
to do this in an .mdb (jet) database and not in an Access PROJECT or using MS SQL.

Post back if you want to do this through code. I'll have to dig out a sample to
show how I did it.

 

[John Spencer (MVP)]

Ah, I was able to find it quicker than I thought. It took me a 4 days off and
on to find the solution and then I found it by googling dbSystemField in Google
Groups and found a solution posted by Steve Arbaugh in 1998.

Here is a copy of the code I used to create an autonumber (Replication ID)
field in a newly created table.


Sub BuildGUIDAutonumber()
'Using Access97
'Test Procedure
Dim dbany As DAO.Database
Dim tdefAny As DAO.TableDef
Dim fldAny As DAO.Field
Set dbany = CurrentDb()
On Error Resume Next
dbany.TableDefs.Delete "A__A"
On Error GoTo 0
dbany.TableDefs.Refresh
Set tdefAny = dbany.CreateTableDef("A__A")
With tdefAny

Set fldAny = .CreateField("GUIDFld", dbGUID)
fldAny.Attributes = fldAny.Attributes Or dbSystemField
fldAny.Properties("DefaultValue") = "GenGUID()"
.Fields.Append fldAny

End With

dbany.TableDefs.Append tdefAny
dbany.TableDefs.Refresh

End Sub

To add to an existing table that does not already have an autonumber field
Sub TestAfter()
Dim dbany As DAO.Database
Dim tdefAny As DAO.TableDef
Dim fldAny As DAO.Field

Set dbany = CurrentDb()

Set tdefAny = dbany.TableDefs("A__A")
With tdefAny

Set fldAny = .CreateField("GUIDFld", dbGUID)
fldAny.Attributes = fldAny.Attributes Or dbSystemField
fldAny.Properties("DefaultValue") = "GenGUID()"
.Fields.Append fldAny
End With

End Sub