inserting into a view in SQL server

Question

I have a SQL Server as backend and use ms access as frontend.

I have two tables (persons and managers), manager is derived from persons (a 1:1 relation), thus i created a view managersFull which is basically a:

    SELECT *
      FROM `managers` `m`
INNER JOIN `persons` `p`
        ON `m`.`id` = `p`.`id`

id in persons is autoincrementing and the primary key, id in managers is the primary key and a foreign key, referencing persons.id

now i want to be able to insert a new dataset with a form in ms access, but i can’t get it to work. no error message, no status line, nothing. the new rows aren’t inserted, and i have to press escape to cancel my changes to get back to design view in ms access.

i’m talking about a managers form and i want to be able to enter manager AND person information at the same time in a single form

my question is now: is it possible what i want to do here? if not, is there a “simple” workaround using after insert triggers or some lines of vba code?

thanks in advance

Answer 1

The problem is that your view is across several tables. If you access multiple tables you could update or insert in only one of them.

Please also check the MSDN for more detailed information on restrictions and on proper strategies for view updates

Answer 2

Ilya Kochetov pointed out that you can only update one table, but the work-around would be to apply the updates to the fields on one table and then the other. This solution assumes that the only access you have to these two tables is through this view and that you are not allowed to create a stored procedure to take care of this.

Answer 3

Assuming ODBC, some things to consider:

1. make sure you have a timestamp field in the person table, and that it is returned in your managers view. You also probably need the real PK of the person table in the manager view (I'm assuming your view takes the FK used for the self-join and aliases it as the ID field -- I wouldn't do that myself, as it is confusing. Instead, I'd use the real foreign key name in the managers view, and let the PK stand on its own with its real name).

2. try the Jet/ACE-specific DISTINCTROW predicate in your recordsource. With Jet/ACE back ends, this often makes it possible to insert into both tables when it's otherwise impossible. I don't know for certain if Jet will be smart enough to tell SQL Server to do the right thing, though.

3. if neither of those things works, change your form to use a recordsource based on your person table, and use a combo box based on the managers view as the control with which you edit the record to relate the person to a manager.

Answer 4

To model and maintain two related tables in access you don’t use a query or view that is a join of both tables. What you do is use a main form, and drop in a sub-form that is based on the child table. If the link master and child setting in the sub-form is set correctly, then you not need to write any code and access will insert the person’s id in the link field.

So, don’t use a joined table here. Simply use a form + sub-form setup and you be able to edit and maintain the data and the data in the related child table.

This means you base the form on the table, and not a view. And you base the sub-form on the child table. So, don't use a view here.