Solved

Create Autonumber/ReplicationID field using DAO

Posted on 2002-06-19
11
832 Views
Last Modified: 2010-08-05
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!!
0
Comment
Question by:bbaldwin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 7094344
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)
0
 
LVL 2

Author Comment

by:bbaldwin
ID: 7094372
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?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7095441
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)
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 2

Author Comment

by:bbaldwin
ID: 7096035
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!!
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7096801
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)
0
 
LVL 2

Author Comment

by:bbaldwin
ID: 7097082
Yep!! You ran right into what I ran into!! I increasing the points to 300!

bbb
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7100297
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)
0
 
LVL 2

Author Comment

by:bbaldwin
ID: 7104893
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.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7105480
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)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7253762

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)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7269925
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question