Access to SQL Server help...

Hi everyone,

I'm new to SQL Server, and i've got a mini project to convert an MS Access backend of a webapp to SQL Server 2000. I've been able to correct all the names//datatype errors, and upload data ok. My problem, however, is with relationships between tables.

Here's my problem:

If given two tables, TABLE A (1:many) TABLE B
the FK in TABLE B (from A) does not have to be entered, that is, it can be in table A or null.

Right now when I load my webpage, I can only insert a record into TABLE B if I fill that FK with data, but as a business rule this does not have to be entered.

I've been playing around with triggers to help fix this, but i'm a newbie to triggers...can anyone give me any pointers on how to write a triggers to handle this??

Thanks,

Bill

triggers not necessary

just make sure the FK in table B is declared NULL

then you will not have to enter a FK value if you don't want to

Yes, but what's getting me is that I have the field so it allows NULLS. But I still get this error:

Create Failed
An unexpected error has occurred.
Please contact your system administrator with the following information.
Error Number: -2147217900
Error Description: The record can't be added or changed. Referential integrity rules require a related record in table 'tblAttendants'.
Error Source: Microsoft OLE DB Provider for SQL Server

could you script the CREATE TABLE statements for me?

Well I used the upsizing wizard to generate the tables, then ran the data transfer from the enterprise manager. But I can write up some scripts for sure. I have double-checked and the fields are set to allow nulls.

enterprise manager can generate the scripts for you

here is the script generated for the table in question...The items generating the error are Attendant1ID, Attendant2ID, Attendant3ID as they link to a parent table, but are not "required" fields.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblMiscItemEMSCards_FK01]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblMiscItemEMSCards] DROP CONSTRAINT tblMiscItemEMSCards_FK01
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPCRs_FK10]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblPCRs] DROP CONSTRAINT tblPCRs_FK10
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblEMSCards]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblEMSCards]
GO

CREATE TABLE [dbo].[tblEMSCards] (
[CardID] [int] IDENTITY (1, 1) NOT NULL ,
[CardType] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DispatcherID] [int] NULL ,
[CallOriginID] [int] NULL ,
[FirstRespGroupID] [int] NULL ,
[Attendant1ID] [int] NULL ,
[Attendant2ID] [int] NULL ,
[Attendant3ID] [int] NULL ,
[AreaID] [int] NULL ,
[PULocationID] [int] NULL ,
[DestinationID] [int] NULL ,
[ResponseToID] [int] NULL ,
[ResponseFromID] [int] NULL ,
[Unit1ID] [int] NULL ,
[Unit2ID] [int] NULL ,
[ScheduledCall] [bit] NOT NULL ,
[CardNum] [int] NULL ,
[Time] [datetime] NULL ,
[TimeOfCall] [datetime] NULL ,
[PreAlert] [datetime] NULL ,
[OnAir] [datetime] NULL ,
[TimeOfResponse] [datetime] NULL ,
[107Scene] [datetime] NULL ,
[PatientSide] [datetime] NULL ,
[DepartScene] [datetime] NULL ,
[107Destination] [datetime] NULL ,
[108Clear] [datetime] NULL ,
[BackAtBase] [datetime] NULL ,
[Date] [datetime] NULL ,
[UrbanRural] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Specials] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sex] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SHSP] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PULocation] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Destination] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ChiefComplaint] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LandLocation] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CallerName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CallerNumber] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherCallOrigin] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RelatedCardNumbers] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreateUser] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreateDate] [datetime] NULL ,
[LastUpdateUser] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdateDate] [datetime] NULL
) ON [PRIMARY]
GO

Hi guy,
I need to be helped...I'm trying to get the data from Access to put in SQL Server but I have problem everytime.
Anybody know how can I do to resolve this problem.
I'm waiting your information!!!!
Thanks!!

Simo