My adp: can't update a query with multiple (linked) tables

[Guest]

I am working on a Microsoft Access project (Access 2003) connected to SQL
Server 2000 backend.

I have 1 basic table: tblOrders. This table has relationships with 2 other
tables: tblClients and tblEmployees

In tblOrders the Client field is the relationship to tblClients (via the
ClientID field)
In tblOrders the OrderingEmployee field is the relationship to tblEmployees
(via the EmployeeID field)

I can make a view in sql server based only on tblOrders. This view is
updatable in sql server and in Microsoft Access. No problem.

I can make a view in sql server based on tblOrders and tblClients. This
view is updatable in sql server and in Microsoft Access. No problem.

I can make a view in sql server based on tblOrders and tblEmployees. This
view is updatable in sql server and in Microsoft Access. No problem.

But, when I make a view that is based on tblOrders and tblEmployees and
tblClients, this view is updatable in sql server fine, but Access says “the
recordset is not updatable†Why is this??!?

 

[Michael Cheng [MSFT]]

Hi,

Welcome to use MSDN Managed Newsgroup!

From your descriptions, I understood you would like to know why that view
based on three tables is not updateable. If I have misunderstood your
concern, please feel free to point it out.

I have tested this on my side with Northwind database (Table: Customers,
Orders, OrderDetail) but failed to reproduce it on my side. The table that
you are updating is a linked table that does not have a primary key or a
unique index. Or, the query or the form is based on a linked table that
does not have a primary key or a unique index. Please refer the following
KB for more detailed information.

Status bar displays an "Recordset is not updateable" message when you try
to update a linked table in Access 2000
http://support.microsoft.com/kb/304179

ACC2000: How to Use the UniqueTable Property of a Data Access Page
http://support.microsoft.com/kb/263650/EN-US/

If above does not resolved your issue, would you please generate a sample
db with the realtionship and let me know? If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!


Sincerely yours,

Michael Cheng
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.