Solved Record set too slow with SQL server Cloud & Parameters not working (1 Viewer)

[nector]

I'm using the record set to get the banking details to be part of the invoice instead of Dlook up but the loading of invoices is slow causing clients to wait for some time which I feel is bad for the business, could you please assist on how to improve the speed. if I remove the banking details the invoice becomes faster any idea how to sort put this?

Private Sub Report_Load()
Dim db As DAO.Database
Dim strSQL As String
Dim prm As DAO.Parameter
Set db = CurrentDb
strSQL = "SELECT CompanyName,Address,Town,VATRegistration,Country,AccountName,BankName,AccounNumber,BranchName,SortCode,SWIFTCODE,ContactPerson," & _
"ContactTelephone,ContactEmail FROM [tblCompany] WHERE [CompID] = 1"
With db.CreateQueryDef("", strSQL)
For Each prm In .Parameters
prm.Value = Eval(prm.Name)
Next
    With .OpenRecordset(dbOpenSnapshot, dbSeeChanges)
        If Not .EOF() Then
         Me.txtCompanyName = .Fields(0).Value
         Me.txtAddress = .Fields(1).Value
         Me.txttown = .Fields(2).Value
         Me.txtTpins = .Fields(3).Value
         Me.txtcontinent = .Fields(4).Value
         Me.txtAccountNumber = .Fields(7).Value
         Me.txtBranch = .Fields(8).Value
         Me.txtSortCode = .Fields(9).Value
         Me.txtSwiftCode = .Fields(10).Value
         Me.txtttCompanyName = .Fields(0).Value
         Me.txtBankNames = .Fields(6).Value
   
    End If
    End With
End With
Set prm = Nothing
Set db = Nothing
If (Me.txtgames = 0) Then
Me.txtgames.Visible = False
End If
End Sub

Parameters not working below

The code below is suppose to use parameters to select only one record at a time but its updating the entire record in the table any Idea here how to stop this

Private Sub CmdPostJvs_Click()
Dim SCh As String
Dim Cancel As Integer
If (((DSum("Dr", "tblVoucher", "[CreateID] =" & Me.CboCreateID)) - (DSum("Cr", "tblVoucher", "[CreateID] =" & Me.CboCreateID))) <> 0) Then
Cancel = True
MsgBox "Please note that your Journal is out of balance", vbExclamation, "Check both debits and credits are not equal"
Exit Sub
End If
If IsNull(Me.CboCreateID) Then
MsgBox "Please Select the journal to post", vbInformation, "Post Financial Journal"
Me.CboCreateID.SetFocus
Exit Sub
ElseIf IsNull(Me.Cbostatus) Then
MsgBox "Please Select Approved to post", vbInformation, "Post Financial Journal"
Me.Cbostatus.SetFocus
Exit Sub
End If
If (Me.txtbalance <> 0) Then
MsgBox "Please Check And Clear The Difference In The Box Below", vbInformation, "Post Financial Journal"
Cancel = True
Me.CboCreateID = Null
Me.Cbostatus = Null
Exit Sub
ElseIf (Me.txtbalance = 0) Then
Cancel = False
End If
Dim db As DAO.Database
Dim strSQL As String
Dim strSqLOne As String
Set db = CurrentDb
strSQL = "SELECT * FROM [QryJournals] WHERE [tblJournalHeader].[CreateID] = " & [Forms]![FrmJournalVourcherPosting]![CboCreateID]
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryJournals"
strSqLOne = "SELECT * FROM [QryFinJournal] WHERE [tblJournalHeader].[CreateID] = " & [Forms]![FrmJournalVourcherPosting]![CboCreateID]
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryFinJournal"
db.Close
Set db = Nothing
MsgBox "Journal Posting successful", vbInformation, "Please Proceed"
Me.CboCreateID.Requery
Me.CboCreateID = Null
Me.Cbostatus.Requery
Me.Cbostatus = Null
End Sub

