left join updatable query (1 Viewer)

[Andy74]

Hello,

I have a simple LEFT JOIN query like below. The left table (tblSmall) is a from an SQL server (1000 records), the right table (tableLarge) is from a Oracle back end (200k records).

I would like to use this query as the data source of a simple form in datasheet view where the user can see the records of tblSmall with some fields from the tblLarge.

The query as it is shown comes out as "not updatable", so I tried to change its property Recordset Type to "Dynaset - not consistent updates". This make the query updatable so it looks ok, the user can add records: the problem is that if the user cancel one record from the query then the record is cancelled from both tables, which should be absolutely avoided. I would like to have the record cancelled only from the left table, i.e. tblSmall.

I think that this can be solved with temporary tables and some programming, but I wonder if there is a quicker way to do it by just using queries and a form using query as data source of the form.

Any help is much appreciated.

 

[theDBguy]

What do you mean by "cancel?"

 

[Andy74]

What do you mean by "cancel?"

I mean deleting a record

 

[isladogs]

Try reverting to Dynaset and using SELECT DISTINCTROW instead. In the property sheet that is Unique Records = Yes.
Is the Componente field indexed?

 

[Pat Hartman]

Just because you can do something, doesn't mean that you should. Think of the magic of Access as it is allowing you to join a table from SQL Server to a table from Oracle and it is giving you an updateable recordset. Try that with other tools.

A far better solution is to use a main form with a subform. That way you don't need the join and you don't have to worry about cancels.

The reason that the query is not updateable is because of the join. You are not joining PK to FK. You are joining data field to FK. Does "componete" have a unique index on it? If not, see if you can get the SQ: Server DBA to add one for you. That will solve the problem. But the form/subform is still the better solution.

 

[Andy74]

Just because you can do something, doesn't mean that you should. Think of the magic of Access as it is allowing you to join a table from SQL Server to a table from Oracle and it is giving you an updateable recordset. Try that with other tools.

A far better solution is to use a main form with a subform. That way you don't need the join and you don't have to worry about cancels.

The reason that the query is not updateable is because of the join. You are not joining PK to FK. You are joining data field to FK. Does "componete" have a unique index on it? If not, see if you can get the SQ: Server DBA to add one for you. That will solve the problem. But the form/subform is still the better solution.

Thanks, you are right we take those things for granted but they are not at all!

I tried to add the index in SQL back end on the "componente" field, but the query is still not updatable.

Anyway I wanted to try your approach with subform and I think this is the best solution: I put the tblSmall in the main form and the tblLarge in the subform. In this way there is no worry for the cancels as you said.

Thanks!

 

[Pat Hartman]

You're welcome. It is rarely a good idea to create a query that joins tables and expect to update all the tables in the same query. Jet/ACE are much more flexible with this than other RDBMS but the form/subform is the best solution since it avoids all problems especially if you expect to update both tables.

Many of my forms are bound to queries that join multiple tables but the "other" tables are lookups and I always lock the controls they are bound to to prevent the lookup value from being changed by accident. But then they are not joining tables from separate RDBMS'