Solved

Create Autonumber/ReplicationID field using DAO

Posted on 2002-06-19
11
829 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
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

679 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