select query with inner join

[Andy74]

Hello,

I have a simple "Orders" form, based on a select query, something like:
SELECT *.tblOrders, country.tblCustomer FROM tblOrders INNER JOIN tblCustomers ON tbl.Orders.CustId=tbl.Customers.CustID

The form simply display the orders with some customers details like Country.

My problem is that when a user cancel a record from the form then not only the record in the tblOrder is cancelled but also the corresponding record in the tblCustomers. The two tables are on different databases, connected with ODBC to access (tblCustomers is in Oracle, tblOrders is in Sql server), so I cannot put foreign keys on the tblOrders.

I think the situation is very typical, but how can I tell Access which side of the JOIN expression should be without deletion? I have tried with a LEFT JOIN but the query becomes not updatable and so useless for the form.

Thanks for any help

 

[MarkK]

Typically, I base a single form on a single table. If I need other information from a related table then I use a subform, or do a lookup with a combo, or do a lookup in code in the Current event of the form. Then my form's RecordSource (based on a single table) gives me its best possible functionality with the greatest simplicity.

 

[Andy74]

Thanks MarkK,

I understand but in some way I need a query to be under the form because the query is programmatically built when a user logs in. According to the user then query will restrict the data seen by the form (e.g. a user will see only orders for customers in some countries). It cannot be a filter in the form because I want to keep filters capability in the form.

I have found a solution but I don't like it very much: I use only the tblOrders table in the query and I calculate the other fields with Dlookup functions in the query. I think this will slow down the query in the long term.

 

[MarkK]

If the country of an order is critical to the processing of that order then put the country in the order table.

 

[Andy74]

Ok, that is also an option but I don't like duplicating information.


Inviato dal mio iPhone utilizzando Tapatalk