Queries Directed to Cloud SQL Server from Access Extreamely Slow

[spaLOGICng]

I Have checked all the forms even if binded on actual tables as long as the indexes are there the performance is realy good. The nightmare is on the queries they are extremely slow, can you Imagin one line take 3 minutes to process and just 10 lines takes 40 minutes.

Here is the actual query below , but when you run it against access database it can process 30 lines in 50 seconds , then why it is too slow in Cloud Ms Sql Server:

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, tblCustomerInvoice.FCRate, tblLineDetails.Duty
FROM tblCustomerInvoice INNER JOIN (tblProducts INNER JOIN tblLineDetails ON tblProducts.ProductID = tblLineDetails.ProductID) ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID
WHERE (((tblCustomerInvoice.InvoiceID)=[Forms]![frmCustomerInvoice]![CboEsdInvoices]));

Hi Nector,

Can you give me a good reason why you exported your data to a JSon file, rather than connecting directly to the Table? I think this can be greatly simplified.

 

[CJ_London]

why you exported your data to a JSon file

that's for the OP to state although the code is commented

'' Build data packet to transmit (passing command code, and data to package)

which seems clear enough to me

and it's not one table, its a combination, such as invoice header and invoice lines

 

[arnelgp]

can he Open those "other" tables as recordset and just "filter" them according to his Criteria?
he already opened "qryJson" as recordset, why he need to use Dlookup() on same query?

 

[gemma-the-husky]

I'm sure you saw some article about the dictionary object and said - well that's cool - let me use that. Well, just because you can do something, doesn't mean you should. The dictionary object is far more useful in Word and Excel than it is in Access where you have actual tables

Again there we go! Kindly if you have nothing to say with value you do us a favour by slimply keeping quite.

The correct answer to iterate in the record set is below not what you are suggesting, thank God I did not get bothered with your comments:

transaction.Add "LocalPurchaseOrder", rs!LocalPurchaseOrder
        transaction.Add "Cashier", rs!Cashier.value
        transaction.Add "BuyerTPIN", rs!BuyerTPIN.value
        transaction.Add "BuyerName", rs!BuyerName.value
        transaction.Add "BuyerTaxAccountName", rs!BuyerTaxAccountName.value
        transaction.Add "BuyerAddress", rs!BuyerAddress.value

Out of interest, what is transaction? Is it just an object in a collection, or something else?
Does this imply you are using transaction processing within your database? I have never once found the need to declare a transaction. I don't particularly like collections to be honest. I just don't understand why you would need to add all these different fields from a recordset.

Another thing: Why are you using .value properties. I don't think I have ever used a .value property of an object. It may be necessary in some cases, but I would have to look carefully to understand what it does,, and whether I really need it. I expect your code would work just the same without including .value.