updateable table errors...help

[Lez]

Hi Guys,

Just looking for guidance of this issue. I have been trying to improve the
performance of my BE SQL 2005 database, and have identified a number of
tables I can cache locally to avoid getting data from the server.

The issue is when I combine the use of local tables and SQL views or tables
in a local query (i.e. I am using 2 local tables and one linked view with
the query designer window) when I try to update the information the query
states that the query is not updateable.

However using the tables or views from the SQL BE, it all works fine.

Can someone give me some guidance as to how I set the indexing etc when
linking the tables to avoid these issues. In particular, my main frmInvoice
has 2 tables in the query:

VAT_Local
dbo.View_Invoice

If I link the table with the vatID the query is updateable. However, as I
have another form that allows me to edit an invoice once created, and it is
linked by the InvoiceID as there is no index on the InvoiceID the form will
not open.

If I then link the table and index the InvoiceID I can use the form and open
the selected invoice, but the record is then not updateable as the index is
no longer on the vatID.

I have tried indexing both InvoiceID and vatID both in that situation
neither methods then work.

Advice of this would be greatly appreciated.

Regards
Lez

 

[Clifford Bass]

Hi Lez,

Do you really see that much of a performance difference when the table
is local? Usually back ends such as SQL Server are designed to optimize
queries. If things are slower with the tables all on the back end, it may be
that some indexes need to be created there.

More basic questions because I do not really grasp what is being done:
Why would you join the tables in a query when using the data in a form? Why
not use them separately? Maybe you could have the VatID from the
View_Invoice connected to a combo box that gets its possible values from the
VAT_Local (or remote) table? Then you are only dealing with the view, not a
query.

Hope that helps,

Clifford Bass