Solved

Select statement vs RecordsetClone

Posted on 2010-08-24
13
317 Views
Last Modified: 2012-06-27
I attached some access code (I'm linked to SQL 2008) and I don't understand why it's working the way it is.
If I run the code the way it is using "Set rs = Me.RecordsetClone" it falls thru rs.findfirst and is painfully slow but it finds a matching record and gives the desired results.
If I use the commented out SQL statement (which is the SQL code I copied from my record source) then it falls thru the findfirst much quicker but it does not find a match.  What am I doing wrong.  

Set db = CurrentDb()

'       strKey1 = "SELECT dbo_tblCaseNotes.ClientFileNo, dbo_tblClients.LastName, dbo_tblClients.FirstName, dbo_tblClients.MiddleName, "

'              strKey1 = strKey1 & "dbo_tblCaseNotes.CaseNotesID, dbo_tblCaseNotes.CaseNotesDate, dbo_tblCaseNotes.SupportStaffLkUpID "

'              strKey1 = strKey1 & "FROM (dbo_tblClients INNER JOIN dbo_tblCaseNotes ON dbo_tblClients.ClientFileNo = dbo_tblCaseNotes.ClientFileNo) "

'              strKey1 = strKey1 & "LEFT JOIN dbo_tblLkUpSupportStaff ON dbo_tblCaseNotes.SupportStaffLkUpID = dbo_tblLkUpSupportStaff.SupportStaffLkUpID "

'              strKey1 = strKey1 & "ORDER BY dbo_tblCaseNotes.ClientFileNo, dbo_tblCaseNotes.CaseNotesDate DESC , dbo_tblCaseNotes.SupportStaffLkUpID; "

'              Set rs = db.OpenRecordset(strKey1, dbOpenSnapshot)



        Set rs = Me.RecordsetClone

       

        If bDelete = True Then

            rs.FindFirst "[ClientFileNo]& [CaseNotesId] = " & txtClientFileNo & txtCaseNotesID

            Exit Sub

        End If

        If bNewRevu = True Then

            Call AddInterventionRcd  'if adding new

            'if dblCaseNotesID has value then a new rcd was added and screen refreshed so use saved off dbl values

            If dblCaseNotesID = 0 Then

                rs.FindFirst "[ClientFileNo]& [CaseNotesId] = " & txtClientFileNo & cboCaseNotesDate.Value

            Else

                rs.FindFirst "[ClientFileNo]& [CaseNotesId] = " & dblClientFileNo & dblCaseNotesID

            End If

            bNewRevu = False





SQL code for the recordsource = 

SELECT dbo_tblCaseNotes.ClientFileNo, dbo_tblClients.LastName, dbo_tblClients.FirstName, dbo_tblClients.MiddleName, dbo_tblCaseNotes.CaseNotesID, dbo_tblCaseNotes.CaseNotesDate, dbo_tblCaseNotes.SupportStaffLkUpID

FROM (dbo_tblClients INNER JOIN dbo_tblCaseNotes ON dbo_tblClients.ClientFileNo = dbo_tblCaseNotes.ClientFileNo) LEFT JOIN dbo_tblLkUpSupportStaff ON dbo_tblCaseNotes.SupportStaffLkUpID = dbo_tblLkUpSupportStaff.SupportStaffLkUpID

ORDER BY dbo_tblCaseNotes.ClientFileNo, dbo_tblCaseNotes.CaseNotesDate DESC , dbo_tblCaseNotes.SupportStaffLkUpID;

Open in new window

0
Comment
Question by:BobRosas
  • 7
  • 6
13 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 33515425
Who knows what kind of SQL exchanges go on behind a recordset and a backend table.  But, your logic is client based, meaning the search logic is running on the client. In some circumstances the data provider is smart enough to "trump" your logic and do a server-side search, but not always.  I expect that is the difference.  Also, the type of recordset may may a difference.

My suggestion is to force a server-side filter by putting your search arguments in the where clause of your SQL rather than using findfirst.  
0
 

Author Comment

by:BobRosas
ID: 33515748
Thank you for your help.  What you suggest sounds like a good idea.  I tried adding in the arguments and I get the error...
Too few parameters, Expected 2
Do those go in with the "Set rs" statement some where?
I could really use some help with the syntax.   I've attached what I have so far.
Thanks

 strKey1 = "SELECT dbo_tblCaseNotes.ClientFileNo, dbo_tblClients.LastName, dbo_tblClients.FirstName, dbo_tblClients.MiddleName, "

              strKey1 = strKey1 & "dbo_tblCaseNotes.CaseNotesID, dbo_tblCaseNotes.CaseNotesDate, dbo_tblCaseNotes.SupportStaffLkUpID "

              strKey1 = strKey1 & "FROM (dbo_tblClients INNER JOIN dbo_tblCaseNotes ON dbo_tblClients.ClientFileNo = dbo_tblCaseNotes.ClientFileNo) "

              strKey1 = strKey1 & "LEFT JOIN dbo_tblLkUpSupportStaff ON dbo_tblCaseNotes.SupportStaffLkUpID = dbo_tblLkUpSupportStaff.SupportStaffLkUpID "

              strKey1 = strKey1 & "WHERE (((dbo_tblCaseNotes.ClientFileNo)=[Forms]![frmInterventionTime]![txtClientFileNoTmp]) AND "

              strKey1 = strKey1 & "((dbo_tblCaseNotes.CaseNotesID)=[Forms]![frmInterventionTime]![txtCaseNotesIDTmp])) "

              strKey1 = strKey1 & "ORDER BY dbo_tblCaseNotes.ClientFileNo, dbo_tblCaseNotes.CaseNotesDate DESC , dbo_tblCaseNotes.SupportStaffLkUpID; "

              Set rs = db.OpenRecordset(strKey1, dbOpenSnapshot)

Open in new window

0
 

Author Comment

by:BobRosas
ID: 33516316
I've attached some code that I've tried.  I'm not even sure if I'm going the right direction.
Now I get the error that "you must use the dbSeeChanges...", I guess I'm not using it correctly because that is the line of code where it stops working.
strKey1 = "SELECT dbo_tblCaseNotes.ClientFileNo, dbo_tblClients.LastName, dbo_tblClients.FirstName, dbo_tblClients.MiddleName, "

              strKey1 = strKey1 & "dbo_tblCaseNotes.CaseNotesID, dbo_tblCaseNotes.CaseNotesDate, dbo_tblCaseNotes.SupportStaffLkUpID "

              strKey1 = strKey1 & "FROM (dbo_tblClients INNER JOIN dbo_tblCaseNotes ON dbo_tblClients.ClientFileNo = dbo_tblCaseNotes.ClientFileNo) "

              strKey1 = strKey1 & "LEFT JOIN dbo_tblLkUpSupportStaff ON dbo_tblCaseNotes.SupportStaffLkUpID = dbo_tblLkUpSupportStaff.SupportStaffLkUpID "

              strKey1 = strKey1 & "WHERE (dbo_tblCaseNotes.ClientFileNo = txtClientFileNoTmp And "

              strKey1 = strKey1 & "CaseNotesID = txtCaseNotesIDTmp)"

              strKey1 = strKey1 & "ORDER BY dbo_tblCaseNotes.ClientFileNo, dbo_tblCaseNotes.CaseNotesDate DESC , dbo_tblCaseNotes.SupportStaffLkUpID; "

              Set qdf = db.CreateQueryDef("", strKey1)

                qdf.Parameters("[txtClientFileNoTmp]") = txtClientFileNoTmp

                qdf.Parameters("[txtCaseNotesIDTmp]") = txtCaseNotesIDTmp

                Set rs = qdf.OpenRecordset(, dbSeeChanges)  "ERROR HERE

Open in new window

0
 
LVL 42

Expert Comment

by:dqmq
ID: 33517936
Either method is OK once you work out the kinks. I'd tend to stick with the first method, but do the form substitutions in VBA, like this:

 strKey1 = "SELECT dbo_tblCaseNotes.ClientFileNo, dbo_tblClients.LastName, dbo_tblClients.FirstName, dbo_tblClients.MiddleName, "
              strKey1 = strKey1 & "dbo_tblCaseNotes.CaseNotesID, dbo_tblCaseNotes.CaseNotesDate, dbo_tblCaseNotes.SupportStaffLkUpID "
              strKey1 = strKey1 & "FROM (dbo_tblClients INNER JOIN dbo_tblCaseNotes ON dbo_tblClients.ClientFileNo = dbo_tblCaseNotes.ClientFileNo) "
              strKey1 = strKey1 & "LEFT JOIN dbo_tblLkUpSupportStaff ON dbo_tblCaseNotes.SupportStaffLkUpID = dbo_tblLkUpSupportStaff.SupportStaffLkUpID "

'>>>>note the changes here
              strKey1 = strKey1 & "WHERE (((dbo_tblCaseNotes.ClientFileNo)=" & [Forms]![frmInterventionTime]![txtClientFileNoTmp]) & " AND "
              strKey1 = strKey1 & "((dbo_tblCaseNotes.CaseNotesID)=" & [Forms]![frmInterventionTime]![txtCaseNotesIDTmp])) & " "
 
              strKey1 = strKey1 & "ORDER BY dbo_tblCaseNotes.ClientFileNo, dbo_tblCaseNotes.CaseNotesDate DESC , dbo_tblCaseNotes.SupportStaffLkUpID; "