Below is a pass through updatate which attach to the above query

UPDATE tblJournalHeader SET tblJournalHeader.Status = '1';

 

[The_Doc_Man]

So many issues I don't even know where to start.

First, and this isn't a speed issue, your first exhibit's "WITH" block uses explicit .Value properties for every field, but since .Value is the default property, you can omit that. Makes typing easier.

The fact that you have nested WITH-blocks makes your code harder to read. You should practice indenting things a bit more consistently.

You also don't close the recordset that you opened in that sequence. You erase the prm and db, but the recordset is not properly closed. There is some debate on the subject, but in general it is not a good idea to leave a recordset dangling, particularly since there is no explicit object variable anchoring the recordset. And we have at least some online articles that suggest that leaving a dangling recordset could cause a process to hang during shutdown.

For your subroutine CmdPostJvs_Click, you declare variable Cancel and sometimes set it to TRUE based on the IF statement, but the catch is that you do nothing with the Cancel variable... nothing at all. If you expected it to persist or be visible in some other context, it won't be.

You complain about a DLookup but I don't see one in your exhibits.

And then you have that UPDATE query that has no WHERE clause, so it will set Status = '1' for every record in tblJournalHeader. If that is what you wanted, go for it. But I am generally suspicious of unlimited UPDATE sequences.

Beyond that, I was looking for obvious slowdowns, but the exhibits only hint at possible sources, nothing definite. Part of the problem there is that I have no idea what you were trying to do so I can only guess as the logic and thus can ALSO only guess at its validity.

 

[arnelgp]

which part is the Parameter? i can't find them.
consider passthrough query.

 

[cheekybuddha]

There is no advantage to using a pass-through query and unbound form like you are doing here.

Bind the form to a linked table.

Open the form using the WHERE argument to pass the ID of a single record.

DoCmd.OpenForm "YourFormName", , , "CompID = " & Me.txtSearcID

The second block of your code makes no sense.

 

[arnelgp]

There is no advantage to using a pass-through query and unbound form like you are doing here.

you are just filtering the form after Fetching All records?

 

[cheekybuddha]

you are just filtering the form after Fetching All records?

No, use the WHERE argument rather than the FILTER argument - just pull a single record.

 

[arnelgp]

No, use the WHERE argument rather than the FILTER argument - just pull a single record.

you should prove what you are saying.
I just tested it and the Form is in Filtered Mode.

much worst that what the Op has.

 

[Minty]

The second block of code sets two SQL strings and then does nothing with them at all.
You also set a reference to db and never use it.
You set warnings to false twice without ever resetting it to true, so you would have no clue if there was any error in that code, which is simply running two saved queries, that I assume are action queries.

I have seen this type of set up in your code on more than a few occasions, do you ever set a break point and walk through it to actually try and see what is happening?
I would strongly recommend you do so NOW, as people will get bored trying to fathom out what you are doing.

It's as if you have simply pasted some code you found and hope it does something that works, with no real idea of what it is doing.

 

[nector]

Many thanks people for the value contribution I take all your comments into account.

Regards

Chris

 

[KitaYama]

Many thanks people for the value contribution I take all your comments into account.

Regards

Chris

Now that you're planning to re-consider your code, all those Cancel=True and Cancel=False do nothing.
Cancel is a parameter of some of form's events and has its purpose within those events.
In a normal procedure, setting Cancel=False and exiting the sub, doesn't mean anything at all.

 

[Minty]

If you take on board the suggestions there is something that will help.

Are these your bank details so customers can pay you?
If so, and I assume the banking details very rarely change, why not simply load them into a local table from a central setting table and then either set them or better still join to them in the report/form.

Refresh the local table every time you load the database.

Your problems appear to be caused by trying to reuse code where it's not suitable for the task at hand.
Next time plan a solution and write what you need to achieve that, rather than cobbling together random bits that sort of did a bit of what you wanted. It will almost certainly save you time and effort.
Don't dismiss creating a query in the query editor and using that, just because you have a SQL Server backend.

 

