locked
How do I add a column of type GUID Replicate ID to a Access table using the Alter Table Add Column Statement RRS feed

  • Question

  • User-581139276 posted

    Hi,

     

    I'm trying to create a GUID autogenerated column within my table using the Alter Table Add column Auery as shown below,

     

    ALTER TABLE MainTable ADD COLUMN [My_Guid] AUTOINCREMENT(Replication ID)

    Any help would be greatly appreciated..  Thanks,

    Monday, December 8, 2008 11:34 AM

Answers

  • User-821857111 posted

    ADOX is your best (only, I suspect) option when adding and populating Replication ID fields after the fact.  You can't do it using DDL, and the temp table approach that Hans suggested is not an option for you.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 9, 2008 2:49 AM

All replies

  • User-1199946673 posted

    Acces doesn't have a GUID data type. If you want to have GUID as Key field, you should use a Text Field, and you should fill it using code.

    If you want to have a autogenerated Primary Key, use [COUNTER], which will generate a Long value.

    Monday, December 8, 2008 1:53 PM
  • User-581139276 posted

    Hi,

     

    Access has a AutoNumber Replcate ID type which is exactly what I want,  the problem is that I am unable to create this type using the "Alter Table Add Column.."  Do you know of a way I could accomplush this?

     

    Thanks,

    Monday, December 8, 2008 2:47 PM
  • User-821857111 posted

    ALTER TABLE MainTable ADD COLUMN [My_Guid] GUID

     

    Monday, December 8, 2008 3:42 PM
  • User-1199946673 posted

    Like I said, use the fieldtype [COUNTER]

    ALTER TABLE MainTable ADD COLUMN [MyGuid] COUNTER NOT NULL

    Monday, December 8, 2008 3:47 PM
  • User-581139276 posted

    Hello,

     

    What I want to create is a Autonumber Guid Field,  I can do this using Access design view creating a (AutoNumber->Replicate ID) field but am unable to figure our how to do it using DDL,  I read somewhere that you cannot create a autonumber GUID field using DDL and you need to use DAO.,  I'm able to create a GUID field and the set the default to GenGUID(), this seems to work for new records being added,  but for some reason the default is not generating a GUID for the records already in the database..

     

    Any help would be greatly appreciated.,  Currently my DDL looks like this, what I'l trying to do is programmatically create an AutoNumber field with the Replication ID field size.

    "ALTER TABLE MyTable ADD COLUMN [MYguid] GUID NOT NULL DEFAULT GenGUID()"

     

     

    Monday, December 8, 2008 6:33 PM
  • User-1199946673 posted

    Woh,

    I really didn't know that Access also supported GUID, but it certainly does...

    And I also noticed that when you add this field when records are already present, they remain blank. How about Copy all fields to a temp table, delete all records in MyTable. add the new column, and copy the records frim the temp table back to MyTable, because that will auto generate a Guid...

    By the way, a great tool to work with Access Online is StP Database Administrator It's in classic ASP, but it really does provide a really good interface for an Access Database

     

    Monday, December 8, 2008 7:21 PM
  • User-581139276 posted

    Hello,

    Thank you for the information,  copying the data to and from a temporary table is not an option for me.  Do you know if my task can be accomplished using a DDL?  I have found an example using ADOX, but I really don't want to use this approach unless I have to.

    You can view the ADOX example of what I'm trying to accomplish below:

    http://support.microsoft.com/kb/297980

    Thank you in advance.

    Monday, December 8, 2008 8:38 PM
  • User-821857111 posted

    ADOX is your best (only, I suspect) option when adding and populating Replication ID fields after the fact.  You can't do it using DDL, and the temp table approach that Hans suggested is not an option for you.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 9, 2008 2:49 AM
  • User-581139276 posted

    Hello,

    Thank you for all your help, AdoX did the trick, I have posted my code below just incase it comes in handy for someone else in the future:

    ADODB.Connection cn = new ADODB.Connection();

    ADOX.Column clx = new ADOX.Column();

    ADOX.Catalog cat = new ADOX.Catalog();

    ADOX.Table tblnam;

    cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;cn.Open(sExternalDBConnectionString, "", "", 0);

    cat.ActiveConnection = cn;

    clx.ParentCatalog = cat;

    clx.Type = ADOX.
    DataTypeEnum.adGUID;

    clx.Name = "IDField";

    clx.Properties["AutoIncrement"].Value = false;

    clx.Properties["Fixed Length"].Value = true;

    clx.Properties["Jet OLEDB:AutoGenerate"].Value = true;clx.Properties["Jet OLEDB:Allow Zero Length"].Value = true;

    tblnam = cat.Tables[sExternalDBTableName];

    tblnam.Columns.Append(clx, ADOX.DataTypeEnum.adGUID, 16);

    cn.Close();

    Tuesday, December 9, 2008 8:33 AM
  • User-1199946673 posted

    Good...

    On second thought, you didn't need a temp table. You could also just added the new field. do an insert on all records without a GUID (in case in the meantime a record was already added), so all records are duplicated with a GUID, and then remove all records without a GUID

    But you solved it already with ADOX...

    Tuesday, December 9, 2008 11:26 AM