0
 

Author Comment

by:BobRosas
ID: 33522404
I'd rather do it the way you suggested.  However I can't figure out why lines 4 & 5 won't compile.
See attached.  Would you help me with that?

strKey1.JPG
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33522558
What is the error message?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 42

Expert Comment

by:dqmq
ID: 33522693
Never mind, I see the problem:
The reference to the form field must stand by itself as a distinct term between the ampersands. The closing parens that follow it are wrong.  The need to be moved inside the quote marks that immediately follow.

wrong:
...[txtFileNoTmp]) & " AND"

right:
...[txtFileNoTmp] & ") AND"
0
 

Author Comment

by:BobRosas
ID: 33523182
Thank you!  Now it compiles but I still get an error.  Now that I have the string shouldn't it work in the findfirst?  I get the error "Syntax error in expression" on the rs.FindFirst line.  Here is how it looks so far...
Set db = CurrentDb()
Set rs = Me.RecordsetClone
strKey1 = "SELECT dbo_tblCaseNotes.ClientFileNo, dbo_tblClients.LastName, dbo_tblClients.FirstName, dbo_tblClients.MiddleName, "
              strKey1 = strKey1 & "dbo_tblCaseNotes.CaseNotesID, dbo_tblCaseNotes.CaseNotesDate, dbo_tblCaseNotes.SupportStaffLkUpID "
              strKey1 = strKey1 & "FROM (dbo_tblClients INNER JOIN dbo_tblCaseNotes ON dbo_tblClients.ClientFileNo = dbo_tblCaseNotes.ClientFileNo) "
              strKey1 = strKey1 & "LEFT JOIN dbo_tblLkUpSupportStaff ON dbo_tblCaseNotes.SupportStaffLkUpID = dbo_tblLkUpSupportStaff.SupportStaffLkUpID "
              strKey1 = strKey1 & "WHERE (((dbo_tblCaseNotes.ClientFileNo)=" & [Forms]![frmInterventionTime]![txtClientFileNoTmp] & ") AND "
              strKey1 = strKey1 & "((dbo_tblCaseNotes.CaseNotesID)=" & [Forms]![frmInterventionTime]![txtCaseNotesIDTmp] & " )"
              strKey1 = strKey1 & "ORDER BY dbo_tblCaseNotes.ClientFileNo, dbo_tblCaseNotes.CaseNotesDate DESC , dbo_tblCaseNotes.SupportStaffLkUpID; "
rs.FindFirst strKey1
0
 
LVL 42

Expert Comment

by:dqmq
ID: 33524451
You don't want strKey1 on the findfirst.

The whole point is to apply the filter cirteria on the server so you don't need to do it on the client.  As coded, the recordset will only contain records that already match your criteria, so just do "rs.Findfirst" to get the first one.
0
 

Author Comment

by:BobRosas
ID: 33524633
Thank you for your help.  I've increased your points becaused I really appreciate all you have done.  But I still can't get it to work.  If I use the string I posted above and then use
rs.FindFirst "[ClientFileNo]=" & dblClientFileNo & " And [CaseNotesID] = " & dblCaseNotesID
it runs but it also runs without the string.  I not setting the recordset to the string anywhere and I don't know how.  I thought it would become part of the findfirst code.
.
0
 
LVL 42

Accepted Solution

by:
dqmq earned 250 total points
ID: 33525804
>If I use the string I posted above and then use
rs.FindFirst "[ClientFileNo]=" & dblClientFileNo & " And [CaseNotesID] = " & dblCaseNotesID
it runs but it also runs without the string.

Because you have a where clause in the select statement that feeds the recordset, the server only sends back the rows that match the conditions of the where clause, i.e. the fields referenced on your form.  .Findfirst navigates through the recordset, appying the same conditions again, which is unnecessary.  

