Recommendations for using SQL Server GUID from MS Access

Question

I am increasing access to my existing MS Access database to SQL Server 2008, and since we want to use SQL Merge Replication , you will have to change all current primary keys (currently standard auto-increment integers) to GUIDs.

So here are the questions:

• Any recommendation on changing primary keys from integer to GUID?
• Any recommendations for using and manipulating the GUID from code inside Access clients?
• What type of SQL Server GUID should I use?

Answer 1

Chris is right in saying that (1) you do not need GUIDS for merge replication and (2) there is only one type of GUID, but you should know that:

• GUIDS can be created according to various rules. You can check this one here
• When you configure replication, SQL will systematically add a GUID (generated as newsequentialid) to each table, if it does not already exist, and call it rowguid . Of course, if you already have such a GUID / newSequentialId in each table, SQL will use it. BUt I do not recommend that you "mix" replication GUIDs with GUIDs: you can declare all your primary GUID keys as "newSequentialIds", but (a) you will lose the ability to generate GUID values ​​on the client side - see below - and (b) your PCs will be “predictable,” and this idea makes me feel uncomfortable ...
• storing integer auto-increment numbers and managing their range through replication means a lot of overhead (you need to allocate ranges for each table / every publication) and a potential source of conflict when replicating from different sources.
• In addition, some SQL errors, such as this one , which is specific for range allocation, are still not resolved correctly: applying cumulative package 5 did not solve our problem, and we had to find another way to restart our replication processes.
• In any case, I am deeply convinced that the transition from integers to GUIDs as primary keys is mandatory. There are many reasons for this, and one of them is related to this range control as a potential source for head sessions and night checks.

Regarding changing integers in GUIDS, I advise you to write a step-by-step module that will:

• Before making changes to the backup of all existing tables
• Add GUID field to each table
• Add the appropriate FK fields where requested
• Update FK fields through views created using existing relationships (built on integer fields)
• The end of the relationship
• Change PK from integer fields to GUID fields
• Restore relationship

Take the time to write this code. You will use it many times before working properly. You should profit from the DAO object, tabledefs, indexes, etc. Keep in mind that you should always be able to return to the starting point, so do not forget about the initial backup process.

What about manipulating GUIDs from VBA? You can find out about this several times:

• GUIDs are of type Variant
• You can easily create your own GUID as the main key on the client side of the application, as I suggested here .
• When you try to get a GUID value from a control in a form (usually as a related field in a combo box) you will get "?????", but not the value. To get the correct data, you must refer to the field value in the recordset. You can open such a form in your application, go to the "nearest" window and try the following:

---

 ? myForm.myControl ????? ? myForm.recordset.fields("myFieldName") {000581EB-9CBF-418C-A2D9-5A7141A686CC} 

• You may need to convert your lines to lines when navigating recordsets with expressions such as recordset.findfirst:

---

 myFirstRecordset.FindFirst "stringFromGUID(myGuidId) = " & StringFromGUID(mySecondRecordset.Fields("myGuidId").Value) 

Answer 2

This may be a little off topic. However, you should not use a GUID to combine replication. You can still use auto-increment integers and distribute different ranges for different database instances. Thus, rows with identical identifiers will not be generated.

In addition, in SQL 2008 there is only one GUID type field - uniqueidentifier