Unique Identifier foreign key

[ISSB Programmer]

I'm trying to convert an mdb to an adp connecting to a SQL Server database.
In the process I'm converting a lot of the primary keys from autonumbers to
uniqueidentifiers. Here's the current issue.

I have two tables, USER and MENTOR. The USER table has a foreign key to
MENTOR called MentorID, which is a uniqueidentifier and has the "Is RowGuid"
option set to no. Not all users have mentors so the foreign key should allow
nulls. I've set up the relationship to enforce referential integrity. When I
try to create a new record directly in the table, Access creates a
uniqueidentifier for the MentorID automatically. If I try to save the record
I get an error because that uniqueidentifier is not in the MENTOR table.

My question is, how do I get Access to "not" create a uniqueidentifier for
MentorID every time I generate a new record? I've tried the same scenario by
accessing the table using SQL Server Enterprise Manager and it uses a "null"
value instead of creating a uniqueidentifer and everything saves ok. Thanks.

 

[KARL DEWEY]

Create a Mentor that has ID of zero and name of None. Set default of USER
table MentorID to zero.

 

[ISSB Programmer]

The data type is uniqueidentifier.

 

[KARL DEWEY]

Never heard of any such datatype - but there is a lot I do not know.

Why not backup the database and change the datatype?

 

[ISSB Programmer]

Hi Karl,

For our purposes the uniqueidentifier data type is more robust than
autonumber so we have standardized our database designs on using it as a
primary key for most of our tables. Thanks.