Link to home
Start Free TrialLog in
Avatar of bbaldwin
bbaldwinFlag for United States of America

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!!
Avatar of nico5038
nico5038
Flag of Netherlands image

Hi,

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)
Avatar of bbaldwin

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?
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)
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!!
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)
Yep!! You ran right into what I ran into!! I increasing the points to 300!

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)
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.
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)

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
Avatar of Netminder
Netminder

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial