UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Reading Faster Recordsets, Access 2013    
 
   
SomekindaVB
post Nov 15 2017, 07:52 PM
Post#1



Posts: 157
Joined: 15-December 16



Hi All,

So Im setting a variable as a recordset like so

CODE
Set rsCustomer = CurrentDb.OpenRecordset("Tbl_Customer", dbOpenSnapshot)


Which I am then later using to find data and then write back to the original table.

CODE
rsClient.FindFirst "[Customer Name]=" & Chr$(34) & strCustomerName & Chr$(34)
If Not rsCustomert.NoMatch Then
    lngCustomertID = rsCustomer![ID]
    'Update the hyperlink for this client
    CurrentDb.Execute "UPDATE Tbl_Customer SET Tbl_Customer .[Customer Field] = '" & strHyperlink & "' WHERE Tbl_Customer .ID=" & lngCustomerID
End if


So, i'm finding this method all a bit too slow. I thought the point of using a Dao.Recordset was to set the recordset in memory so that is could be read locally, rather than trying to perform the find over a network.

Am I doing it wrong? It's very slow. Is there a better way?

Cheers
Go to the top of the page
 
GroverParkGeorge
post Nov 15 2017, 08:12 PM
Post#2


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


You could try this instead, and compare the performance.

CODE
Set rsCustomer = CurrentDb.OpenRecordset("SELECT ID FROM Tbl_Customer WHERE [Customer Name] = " & Chr$(34) & strCustomerName & Chr$(34)), dbOpenSnapshot)

With rsCustomer
    If Not .BOF and Not .EOF Then
        lngCustomerID =  !ID
        CurrentDb.Execute "UPDATE Tbl_Customer SET Tbl_Customer .[Customer Field] = '" & strHyperlink & "' WHERE Tbl_Customer .ID=" & lngCustomerID
    End If
End With

--------------------
Go to the top of the page
 
GroverParkGeorge
post Nov 15 2017, 08:15 PM
Post#3


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


Or DLookup, which would be:

CODE
lngCustomerID = Nz(DLookup("ID", "tbl_Customer", "tbl_Customer.[Customer Name] = " & Chr$(34) & strCustomerName & Chr$(34)),0)
    If lngCustomerID  >0 Then
        CurrentDb.Execute "UPDATE Tbl_Customer SET Tbl_Customer .[Customer Field] = '" & strHyperlink & "' WHERE Tbl_Customer .ID=" & lngCustomerID
    End If
End With

--------------------
Go to the top of the page
 
theDBguy
post Nov 16 2017, 01:03 AM
Post#4


Access Wiki and Forums Moderator
Posts: 71,202
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I am having a problem understanding the following three lines:

...
Set rsCustomer...
...
rsClient.FindFirst...
...
If Not rsCustomert.NoMatch...
...

Unless it's just a typo, the three recordset variables you're using do not match.

Also, how is the value in strCustomerName assigned? if you can use a Combobox for the user to select the customer's name, you can then use the table's primary key with the .Seek method, which will use the table's index to make the search faster than using FindFirst.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
LPurvis
post Nov 16 2017, 05:20 AM
Post#5


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


Just to reiterate the above first...

Yep, are you using this recordset to search upon multiple values that are passed one after another?
If not then including the criteria in the recordset request as George has done is far, far more efficient.
(Even if you are using it for multiple searches, if you could establish those values at the start and limit the recordset to the set of values that you will be working upon, that is far more preferable.)

And yes, the possible typos or different recordset variable names is puzzling.

But also... why open a recordset, acquire the row you want, and then perform an update through a SQL update statement, as opposed to just editing the recordset you've already taken then time to open?

Cheers

--------------------
Go to the top of the page
 
cheekybuddha
post Nov 16 2017, 11:21 AM
Post#6


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


I second Leigh, why bother with a recordset at all?

Leigh mentions acquiring the record first - no need to even do that.
CODE
  Dim strSQL As String, blSuccess AS Boolean

  strSQL = "UPDATE tbl_Customer SET [Customer Field] = '" & strHyperlink & "' WHERE [Customer Name] = '" & strCustomerName & "';"
  With CurrentDb
    .Execute strSQL dbFailOnError
    blSuccess = .RecordsAffected > 0
  End With
  If not blSuccess Then MsgBox "Could not find customer: " & strCustomerName


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
SomekindaVB
post Nov 16 2017, 10:42 PM
Post#7



Posts: 157
Joined: 15-December 16



Thank you all. All of this is great advice. Apologies for the typos, which are caused by renaming tables to forum centric use. Particular thanks to cheekybuddha, whose advice I went with on this problem.

Plus I can use this exact same solution for various other scenarios in the same procedure that slows things down a little. Every little bit helps right? especially within a loop.

Cheers
Go to the top of the page
 
mklein
post Nov 17 2017, 01:05 AM
Post#8



Posts: 264
Joined: 7-August 12
From: BC, Canada


An even more robust an approach would be...
CODE
    Const SQL_INSERT As String = _
        "UPDATE tbl_Customer " & _
        "SET [Customer Field] = p0 " & _
        "WHERE [Customer Name] = p1 "

    With CurrentDb.CreateQueryDef("", SQL_INSERT)
        .Parameters(0) = strHyperlink
        .Parameters(1) = strCustomerName
        .Execute dbFailOnError
        If Not .RecordsAffected Then MsgBox "Could not find customer: " & strCustomerName
    End With
...in which there are no delimiters in the SQL. The QueryDef knows the types of the fields, and handles the delimiters automatically.

Mark

--------------------
| Mark Klein | Access 2010 | Windows 10 | Visual Studio 2013
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 01:41 AM