Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create Autonumber/ReplicationID field using DAO

Posted on 2002-06-19
11
Medium Priority
?
837 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

670 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