appending data with a replication ID (Access 2000)

Topic

New Reply

I’m trying to append data from one database to a new one where the new table has a replication ID (GUID) as the primary key field. I thought this would be similar to an autonumber, but it isn’t. Is there some trick to creating an acceptable GUID to go along with the record that’s being appended? For example:

INSERT INTO Equipment ( UniqueID, Equipment )
SELECT T_Machine.MachineID, T_Machine.MachineName
FROM T_Machine;

Where the MachineID is an autonumber. I was able to append the information, but the UniqueID now looks like an autonumber instead of a GUID and I don’t think this will be good enough for the new application. Is there a bit of code that anyone has to create the GUID (e.g. {60E7EF97-D735-42MM-A014-7AB11D83AB8C})?

Reply | Quote

Replies

For some reason which I don’t understand, replication GUIDs don’t make good primary keys, and that really tends to make things sticky. You might take a look at Replication Tutorial and in particular the FAQ for Access 2000 replication – there are some issues noted there with GUIDS. Meanwhile I’ll do some searching and see if I can find anything more definitive.

Reply | Quote

I lost my DSL connection for a short period, so couldn’t post the info I found. In the link I cited in my previous post (Microsoft Knowledge Base Article 282977), if you download the referenced FAQ, you will find this under the second question:

2. Can I use a GUID as a Primary Key field?
Yes, you can. However, it’s not required that you do this, and in fact it is not recommended. If you would like to use the Access 2000 Conflict Viewer to resolve synchronization conflicts, you should not use a GUID as a primary key field. If you decide to use a GUID as the primary key for a table, you must do so prior to creating a replica set. To use an AutoNumber field as a primary key field in a replicated database, you must do the following:
1. Choose the AutoNumber data type for the primary key field.
2. Select ReplicationID as the FieldSize property setting.
An additional problem that can occur when you use GUIDs for your primary key fields is that you may have problems trying to use GUIDs in DLOOKUP expressions, query parameters, subform master/child links, and other situations. Usually it is best to avoid this practice entirely.

There is some additional information also available there about other issues with GUIDs.

Reply | Quote

Wendell,

Yes, I know all about GUID being bad; the issue is that it’s not my database and I can’t make any changes. I’m just trying to get data from a nice, indexed-primary-keyed-non-redundant database to a new and improved third party product. and I was hoping there was a vba or vb snippet out there somewhere that created a GUID.

Reply | Quote

The GUID generation process is supposed to be hidden within the Jet engine (or the SQL Server engine if you are using that or MSDE) so an insert operation like you are doing should generate a proper GUID. Are you working with the table as an attached table, or are you able to work directly in the new database? Also, can you view the design of the table, and is the new database actually a replica (i.e. a member of a replicated database)?

Reply | Quote

Wendell and Charlotte,

Thanks for trying to help! Now for the particulars…

Wendell – I am able to go into the new database and the tables are not linked. I’m thinking that the GUID is created if one were to go into the program (which is a front-end executable) and set up, let’s say, a new piece of equipment. Perhaps the programmers thought since everyone would have the front-end, that the backend tables needed to be synchonized and this could be best served with a GUID.

Thankfully, the field itself is a text field, so when I loaded my current data into it, I was able to open the application and see my data. It wasn’t pretty, but it worked.

Charlotte – thanks for looking! I kept running into “how to replicate” instead of how to fake like you’re doing it.

Reply | Quote

Quite frankly, a new and improved third-party app that uses GUIDs as primary keys immediately makes it suspect in my book, but then I’m the skeptical type. Just so it’s clear, I think you are telling us that the back-end isn’t really replicated, and that they created a text field and then populated it with GUIDs in order to create a primary key. Is by chance the front-end a VB application rather than Access?

Update to show link to MSKB article
Thinking about it after my original post, I did a couple of searches and came up with Microsoft Knowledge Base Article 197916 that appears at least on the surface to tell you how to generate a GUID. However it looks like you may need to use ADO along with VBA routines to populate your table.

Reply | Quote

Wendell,

The front end is probably VB. At least it looks like VB; they don’t supply the source code for obvious reasons.

Thanks for the KB reference – I’ll see if this solves situations that we have in our old application that don’t play nice with the new product.

Reply | Quote

Sorry, but I’ve never found a way to create one in code, although it’s been several years since I looked diligently for a solution to that problem. A GUID is a complex identifier that has special characteristics (it is actually a byte array) and identifies not only the record but the machine it was created on. The best I can suggest is that you use a table with a GUID PK in Access, create a record in that table and then pass it to the app in question.

Reply | Quote