Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Create Autonumber/ReplicationID field using DAO

Posted on 2002-06-19
11
Medium Priority
?
840 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

886 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