Last post Dec 09, 2008 11:26 AM by hans_v
Dec 08, 2008 11:34 AM|robdob|LINK
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,
Dec 08, 2008 01:53 PM|hans_v|LINK
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.
Dec 08, 2008 02:47 PM|robdob|LINK
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?
Dec 08, 2008 03:42 PM|Mikesdotnetting|LINK
ALTER TABLE MainTable ADD COLUMN [My_Guid] GUID
Dec 08, 2008 03:47 PM|hans_v|LINK
Like I said, use the fieldtype [COUNTER]
ALTER TABLE MainTable ADD COLUMN [MyGuid] COUNTER NOT NULL
Dec 08, 2008 06:33 PM|robdob|LINK
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.
Dec 08, 2008 07:21 PM|hans_v|LINK
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
Dec 08, 2008 08:38 PM|robdob|LINK
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
You can view the ADOX example of what I'm trying to accomplish below:
Thank you in advance.
Dec 09, 2008 02:49 AM|Mikesdotnetting|LINK
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.
Dec 09, 2008 08:33 AM|robdob|LINK
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:
cat.ActiveConnection = cn;
clx.ParentCatalog = cat;
tblnam = cat.Tables[sExternalDBTableName];
Dec 09, 2008 11:26 AM|hans_v|LINK
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...