asked on

Access 2010 linked table to SQL Server 2008 - required field setting yes for linked table but no for linked view

I have an Access 2010 front end linked to SQL Server 2008. There is an error when pasting null records into a form. The form uses a linked view and the Required settings when I look at design view on the Access "table" says required Yes. So linked to the same table that I have the view for, and it does show the Required settings the way it should be, but not in the linked view. I want to use a linked view and not a linked table. Is there a way I can get the linked view to show the required setting as Required No?

The primary key of the table in SQL Server is ID and I select ID when I created the linked object in Access 2010.

Here is a screenshot of the linked view in design mode in Access.

Here is a screenshot of the linked table in design mode in Access.

Thanks for your help.

Regards,
Alexis

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alexisbr

ASKER

Thanks. Here's the create table statement from SQL Server 2008:
CREATE TABLE [dbo].[SelectedTests](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [SelectedTest] [varchar](20) NULL,
      [Username] [varchar](30) NULL,
      [Legend] [varchar](100) NULL,
      [Sequence] [int] NULL,
      [DateLastUpdated] [datetime] NULL,
      [DateAdded] [datetime] NULL,
CONSTRAINT [PK_SelectedTests] PRIMARY KEY CLUSTERED
(
      [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Gustav Brock

And the view? Perhaps it states NOT NULL somehow? Not that I know how it should be done.

/gustav

ASKER CERTIFIED SOLUTION

Hamed Nasr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

Please post the error message you are getting and the script for the VIEW. What column causes the error? And are you sure the VIEW is updateable?

alexisbr

ASKER

hsnar,
After doing the test you gave me, I realized what the problem was. I had initially forgot to set the SelectedTests column to NULL in SQL Server. I fixed that and refreshed the view but didn't recreate it. When I did the test you suggested, I realized that I probably should have recreated the view, because maybe the old view was still seeing the NOT NULL setting from the original table. When I recreated the view rather than just refreshing it, it worked.

acperkins, your questions also helped me figure out problem.

Thanks everyone. I really appreciate your help.

Regards,
Alexis

Hamed Nasr

Welcome! And thanks for the feedback.

Gustav Brock

> And the view? Perhaps it states NOT NULL somehow? Not that I know how it should be done.

Now I know.

/gustav