Access ADP with MSDE2K: Error updating table via VIEW on bound subform

[Martin Hunter]

Hi all,

Problem context:

I am working on an MSDE database with an Access ADP front-end. I'm
getting an error when inserting via a Subform which is bound to a VIEW
over two tables (TABLE1, TABLE2 related many-to-one (many TABLE1 to
one TABLE2)).

"Violation of PRIMARY KEY constraint 'aaaaaa<TABLE2>'. Cannot insert
duplicate into object '<TABLE2>'".

Detail:

The ADP contains a Form, which is bound to a TABLE. The Form contains
the aforementioned Subform, which is bound to the VIEW. The Form and
subform are related using the Link Master / Link Child of the subform
control. The 'UniqueTable' property of the Subform is set to TABLE1,
and 'Recordset Type' to 'Updateable Snapshot'.

Thoughts so far:

As I understand it, the UniqueTable property dictates which of the
TABLEs specified in the VIEW Access should update. As I have set the
UniqueTable property to be TABLE1, it seems curious to me as to why
Access should make any attempt to insert into TABLE2 at all. I can
only think it has something to do with the relationships specified,
though I am at a loss as to know how and why.


Any help much appreciated,

Mart.

 

[Mary Chipman]

The best way to get de-mystified is to create a Profiler trace so you
can see what's going on under the covers when you update data. If you
use a view, and you want Access to use the view to update data instead
of using the base tables, then you must create the view using the WITH
VIEW_METADATA attribute. See SQL BOL for more information.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

 

[Brian M. Sockey]

Hi Martin,

If I understand correctly, you are trying to insert a new record into just
one table of a view based on two tables. Unfortunately, Access doesn't work
this way. When you insert a new record into a 2-table view Access always
tries to insert a new record into both underlying base tables of the view,
regardless of what the unique table property is set to. So whatever values
you have in the fields that are a part of "Table2", Access will try to use
those values to insert a new record into Table2, even if you only intended
to add a record to Table1. If the Table2 PK values already exist in Table2,
then you will get an error like the one you're getting.

It is possible to insert a new row into just one table of a multi-table view
but it needs to be done on the SQL Server end by using triggers and, as Mary
suggested, specifying WITH VIEW_METADATA in the design of the table so
Access updates the view directly instead of the base tables. However, it
can be a little tricky to get to working right with Access, and won't work
at all with identity (autonumber) fields. You might be better off
reworking your form to use single tables for the subforms that need to do
inserts or handle the inserts programmatically.

The unique table property just helps Access to figure out how to resynch a
record after you edit or insert one into a multi-table view. In your case,
you're getting an error attempting the insert itself so the unique table
property doesn't come into play yet.

Hope this helps,

Brian M. Sockey
www.farsightsolutions.com
www.televantagenorthwest.com

 

[Martin Hunter]

Brian & Mary,

Many thanks for your help and clarifications - much appreciated. It
can be difficult sometimes to figure out - especially with the usual
terse Microsoft resources - what's happening under the hood!

I'll take a look at all of the options you gave. I'll probably end up
doing the INSERTs using ADO and VBA, as Brian suggests, since both
tables use identity columns. Ah, yet another shift in paradigm.....

Cheers again,

Mart.