Can't Update Multiple table view?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a linked view based on four tables in SQL Server. This view is used
as the RecordSource for a form in Access. I am able to add new records, as
well as to update records, however if I try to update records from more than
one of the base tables I get this error:

ODBC--delete on a linked table "AllView" failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]View or function 'dbo.MyView'
is not updatable because the modification affects multiple base tables.
(#4405)

I also get this error if I try to delete any record.

So, I am wondering whether or not it is feasible to use a view as the
RecordSource for my form.

Previously, I was simply using four separate forms and using Option buttons
to navigate between them, however my immediate superiors (the president and
CEO of the company) specified that they wanted everything on one page.

I know that it's possible to use subforms and use Master/Child fields to
link everything up, however if I do this then at no point are there more than
a few records in any of the subforms, which means that I can't effectively
use a Find or even a Filter function on any of the fields in my subforms.
This is potentially a problem because there are fields in my subforms (if I
use subforms) that I would like to be able to search.

Please let me know if you have any suggestions as to how I might solve this
problem.

Thanks,

Chris
 
Dear Chris:

Under most circumstance, it is illogical to expect a query based on the join
between multiple tables to be updatable.

As an example, I take a pair of tables in a typical one-to-many
relationship. Let's say we show invoices for set of accounts:

Account (table)
AccountName
ResponsiblePerson

Invoice (table)
InvoiceNumber
AccountName
InvoiceAmount

So, a query may show:

AccountName ResponsiblePerson InvoiceNumber InvoiceAmount
Smith Family John Smith 1 40.00
Smith Family John Smith 2 37.00
Smith Family John Smith 3 23.00

Now, clearly there is only one Account row referenced here.

Now, if you put this in a singe form, what do you expect if someone changes
this to read:

AccountName ResponsiblePerson InvoiceNumber InvoiceAmount
Smith Family John Smith 1 40.00
Smith Family Mary Smith 2 37.00
Smith Family John Smith 3 23.00

Consider how you would expect the database to change if this were done.

There's no way it could be done.

I build forms with multiple subforms that accurately reflect the actual
organization. I use continuous subforms for both the parent and child
tables, not using the built in linking, but using the Current event of the
parent table's form to change the filtering of the child form. This gives
the exact functionality of the link fields, but allows everything to be
continuous forms.

The kind of filtering and searching you desire can certainly be performed.

Filter into a combo box, and then select the desired combination from there
and set the subforms affected to show what is chosen. The combo box can
show several columns located in the filter or search and the user can see to
compare them there, and quickly select one if editing is desired.

I know that editing several tables in one form is possible under very
limited circumstances, but that rarely suffices. What I propose is a
thorough general solution. It encorporates features such as "drill down" in
which a user progresses easily from the general to the more specific to
locate just what they need.

Tom Ellison


Chris Burnette said:
I have a linked view based on four tables in SQL Server. This view is used
as the RecordSource for a form in Access. I am able to add new records,
as
well as to update records, however if I try to update records from more
than
one of the base tables I get this error:

ODBC--delete on a linked table "AllView" failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]View or function
'dbo.MyView'
is not updatable because the modification affects multiple base tables.
(#4405)

I also get this error if I try to delete any record.

So, I am wondering whether or not it is feasible to use a view as the
RecordSource for my form.

Previously, I was simply using four separate forms and using Option
buttons
to navigate between them, however my immediate superiors (the president
and
CEO of the company) specified that they wanted everything on one page.

I know that it's possible to use subforms and use Master/Child fields to
link everything up, however if I do this then at no point are there more
than
a few records in any of the subforms, which means that I can't effectively
use a Find or even a Filter function on any of the fields in my subforms.
This is potentially a problem because there are fields in my subforms (if
I
use subforms) that I would like to be able to search.

Please let me know if you have any suggestions as to how I might solve
this
problem.

Thanks,

Chris
Click to expand...
 
Tom, thanks for the response. I have a few questions about what you said.

When you say that you use continuous subforms for both the parent and child
tables, do you mean that all your forms are subforms, or are you actually
linking a table to the main form? Also, what is the advantage to using
Continuous subforms over Single Form view?

What do you mean by "filter into a combo box" ? I think I know how to set
the filtering of a subform in the Current event so that the same record will
show up in both forms (I would use FindRecord), but what is the combo box
used for?

Currently, I have a combo box that a user can select a column from and then
enter criteria into an InputBox for that column to filter the form (i.e. the
would select the LastName column and type in Smith to see everyone with a
last name of Smith). Is this what you mean, or do you implement it
differently?

Again, I appreciate the help.

Thanks,

Chris
 
Dear Chris:

There is, of course, a main form on which the subforms reside. This form
usually has not associated table or query, and no "data" on it.

There is no "linking" in the sense of the parent/child link properties. The
same effective capability is performed by changing the RecordSource of
subsidiary subforms. After you do this once or twice, ti becomes very
simple and natural.

The advantage of a continuous form is that you can see multiple rows
simultaneously. You can use a scroll bar to move through the rows several
at a time. You can scroll to the bottom to add a new row.

A combo box is commonly used to filter all the rows in a set of subforms.
This is sometimes apropriate and useful. This combo box is an optional
facility, having nothing to do with the basic functionality of the behavior
of the subforms.

On the Current Event of a subform, you set the RecordSource of the
immediately dependent subforms. Their Current Events fire as you do this,
filtering the subforms that depend on them, and so on.

Do not use FindRecord. Instead, change the RecordSource of the subforms,
filtering it according to the parent subform's selected row.

Sorry if this is somehow not clear. Don't strain at it. It is a simple way
to replace the Link Parent/Child facility without experiencing its
restrictions.

Tom Ellison
 
Back
Top