uniqueidentifier foreign key autogenerates in tables

[ISSBProgrammer]

I've been working on migrating a database from and mdb to SQL Server and in the process changed most of my autonumbers to uniqueidentifiers. I'm having a problem with the unique identifiers set up as foreign keys. When I start creating a new record in any table with uniqueidentifier foreign keys Access automatically generates a key instead of using null even though nulls are allowed. Naturally this causes a problem with referential integrity. Is there a setting I need to set? I'm trying to avoid having to use code in all of my forms. Thanks.

 

[Crowley16]

in the design of the table, are the foreign keys set up as identities?

you only need to set the primary key of a table to be identity, not foreign key.

--------------------
Procrastinate Now!

 

[ISSBProgrammer]

Crowley16,

I don't understand what an identity is. Did you mean uniqueidentifier? Thanks.

 

[cmmrfrds]

First, does this article explain how/why you are using uniqueidentifier?

http://www.sqlteam.com/item.asp?ItemID=283

Does the table cross servers or is it replicated?
If not, why use?


Do you really need an uniqueidentifier data type, since it will be a little harder to work with in Access than the identity column of which there are ways to retrieve after an insert.
With identity these methods are available.
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
SELECT IDENT_CURRENT('tablename')

 

[ISSBProgrammer]

cmmrfrds,

We need the flexibility that uniqueidentifiers provide. Our network has multiple SQL Servers in different locations and there is the possibility of migrating or merging in the future. The first version of the database was an MDB using autonumbers (identities) but in this migration to ADPs we are converting most of the primary keys to uniqueidentifiers.

Also, the ADPs will most likely be converted into .Net code in the future to creat web interfaces. When we do that we can utilize the .Net libraries to handle the uniqueidentifiers.

 

[whatshandlemen]

i think you should set the "is row guid" property of the foreign key to "NO"; this way and because of referential integrity it will take the value of the primary key. am i right?