Migration from Access to MSSQL and Primary Key

[rmatthew]

I have migrated an access database to SQL server 2000 with the intent of using Access as the front-end. The old database did not have any primary key (just some one to one and one to many reationships).

 

Come to find out that the tables are not editable in when viewed through an access project without a primary key.

 

In several of the old tables there are no combination of fields that would allow for a combined key (guaranteed to be non null and also unique).

 

So... I beleive I need to create a new field to hold a primary key. The project is such that the individual site databases may at some time be combined at a central location. For these reasons I would like to know the following:

 

How can I add a uniqueidentifier field after the table is populated with data. And once done - if insert statements are run against the table without specifying an id will SQL generate one anyway?

 

Thanks in advance....

[Nerseus]

If you use uniqueidentifier, you have to insert the identifier yourself (using a function I can't remember the name of right now - getguid or something maybe?). It's not like an identity column which is auto-generated. So if you go that route, create your tables in SQL Server then loop (custom code or a SQL script with a cursor) and create/insert keys.

 

This brings up a question though. Since you can't identify any set of columns to get a unique row, how would you update an already inserted row with a uniqueidentifier? Meaning, how would your code uniquely identify a row to do an update on?

 

If you're manually (through code) copying rows from your Access DB to SQL Server, then you can insert the uniqueidentifier at the time you insert the row into SQL Server. In that case, you could use the WinAPI to generate a GUID and convert it to a string for the insert.

 

If you're manually copying rows, you could also use an identity column and have SQL Server do the updating for you.

 

The following quote has me worried for you:

The project is such that the individual site databases may at some time be combined at a central location

I take it you might be loading an Access database more than once? Or maybe merging multiple access DB's into one SQL Server? Without having any way to identify one row as being different from another, I don't know how you'd do the merge (assuming you need any kind of way to match up existing rows). This might not be an issue but it at least raises some flags in my mind.

 

-nerseus

[rmatthew]

Here is what I did....

I opened the table and made a field named primkey, set it to uniqueidentifier and changed the property in (I think it was isRowGUID) or something like that to true....... Made the field the primary key and when saved it created them all without a hitch :)