[cheekybuddha]

you should prove what you are saying.
I just tested it and the Form is in Filtered Mode.

much worst that what the Op has.

@arnelgp, Sure.

It helps if you understand how the ODBC driver works.

I log in to an SQLServer with DBeaver and view the current connections to the server:

sp_who;

with results:

spid|ecid|status       |loginame|hostname|blk  |dbname |cmd                       |request_id|
----+----+-------------+--------+--------+-----+-------+--------------------------+----------+
  78|   0|sleeping     |david   |dm11    |0    |test   |AWAITING COMMAND          |         0|
  79|   0|sleeping     |david   |dm11    |0    |test   |AWAITING COMMAND          |         0|
  81|   0|runnable     |david   |dm11    |0    |test   |SELECT                    |         0|

SQLServer has a table "Contacts":

SELECT
  COUNT(*) AS NumRecs
FROM Contacts
;

with results:

NumRecs|
-------+
  17689|

I have an Access form "frmContacts" with RecordSource to linked table to SQLServer "dbo_Contacts".

I open the form with no WHERE or FILTER argument:

DoCmd.OpenForm "frmContacts"

In DBeaver I check the connections again:

sp_who;

with results:

spid|ecid|status       |loginame|hostname     |blk  |dbname |cmd                       |request_id|
----+----+-------------+--------+-------------+-----+-------+--------------------------+----------+
  51|   0|sleeping     |david   |DM-WIN10-64  |0    |test   |AWAITING COMMAND          |         0|
  52|   0|sleeping     |david   |DM-WIN10-64  |0    |test   |AWAITING COMMAND          |         0|
  78|   0|sleeping     |david   |dm11         |0    |test   |AWAITING COMMAND          |         0|
  79|   0|sleeping     |david   |dm11         |0    |test   |AWAITING COMMAND          |         0|
  81|   0|runnable     |david   |dm11         |0    |test   |SELECT                    |         0|

You can see 2 new connections coming from Access (DM-WIN10-64 is my windows machine).

Let's look at the commands from those connections:
First connection (spid = 51):

DBCC inputbuffer( 51 );

returns:

EventType     |Parameters|EventInfo                                                 |
--------------+----------+----------------------------------------------------------+
Language Event|         0|SELECT "dbo"."Contacts"."ContactID" FROM "dbo"."Contacts" |

Second connection (spid = 52)

DBCC inputbuffer( 52 );

returns:

EventType     |Parameters|EventInfo                                                                                    |
--------------+----------+---------------------------------------------------------------------------------------------+
Language Event|         0|(@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int)           |
                         |SELECT "ContactID","CompanyID","FirstName","Surname","Address1","Address2","City",           |
                         |       "County","Postcode","CountryID","MobileNo","HomeNo","WorkNo","Email",'#S_C_H#'     ,  |
                         |       "Initials","Title","DOB","CreditLimit","IsDriver",'#S_C_H#'      ,                    |
                         |       "Shortcode","IsStaff","CreditDays",'#S_C_H#'            ,                             |
                         |       "FromOnline","Password","ReferrerID","IsAgent",'#S_C_H#'                              |
                         |FROM "dbo"."Contacts"                                                                        |
                         |WHERE "ContactID" = @P1                                                                      |
                         |   OR "ContactID" = @P2                                                                      |
                         |   OR "ContactID" = @P3                                                                      |
                         |   OR "ContactID" = @P4                                                                      |
                         |   OR "ContactID" = @P5                                                                      |
                         |   OR "ContactID" = @P6                                                                      |
                         |   OR "ContactID" = @P7                                                                      |
                         |   OR "ContactID" = @P8                                                                      |
                         |   OR "ContactID" = @P9                                                                      |
                         |   OR "ContactID" = @P10                                                                     |

So you can see the ODBC driver first issues a query to get all the ContactID's (remember there were 17689 records), then issues a second query to get all the data for the first 10 records.

OK, so I close the form and re-open using:

DoCmd.OpenForm "frmContacts", , ,"ContactID = 123"

I check the connections again:

sp_who;

with results:

spid|ecid|status       |loginame|hostname     |blk  |dbname |cmd                       |request_id|
----+----+-------------+--------+-------------+-----+-------+--------------------------+----------+
  51|   0|sleeping     |david   |DM-WIN10-64  |0    |test   |AWAITING COMMAND          |         0|
  71|   0|sleeping     |david   |DM-WIN10-64  |0    |test   |AWAITING COMMAND          |         0|
  78|   0|sleeping     |david   |dm11         |0    |test   |AWAITING COMMAND          |         0|
  79|   0|sleeping     |david   |dm11         |0    |test   |AWAITING COMMAND          |         0|
  81|   0|runnable     |david   |dm11         |0    |test   |SELECT                    |         0|

The second connection from Access has a new spid = 71

Let's look again at the Access commands:
First for first connection (spid = 51):

DBCC inputbuffer( 51 );

returns:

EventType     |Parameters|EventInfo                                                                                         |
--------------+----------+--------------------------------------------------------------------------------------------------+
Language Event|         0|SELECT "dbo_Contacts"."ContactID" FROM "dbo"."Contacts" "dbo_Contacts" WHERE ("ContactID" = 123 ) |

Then second connection (spid = 71):

DBCC inputbuffer( 71 );

returns:

EventType     |Parameters|EventInfo                                                                                         
--------------+----------+---------------------------------------------------------------------------------------------|
Language Event|         0|(@P1 int)                                                                                    |
                         |SELECT "ContactID","CompanyID","FirstName","Surname","Address1","Address2","City",           |
                         |       "County","Postcode","CountryID","MobileNo","HomeNo","WorkNo","Email","ContactNotes",  |
                         |       "Initials","Title","DOB","CreditLimit","IsDriver","MarketingList",                    |
                         |       "Shortcode","IsStaff","CreditDays","ReservationReminder",                             |
                         |       "FromOnline","Password","ReferrerID","IsAgent","PhotoURL"                             |
                         |FROM "dbo"."Contacts"                                                                        |
                         |WHERE "ContactID" = @P1                                                                      |

This time you can see that ODBC driver with the first connection just selected a single ContactID and used that single parameter in the second query to fetch the record.

The Access form shows 'Filtered' in the record selector bar (what you saw when you tried):

Checking the filter in the Immediate Window gives:

?Forms.frmContacts.Filter
ContactID = 123

So you can see this is how Access applies a WHERE clause argument to a form, but as you can see from querying what the server received and returned the ODBC driver is clever enough just to request a single record from the linked table.

I used to think like you do.

 

[arnelgp]

i want to see a "stored procedure" in SQL Server that returns recordset from MS Access passthrough query.
does it create the "two" query in the Server?
It was said (and proven) that passthrough is much faster than regular query.

 

[cheekybuddha]

It was said (and proven) that passthrough is much faster than regular query.

Your turn to prove it.

 

[arnelgp]

i don't have Server nor DBWeaver.

does that mean that the "Filtered" you see on the form is a lie? what if you Unfilter it?

 

[cheekybuddha]

If you unfilter it will issue the queries as if you had used plain DoCmd.OpenForm.

Re. your previous point about pass-through queues, it can be useful for complex queues with multiple joins or calculations, or selecting using a where condition that can not use ask index. The heavy processing can be done more quickly in the server

But remember, you get the whole resultset returned, so if there are a lot of records they all come down the pipe.

Sometimes it's useful to use a PT to get a recordset to use as a combo RowSource so the data just comes once and there is no risk of re-running the query each time the combo is entered.

DBeaver is not special - you just can not get SSMS on linux, and I don't install it in Windows because it's several gigabytes! (Also DBeaver can connect to MySQL and postgres too, and is available on Windows if you want it, also free)