bbaldwin
asked on
Create Autonumber/ReplicationID field using DAO
I am trying to create fields in tables that are GUIDs. In Access 2000, the field is an Autonumber with a size of ReplicationID.
I can create Autonumber fields but can't seem to figure out the ReplicationID attribute. Here is my code for the Autonumber:
Dim db As Database, t As TableDef, fAdd As Field
Set db = CurrentDb
For Each t In db.TableDefs
Set fAdd = t.CreateField("GUID", dbLong)
fAdd.Attributes = dbAutoIncrField
t.Fields.Append fAdd
t.Fields.Refresh
Next
I tried lots of different options without success.
Thanks for your help!!
I can create Autonumber fields but can't seem to figure out the ReplicationID attribute. Here is my code for the Autonumber:
Dim db As Database, t As TableDef, fAdd As Field
Set db = CurrentDb
For Each t In db.TableDefs
Set fAdd = t.CreateField("GUID", dbLong)
fAdd.Attributes = dbAutoIncrField
t.Fields.Append fAdd
t.Fields.Refresh
Next
I tried lots of different options without success.
Thanks for your help!!
ASKER
nico5038,
I understand this, however, I am trying to do this with VBA code since I have hundreds of tables. If I have to do this by hand, I will do it but I just wanted to do it with one piece of code.
Any code ideas?
I understand this, however, I am trying to do this with VBA code since I have hundreds of tables. If I have to do this by hand, I will do it but I just wanted to do it with one piece of code.
Any code ideas?
Try:
Function fncSetReplID()
Dim db As Database, t As TableDef, fAdd As Field
Set db = CurrentDb
For Each t In db.TableDefs
t.Fields.Append t.CreateField("GUID", dbGUID)
Next
End Function
Just be carefull as this won't work when there is already an autonumber as Access allows only ONE autonumber per table !
Nic;o)
Function fncSetReplID()
Dim db As Database, t As TableDef, fAdd As Field
Set db = CurrentDb
For Each t In db.TableDefs
t.Fields.Append t.CreateField("GUID", dbGUID)
Next
End Function
Just be carefull as this won't work when there is already an autonumber as Access allows only ONE autonumber per table !
Nic;o)
ASKER
You are close!! We tried this method and the field comes out as a Number/ReplicationID and we are wanting an AutoNumber/ReplicationID.
Any other ideas??
Thanks for your efforts!!
Any other ideas??
Thanks for your efforts!!
Oops, think I'm stuck too.
Tried:
Set fAdd = t.CreateField("GUID", dbLong)
fAdd.Attributes = dbAutoIncrField
fAdd.FieldSize = dbGUID
t.Fields.Append fAdd
t.Fields.Refresh
But fails because Fieldsize is read-only ;-(
And:
Set fAdd = t.CreateField("GUID", dbGUID)
fAdd.Attributes = dbAutoIncrField
t.Fields.Append fAdd
t.Fields.Refresh
Doesn't fail, but doesn't add a field either....
If you try to change a field to autonumber in a table, then you're warned that's only possible for a new field !
Nic;o)
Tried:
Set fAdd = t.CreateField("GUID", dbLong)
fAdd.Attributes = dbAutoIncrField
fAdd.FieldSize = dbGUID
t.Fields.Append fAdd
t.Fields.Refresh
But fails because Fieldsize is read-only ;-(
And:
Set fAdd = t.CreateField("GUID", dbGUID)
fAdd.Attributes = dbAutoIncrField
t.Fields.Append fAdd
t.Fields.Refresh
Doesn't fail, but doesn't add a field either....
If you try to change a field to autonumber in a table, then you're warned that's only possible for a new field !
Nic;o)
ASKER
Yep!! You ran right into what I ran into!! I increasing the points to 300!
bbb
bbb
Well there is a "work-around".
Just create a table with the GUID field manually set to autonum and ReplicationID.
Now use in the code this field to append to all other tables...
Nic;o)
Just create a table with the GUID field manually set to autonum and ReplicationID.
Now use in the code this field to append to all other tables...
Nic;o)
ASKER
nico5038,
I thought I had tried that. Give me the code to use the code to build the field for other tables and the question is yours.
I thought I had tried that. Give me the code to use the code to build the field for other tables and the question is yours.
Basically this should work:
DIM fAdd as field
Set fAdd = <field from "manual" table>
For Each t In db.TableDefs
t.Fields.Append fAdd
Next
Nic;o)
DIM fAdd as field
Set fAdd = <field from "manual" table>
For Each t In db.TableDefs
t.Fields.Append fAdd
Next
Nic;o)
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
- PAQ'd and pts removed
Please leave any comments here within the
next seven days.
Nic;o)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just checkout the Helpfile for the "FieldSize Property".
When you use an autonum, the fieldsize can be set to "Replication ID" or "Long Integer"
Nic;o)