Solved Access query with linked tables slow

[nector]

I'm only pulling one record at a time to be used by the VBA record set from Cloud SQL server, I'm using this strategy as per advice from this platform , yes there is an improvement , but the 10 seconds its taking to pull data is too much and users do like this.

Here is my simple Query:

SELECT tblCustomerInvoice.InvoiceID, tblLineDetails.ItemesID, tblProducts.ProductName, tblProducts.ProductID, tblLineDetails.Quantity, tblLineDetails.UnitPrice, tblLineDetails.Discount, tblLineDetails.IsTaxInclusive, tblLineDetails.RRP, tblLineDetails.VAT, ((([Quantity]*[UnitPrice]))) AS TotalAmount, tblLineDetails.TaxClassA, tblLineDetails.TourismClass, tblLineDetails.ExciseClass, tblLineDetails.InsuranceClass, IIf([IsTaxInclusive]<0,"True","False") AS CGControl, tblProducts.BarCode, tblLineDetails.ESDPrice, tblLineDetails.Duty, tblCustomerInvoice.ReceiptType, tblCustomerInvoice.PaymentMode, tblCustomerInvoice.SalesType, tblCustomerInvoice.LocalPurchaseOrder, tblCustomerInvoice.Cashier, tblCustomerInvoice.BuyerTPIN, tblCustomerInvoice.BuyerName, tblCustomerInvoice.BuyerTaxAccountName, tblCustomerInvoice.BuyerAddress, tblCustomerInvoice.BuyerTel, tblCustomerInvoice.OrignalInvoiceCode, tblCustomerInvoice.OrignalInvoiceNumber, tblCustomerInvoice.TheNotes, tblCustomerInvoice.Moneytype, tblCustomerInvoice.FCRate
FROM tblCustomerInvoice INNER JOIN (tblProducts INNER JOIN tblLineDetails ON tblProducts.ProductID = tblLineDetails.ProductID) ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID
WHERE (((tblCustomerInvoice.InvoiceID)=[Forms]![frmCustomerInvoice]![CboEsdInvoices]));

If I convert it to a VIEW in SQL server cloud can I gain performance here, in any cases I will continue pulling one record at a time , lastly can a combo box be attached to a to View also even if in that area the combo box load with empty record until the users type in the first three characters only then can a short list is built

 

[Minty]

Any query with multiple joins to large tables will work better on the server as a view than locally joining them in Access.

This looks like it is simply a look up list for a continuous form?
If so and you don't need to edit it, make sure you open it as a snapshot on the form, that will also speed things up.

 

[nector]

Okay thanks Minty I missed the key word snapshoot, very correct

 

[Pat Hartman]

Any query with multiple joins to large tables will work better on the server as a view than locally joining them in Access.

I won't dispute the first part of this sentence but I will dispute the end. Access does NOT do the join locally UNLESS you are joining a SQL Server table to a local table or an Oracle table, etc.

Access makes every effort to make all queries "pass" through. That doesn't mean that you can't defeat Access. You certainly can.

Views of SQL Server table with joins are more efficient for the same reason that querydefs of Jet/ACE tables are more efficient. The server calculates the execution plan and saves it. The plan doesn't have to be calculated on the fly every time the query runs. In the case of SQL Server, you might even be able to add indexes to views to help even more.