[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-08-28
8
Medium Priority
?
770 Views
Last Modified: 2012-08-29
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.
linked view
Here is a screenshot of the linked table in design mode in Access.
linked table
Thanks for your help.

Regards,
Alexis
0
Comment
Question by:alexisbr
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 38343733
There is an error when pasting null records into a form
That would indicate that the table does not allow NULLs, however without seeing the SQL Server table structure (CREATE TABLE ...) and the error message, it is difficult to be more precise.
0
 

Author Comment

by:alexisbr
ID: 38343864
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
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 38344408
And the view? Perhaps it states NOT NULL somehow? Not that I know how it should be done.

/gustav
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 31

Accepted Solution

by:
hnasr earned 1600 total points
ID: 38344903
If I understand correctly, Table in SQL , has a view and that is linked to access.

I tried to reproduce the issue, and found  no problem. Linked view is working fine.

Try to reproduce the issue with a simple table like:

create table aTable1 (id int primary key, a nvarchar(10) null, b nvarchar(10) null);
Insert into aTable1  values (1, 'a1', 'b1');
Insert into aTable1  values (2, 'a2', 'b2');
Insert into aTable1  values (3, 'a3', 'b3');
go
create view aView1 as (Select * from aTable1);
go
Select * from aView1;
id      a      b
1      a1      b1
2      a2      b2
3      a3      b3
Now linking aView1 to access through ODBC Database, it works as expected.

SELECT * FROM aView1
id      a      b
1      a1      b1
2      a2      b1
3      a3      b1

Any feedback!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38345138
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?
0
 

Author Comment

by:alexisbr
ID: 38345581
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
0
 
LVL 31

Expert Comment

by:hnasr
ID: 38346690
Welcome! And thanks for the feedback.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 38346787
> And the view? Perhaps it states NOT NULL somehow? Not that I know how it should be done.

Now I know.

/gustav
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question