Queries Directed to Cloud SQL Server from Access Extreamely Slow

[spaLOGICng]

Well after investigating the entire VBA code I discovered that the part which is slowing the processing is only the lookup which taking too much time to read the above code

Here is the DLookup

Private Sub CmdCwrite_Click()
On Error GoTo Err_Handler
Dim Cancel As Integer
If IsNull(Me.txtEsDFinInvoice) Then
Beep
MsgBox "Please select the invoice to sign on", vbOKOnly, "Data is required here"
Cancel = True
Exit Sub
End If
Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim root As Dictionary
    Dim transaction As Dictionary
    Dim transactions As Collection
    Dim item As Dictionary
    Dim items As Collection
    Dim Tax As Collection
    Dim i As Long
    Dim j As Long
    Dim t As Long
    Dim itemCount As Long
    Dim taxCount As Long
    Dim strTaxes As Boolean
    Dim invoiceCount As Long
    Dim json As Object
    Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
    Dim lngStatus As Long
    Dim strError  As String
    Dim strData   As String
    Dim Details As Variant
    Dim n As Integer
    Dim s As String
    Dim Z As Long
    Set root = New Dictionary
    Set transactions = New Collection
    Set db = CurrentDb
    Set qdf = db.QueryDefs("QryJson")
    For Each prm In qdf.Parameters
        prm = Eval(prm.Name)
    Next prm
    Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbSeeChanges)
    Set qdf = Nothing
    rs.MoveFirst
    Do While Not rs.EOF
        Set transaction = New Dictionary
        transaction.Add "PosVendor", "Nector Prime Accounting Solutions"
        transaction.Add "PosSoftVersion", "2.0.0.1"
        transaction.Add "PosModel", "Cap-2017"
        transaction.Add "PosSerialNumber", DLookup("PosSerialNumber", "tblEFDs", "ID = 1")
        transaction.Add "IssueTime", DateAdd("n", 120, Now())
        transaction.Add "TransactionType", DLookup("ReceiptType", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "PaymentMode", 3
        transaction.Add "SaleType", 1
        transaction.Add "LocalPurchaseOrder", DLookup("LocalPurchaseOrder", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "Cashier", DLookup("Cashier", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "BuyerTPIN", DLookup("BuyerTPIN", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "BuyerName", DLookup("BuyerName", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "BuyerTaxAccountName", DLookup("BuyerTaxAccountName", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "BuyerAddress", DLookup("BuyerAddress", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "BuyerTel", DLookup("BuyerTel", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "OriginalInvoiceCode", DLookup("OrignalInvoiceCode", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "OriginalInvoiceNumber", DLookup("OrignalInvoiceNumber", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "Memo", DLookup("TheNotes", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "Currency-Type", DLookup("MoneyType", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        transaction.Add "Conversion-Rate", DLookup("FCrate", "tblCustomerInvoice", "[InvoiceID]= " & Me.CboEsdInvoices)
        '--- loop over all the items
        itemCount = Me.txtinternalaudit
        Set items = New Collection
        For i = 1 To itemCount
            Set item = New Dictionary
            item.Add "ItemId", i
            item.Add "Description", DLookup("ProductName", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
            item.Add "BarCode", DLookup("BarCode", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
            item.Add "Quantity", DLookup("Quantity", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
            item.Add "UnitPrice", DLookup("UnitPrice", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
            item.Add "Discount", 0
            '--- loop over all the taxes
            taxCount = 1
            Set Tax = New Collection
             strTaxes = DLookup("CGControl", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
            '--- loop over all the invoices
            invoiceCount = 1
            For j = 1 To invoiceCount
                For t = 1 To taxCount
            Next t
            item.Add "TaxLabels", Tax
           
            Tax.Add DLookup("TaxClassA", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
               If Len(Trim(Nz(DLookup("TourismClass", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i)), ""))) > 0 Then
               Tax.Add Nz(DLookup("TourismClass", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i)), "")
               End If
            If Len(Trim(Nz(DLookup("InsuranceClass", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i)), ""))) > 0 Then
               Tax.Add Nz(DLookup("InsuranceClass", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i)), "")
               End If
            If Len(Trim(Nz(DLookup("ExciseClass", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i)), ""))) > 0 Then
               Tax.Add Nz(DLookup("ExciseClass", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i)), "")
               End If
            item.Add "TotalAmount", DLookup("TotalAmount", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
            item.Add "IsTaxInclusive", strTaxes
            item.Add "RRP", DLookup("RRP", "QryJson", "InvoiceID =" & Me.CboEsdInvoices & " AND ItemesID =" & CStr(i))
               
            Next j
           
            items.Add item
        Next i
        transaction.Add "Items", items
       
        rs.MoveNext
    Loop
   
    root.Add "", transaction
    On Error Resume Next
    intPortID = Forms!frmLogin!txtFinComPort.value
    Call CommFlush(intPortID)
    If lngStatus <> 0 Then
Application.Quit
ElseIf lngStatus = 0 Then
End If
    ' Write data to serial port.
    ' Build data packet to transmit (passing command code, and data to package)
    strData = BuildData(JsonConverter.ConvertToJson(transaction, Whitespace:=3))
'Here the chief auditor down confirmed the slowness above
n = FreeFile()
Open "C:\Users\chris.hankwembo\Desktop\Testing\test.txt" For Output As #n
Print #n, strData
Close #n
MsgBox "strData:" & vbCrLf & ShowHex(strData)
               
    ' Send the data packet and check for error
    lngStatus = CommWrite(intPortID, strData)
    If lngStatus <> Len(strData) Then
Beep
MsgBox "There is no data to write", vbOKOnly, "Data is required here"
    Application.Quit
   
        ' Handle error.
    lngStatus = CommGetError(strError)
        MsgBox "COM Error: " & strError
    End If

rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set json = Nothing
    Set transaction = Nothing
    Set transactions = Nothing
    Set item = Nothing
    Set items = Nothing
    Set Tax = Nothing
    Set fld = Nothing
    Set root = Nothing
    Set qdf = Nothing
    Set prm = Nothing
    Set Details = Nothing

Exit Sub
Exit_CmdCwrite_Click:
Exit Sub
Err_Handler:
MsgBox Err.Number & Err.Description, vbExclamation, "Error"
Resume Exit_CmdCwrite_Click
End Sub

The above is reading the data from the code below:

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]));

The problem here is how to do away with DLookup

Avoid using DLookups.

I am also not sure why you are making this more complicated than it needs to be. You do not need JSON to insert Records into a Cloud MS SQL Server DB.

Make sure your DB has a Link to the Table that you are wanting to Insert into, then just use an Action Query that is filtered to the specific record and INSERT. You could literally use 1/8th of the amount of code to accomplish the same thing and eliminate the need for the DLookups at the same time.

 

[CJ_London]

Suggest read the thread again - the op is creating a json file from sql server

 

[spaLOGICng]

Suggest read the thread again - the op is creating a json file from sql server

Hi,

I understand that the OP us creating a JSon File from SQL Server. What I am suggesting is that it is not necessary. Unless I know a good reason why, my suggestion(s) will always be the most appropriate method.

This is nothing new to you, but it may be to the OP. Access can link to a Table or a View directly toSQL Server, locally or in the Cloud. Tables and Views can also be connected via ADO or DAO from Access VBA, as well as via passthrough queries. It is quite possible that the OP did not know any of this and is learning for the first time!

That said, I would like to know the reason why the OP decided to export a Table via JSON, and then we can go from there.

 

[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.