Results 1 to 7 of 7
  1. #1
    IIBRAHIM is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    7

    Adding a New Column with field size as Replication ID in an existing MS Access table using VBA


    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.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    no need to code this since adding fields is not a regular thing.
    just design table and add it manually.

  3. #3
    IIBRAHIM is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    7
    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?

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    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 ↓↓

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here is some code from Allen Browne:
    Code:
    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
    http://allenbrowne.com/func-DAO.html

    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:
    Code:
    INSERT INTO tblYourTable SELECT tblYourTable.* FROM tblYourTable;
    Now you simply run a second delete query to remove the records with null GUIDs:

    Code:
    DELETE * FROM tblYourTable WHERE fldGUID Is Null;
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    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,

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 10-25-2020, 12:59 AM
  2. Replies: 4
    Last Post: 02-15-2017, 08:21 AM
  3. Replies: 0
    Last Post: 08-27-2016, 03:45 AM
  4. Replies: 2
    Last Post: 06-08-2014, 10:23 AM
  5. I am stumped...adding field to existing table
    By tanyalee123 in forum Queries
    Replies: 6
    Last Post: 12-09-2013, 06:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums