Table Relationship Integrity - Null Values

[Guest]

We have been building databases for replication and therefore using primary
keys of type uniqueidentifier. The GUI is developed in Microsoft Access 2003,
which doesn’t seem to allow us to insert NULL values into the foreign keys
(of type uniqueidentifier) that reference these primary keys. This means that
our referential integrity is compromised as we are forced to enter a valid
value into the foreign key, or else remove the relationship from the SQL
table.

How can we assign NULL values to uniqueidentifier fields in Access 2003
projects? We saw one help that suggested an explicit assignment in the
BEFOREUPDATE event, but this doesn’t appear to be working for us.

Thanks.

 

[privatenews]

Hello Alan,


I was not able to reproduce the issue on Access 2003 SP2/WinXP

1. Create tablea/tableb


create table tablea (col1 uniqueidentifier primary key, colb
uniqueidentifier null, a int, b varchar(20))

create table tableb (colb uniqueidentifier primary key default newid())

alter table tablea add constraint fk1 foreign key (colb) references tableb
(colb)

select * from tablea

insert tablea values (newid(), null, 2,'ddd')

insert tablea values (newid(), newid(), 1, 'ddd') --I received error about
foreign key

sp_help tablea

select * from tablea


2. created a ADP from the existing table, I was able to input new item by
removing the guid created auotmatically in colb when editing tablea.

Best Regards,

Peter Yang
MCSE2000, MCSA, MCDBA
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| Thread-Topic: Table Relationship Integrity - Null Values
| thread-index: AcaF7SXQqRB3+gh7T5G9+Pes3CihSg==
| X-WBNR-Posting-Host: 210.50.131.97
| From: =?Utf-8?B?c3RlZWw=?= <[email protected]>
| Subject: Table Relationship Integrity - Null Values
| Date: Thu, 1 Jun 2006 19:35:01 -0700
| Lines: 17
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| Newsgroups: microsoft.public.access.adp.sqlserver
| Path: TK2MSFTNGXA01.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.access.adp.sqlserver:24723
| NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
| X-Tomcat-NG: microsoft.public.access.adp.sqlserver
|
| We have been building databases for replication and therefore using
primary
| keys of type uniqueidentifier. The GUI is developed in Microsoft Access
2003,
| which doesn’t seem to allow us to insert NULL values into the foreign
keys
| (of type uniqueidentifier) that reference these primary keys. This means
that
| our referential integrity is compromised as we are forced to enter a
valid
| value into the foreign key, or else remove the relationship from the SQL
| table.
|
| How can we assign NULL values to uniqueidentifier fields in Access 2003
| projects? We saw one help that suggested an explicit assignment in the
| BEFOREUPDATE event, but this doesn’t appear to be working for us.
|
| Thanks.
| --
| Regards,
|
| Alan
|

 

[Guest]

HI Peter,

The scnerio was like this:
Table A: tblPurchase_order
name1: POID - uniqiueidentifier - PK
name2: PODescription - nvarchar(50)
name3: CostCodeID - uniqueidentifier - FK

In the Form I would have field CostCOdeID set up as a Combo Box referencing
the CostCode table and CostCodeID - uniqueidentifier - PK

When I add a new Purchase Order as follows:
POID - newGuid()
PO Description : "blah blah"
- the error occurs if I try to exit the record without selecting a CostCodeID.

Since posting this issue I was able to resolve by placing some code in the
Current event for the Form. The Form's Control Source is set to
tblPurchaseOrders. I need to keep the GUID's for replication.

------------------------------------------
Private Sub Form_Current()
If IsNull(Me.CostCodeID) Then
Me.CostCodeID = Null
End If
End Sub

 

[privatenews]

Hello Alan,

Thank you for taking time to write in about the status and how you work
around this issue. Your exprience will surely benefit the community!

Best Regards,

Peter Yang
MCSE2000, MCSA, MCDBA
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| Thread-Topic: Table Relationship Integrity - Null Values
| thread-index: AcaGlymnN55PJksESwui0icJJCZVKg==
| X-WBNR-Posting-Host: 202.7.166.164
| From: =?Utf-8?B?c3RlZWw=?= <[email protected]>
| References: <[email protected]>
<[email protected]>
| Subject: RE: Table Relationship Integrity - Null Values
| Date: Fri, 2 Jun 2006 15:52:02 -0700
| Lines: 133
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| Newsgroups: microsoft.public.access.adp.sqlserver
| Path: TK2MSFTNGXA01.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.access.adp.sqlserver:24733
| NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
| X-Tomcat-NG: microsoft.public.access.adp.sqlserver
|
| HI Peter,
|
| The scnerio was like this:
| Table A: tblPurchase_order
| name1: POID - uniqiueidentifier - PK
| name2: PODescription - nvarchar(50)
| name3: CostCodeID - uniqueidentifier - FK
|
| In the Form I would have field CostCOdeID set up as a Combo Box
referencing
| the CostCode table and CostCodeID - uniqueidentifier - PK
|
| When I add a new Purchase Order as follows:
| POID - newGuid()
| PO Description : "blah blah"
| - the error occurs if I try to exit the record without selecting a
CostCodeID.
|
| Since posting this issue I was able to resolve by placing some code in
the
| Current event for the Form. The Form's Control Source is set to
| tblPurchaseOrders. I need to keep the GUID's for replication.
|
| ------------------------------------------
| Private Sub Form_Current()
| If IsNull(Me.CostCodeID) Then
| Me.CostCodeID = Null
| End If
| End Sub
| ------------------------------------------
|
| Thanks for looking at this issue for me.
| --
| Regards,
|
| Alan
|
|
| ""privatenews"" wrote:
|
| > Hello Alan,
| >
| >
| > I was not able to reproduce the issue on Access 2003 SP2/WinXP
| >
| > 1. Create tablea/tableb
| >
| >
| > create table tablea (col1 uniqueidentifier primary key, colb
| > uniqueidentifier null, a int, b varchar(20))
| >
| > create table tableb (colb uniqueidentifier primary key default newid())
| >
| > alter table tablea add constraint fk1 foreign key (colb) references
tableb
| > (colb)
| >
| > select * from tablea
| >
| > insert tablea values (newid(), null, 2,'ddd')
| >
| > insert tablea values (newid(), newid(), 1, 'ddd') --I received error
about
| > foreign key
| >
| > sp_help tablea
| >
| > select * from tablea
| >
| >
| > 2. created a ADP from the existing table, I was able to input new item
by
| > removing the guid created auotmatically in colb when editing tablea.
| >
| > Best Regards,
| >
| > Peter Yang
| > MCSE2000, MCSA, MCDBA
| > Microsoft Partner Online Support
| >
| > Get Secure! - www.microsoft.com/security
| >
| > =====================================================
| > When responding to posts, please "Reply to Group" via
| > your newsreader so that others may learn and benefit
| > from your issue.
| > =====================================================
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| > --------------------
| > | Thread-Topic: Table Relationship Integrity - Null Values
| > | thread-index: AcaF7SXQqRB3+gh7T5G9+Pes3CihSg==
| > | X-WBNR-Posting-Host: 210.50.131.97
| > | From: =?Utf-8?B?c3RlZWw=?= <[email protected]>
| > | Subject: Table Relationship Integrity - Null Values
| > | Date: Thu, 1 Jun 2006 19:35:01 -0700
| > | Lines: 17
| > | Message-ID: <[email protected]>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 8bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| > | Newsgroups: microsoft.public.access.adp.sqlserver
| > | Path: TK2MSFTNGXA01.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.access.adp.sqlserver:24723
| > | NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
| > | X-Tomcat-NG: microsoft.public.access.adp.sqlserver
| > |
| > | We have been building databases for replication and therefore using
| > primary
| > | keys of type uniqueidentifier. The GUI is developed in Microsoft
Access
| > 2003,
| > | which doesn’t seem to allow us to insert NULL values into the
foreign
| > keys
| > | (of type uniqueidentifier) that reference these primary keys. This
means
| > that
| > | our referential integrity is compromised as we are forced to enter a
| > valid
| > | value into the foreign key, or else remove the relationship from the
SQL
| > | table.
| > |
| > | How can we assign NULL values to uniqueidentifier fields in Access
2003
| > | projects? We saw one help that suggested an explicit assignment in
the
| > | BEFOREUPDATE event, but this doesn’t appear to be working for
us.
| > |
| > | Thanks.
| > | --
| > | Regards,
| > |
| > | Alan
| > |
| >
| >
|

 

[Guest]

Hi Peter,

I was able to work out what was going on. When a new record is started
(where the combo boxes using a Unique Identifier) as their rowsource are
automatically given an ID. This ID has nothing to do with any records the
combo box is supposed to be displaying.

So when I try and exit the record (without selecting a value from the combo
box) the Combo Box ID is referenced against its own lookup table. Here's lies
the issue. The ID that is was given isn't in the lookup table as it was
temporary. If I had selected a value from the combo box the references would
have been OK. Because they weren't I received the relationship error I was
trying to fix.

To work around the problem I assign the Combo Box a Null value during a
routine that is called during a AfterUpdate event from a field on the form.
This forces a Null on the Combo Box and solves the relationship problem.

Of course, before applying Null you need to check whether the Combo Box GUID
is actually a good value, not a false one, ie. the Combo Box value may have
already been selected. You wouldn't want to write a Null value when the value
of the Combo Box was previously selected. I used a Recordset to check the
value of the GUID of the Combo Box and if it matched a lookup table GUID then
a NULL does not get assigned, but if there isn't a match (ie a false GUID)
then I assign a NULL.

....clear as mud...

Alan

 

[privatenews]

Hello Alan,

Thank you for sharing your experience! This shall really help others who
may encounter similar issues.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.