Clash Royale CLAN TAG#URR8PPP

How do I write to an access query that uses linked tables?

I have been tasked with create a tracker for company work flow.

I have 10 tables with data in them. There are attributes all the tables have in common. I made a table with those attributes, giving those records a unique ID that could join them to the unique attribute records of the original tables. I am also linking a personnel table to the original tables. All of these tables exist on my SQL Server back end. I Have made a query in Access that displays all the information I was given. I'm going to use the forms in access as a front end to display, edit, and add records.

The issue I am encountering is that I can not write to a query that has externally linked tables. I have spent a lot of time normalizing this data and I know I can get around it by making tables with redundant attributes in SQL and not writing to the query, but rather write to the linked tables instead. Just wondering if there is a way around this.

Thanks

1 Answer
1

In general, even without linked tables, such queries are NOT updatable.

The general approach when working with multiple tables is to use sub forms for the child tables. That way, each form is only bound to one table. (You are free even to bind such forms directly to the linked table).

Thus, you might say have a customer table and then a table of invoices. So your main form will display the one customer and is bound to that one table.

In the sub form, you can then display all of the invoices.

So to combine multiple tables into a form or screen that allows users to update the data, or add more data, you don’t build some query that joins all the data together, but simply combine several forms into one form. But each of those separate forms will display data from the given related child table.

Here is a typical invoice form thus built in Access:

The top part is the “main” form of an invoice. It is bound to the customer table – one record. Then the multiple lines of detail is the invoice details table. So the form does NOT use queries, but each part of the form and sub forms are bound to the given related tables. You are binding each form directly to the linked table (or tables if you need to show related data like above).

This approach allows you to cobble together a set of forms that edit related tables, but each form is bound directly to the linked table.

So the fact of linked tables or not is moot – such queries are not used to edit data and such queries from link or non-linked tables are NOT updatable.

So your form + sub forms will follow the pattern of related tables that you need to work together as one whole view and means to edit data. You don’t need nor want to use a query to fill these forms.

You most certainly will provide some kind of “search” form, or some means to pull up say one customer invoice, and that invoice along with its sub forms will display the related data, and also allow editing of that data.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.