>I not setting the recordset to the string anywhere and I don't know how.  
Are you not doing this:

Set rs = db.OpenRecordset(strKey1, dbOpenSnapshot)


Perhaps it's time to re-post your latest code and clarify what is not working



0
 

Author Comment

by:BobRosas
ID: 33525876
No I did not have
Set rs = db.OpenRecordset(strKey1, dbOpenSnapshot)
I added it and now I get "Run-time error '3075':  Syntax error (missing operator) in query expression...."
on that line.
I've attached my current code.
Set db = CurrentDb()

strKey1 = "SELECT dbo_tblCaseNotes.ClientFileNo, dbo_tblClients.LastName, dbo_tblClients.FirstName, dbo_tblClients.MiddleName, "

              strKey1 = strKey1 & "dbo_tblCaseNotes.CaseNotesID, dbo_tblCaseNotes.CaseNotesDate, dbo_tblCaseNotes.SupportStaffLkUpID "

              strKey1 = strKey1 & "FROM (dbo_tblClients INNER JOIN dbo_tblCaseNotes ON dbo_tblClients.ClientFileNo = dbo_tblCaseNotes.ClientFileNo) "

              strKey1 = strKey1 & "LEFT JOIN dbo_tblLkUpSupportStaff ON dbo_tblCaseNotes.SupportStaffLkUpID = dbo_tblLkUpSupportStaff.SupportStaffLkUpID "

              strKey1 = strKey1 & "WHERE (((dbo_tblCaseNotes.ClientFileNo)=" & dblClientFileNo & ") AND "

              strKey1 = strKey1 & "((dbo_tblCaseNotes.CaseNotesID)=" & dblCaseNotesID & " )"

              strKey1 = strKey1 & "ORDER BY dbo_tblCaseNotes.ClientFileNo, dbo_tblCaseNotes.CaseNotesDate DESC , dbo_tblCaseNotes.SupportStaffLkUpID; "



              Set rs = db.OpenRecordset(strKey1, dbOpenSnapshot)  'syntax error in query expression

              rs.FindFirst "[ClientFileNo]=" & dblClientFileNo & " And [CaseNotesID] = " & dblCaseNotesID

Open in new window

0
 

Author Closing Comment

by:BobRosas
ID: 33667489
I know that becauise of your help I'm now much closer to the solution so I will award points and post a related question.  Thanks again for all your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Restrict result set 1 35
Help with SQL - TOP 10 by date and by group 13 34
AWE-based memory on 32-bit servers 1 23
create insert script based on records in a table 4 15
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now