I have an existing ms access table.
I want to use VBA to append a new field (column) with the DataType as AutoNumber and the Field Size as a Replication ID.
Kindly assist with the appropriate VBA Code.
I have an existing ms access table.
I want to use VBA to append a new field (column) with the DataType as AutoNumber and the Field Size as a Replication ID.
Kindly assist with the appropriate VBA Code.
no need to code this since adding fields is not a regular thing.
just design table and add it manually.
I can actually add the field manually by table design. But how do i assign replication IDs to the existing records in the table after I manually add the field?
This might help. https://social.msdn.microsoft.com/Fo...ccessdatabases
Any reason you can't simply use an autonumber as a unique?
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
Here is some code from Allen Browne:
http://allenbrowne.com/func-DAO.htmlCode:Function MakeGuidTable() 'Purpose: How to create a table with a GUID field. Dim db As DAO.Database Dim tdf As DAO.TableDef Dim fld As DAO.Field Dim prp As DAO.Property Set db = CurrentDb() Set tdf = db.CreateTableDef("Table8") With tdf Set fld = .CreateField("ID", dbGUID) fld.Attributes = dbFixedField fld.DefaultValue = "GenGUID()" .Fields.Append fld End With db.TableDefs.Append tdf End Function
Note the default value "GenGUID()". To populate the existing records you would simply run two action queries: first and append query selecting all records from your table and inserting them into itself:
Now you simply run a second delete query to remove the records with null GUIDs:Code:INSERT INTO tblYourTable SELECT tblYourTable.* FROM tblYourTable;
Cheers,Code:DELETE * FROM tblYourTable WHERE fldGUID Is Null;
In the first Append query you will need to enumerate all the fields other than the new GUID autonumber so you cannot use the * wildcard.
Sorry for the confusion!
Cheers,
Curious as to why you are using a replicationID. Although they provide a 'higher level of uniqueness' over an autonumber, they generally don't make good fields for relationships, in part because of their size (16 bytes v 4 bytes). They were intended to enable syncing of master and slave access db's when networks didn't exist or were poor performers. Admittedly they are used in enterprise scale rdbms but again for much the same reason.