Full Version: Speeding up my TLookup (DLookup replacement)
UtterAccess Forums > Microsoft® Access > Access Forms
Ramdryve
I'm hoping you access Gurus out there can help me with a bit of my code. The following portion of code takes *forever* to run when the network is busy. I already replaced the Dlookups with the TLookups I found in the forums, but this is just *slow* like molasses in january. Ideas? Help? Please?
TIA!
CODE
  
Dim UID As Variant
Dim ACCLEVID As Variant
Dim ModifyAccount As Variant
Dim ModifyAccountStatus As Variant
Dim ModifyContacts As Variant
Dim ModifyEmployees As Variant
Dim ModifyInventory As Variant
Dim ModifyInvoices As Variant
Dim ModifyPrices As Variant
Dim ModifySystem As Variant
Dim GiveDiscount As Variant
Dim Developer As Variant
UID = Tlookup("[EmployeeID]", "EmployeeInfo", "UName = '" & Forms!Holder!UName & "'")
ACCLEVID = Tlookup("[ACCLEVID]", "Acclevels", "UserID = " & UID)
ModifyAccount = Tlookup("[ModifyAccount]", "Acclevels", "UserID = " & UID)
ModifyAccountStatus = Tlookup("[ModifyAccountStatus]", "Acclevels", "UserID = " & UID)
ModifyContacts = Tlookup("[ModifyContacts]", "Acclevels", "UserID = " & UID)
ModifyEmployees = Tlookup("[ModifyEmployees]", "Acclevels", "UserID = " & UID)
ModifyInventory = Tlookup("[ModifyInventory]", "Acclevels", "UserID = " & UID)
ModifyInvoices = Tlookup("[ModifyInvoices]", "Acclevels", "UserID = " & UID)
ModifyPrices = Tlookup("[ModifyPrices]", "Acclevels", "UserID = " & UID)
ModifySystem = Tlookup("[ModifySystem]", "Acclevels", "UserID = " & UID)
Developer = Tlookup("[Developer]", "Acclevels", "UserID = " & UID)
GiveDiscount = Tlookup("[GiveDiscount]", "Acclevels", "UserID = " & UID)
Me.UserID = UID
Me.ACCLEVID = ACCLEVID
Me.ModifyAccount = ModifyAccount
Me.ModifyAccountStatus = ModifyAccountStatus
Me.ModifyContacts = ModifyContacts
Me.ModifyEmployees = ModifyEmployees
Me.ModifyInventory = ModifyInventory
Me.ModifyInvoices = ModifyInvoices
Me.ModifyPrices = ModifyPrices
Me.ModifySystem = ModifySystem
Me.Developer = Developer
Me.GiveDiscount = GiveDiscount
Tomolena
I'm not familiar with Tlookup but if it works anything like Dlookup, that will explain why things are going so slow. Domain aggregate functions are SLOOOOOW!
You would get much faster results by declaring two recordset objects (one for EmployeeInfo and one for Acclevels). Give them Form, or Module level scope...that way, the data only has to be pulled from the server once and it will remain there until you cose the form.
Then, all you have to do is search the instantiated recordset each time you want to assign a value to a variable. To avoid having your code look like the long edition of War and Peace, you can modularize(?) things by creating a new function (Xlookup) to take the place of Tlookup to search the standing recordsets.
Ramdryve
I am looking for a reference on recordsets, can you point me anywhere?
Google is GOD!!!
havent actually used them (Recordsets) before as yet.
TIA!
RuralGuy
How about eliminating most of the TLookup's and the redundant variables?
*** UNTESTED ***
CODE
Me.UserID = tLookup("[EmployeeID]", "EmployeeInfo", "UName = '" & Forms!Holder!UName & "'")
If Not IsNull(Me.UserID) Then
   Dim MyRs As DAO.Recordset
   Dim MySQL As String
  
   MySQL = "SELECT * FROM Acclevels WHERE [UserID] = " & Me.UserID
   Set MyRs = CurrentDb().OpenRecordset(MySQL, dbOpenSnapshot, dbReadOnly)
  
   If Not MyRs.EOF Then
      Me.ACCLEVID = MyRs![ACCLEVID]
      Me.ModifyAccount = MyRs![ModifyAccount]
      Me.ModifyAccountStatus = MyRs![ModifyAccountStatus]
      Me.ModifyContacts = MyRs![ModifyContacts]
      Me.ModifyEmployees = MyRs![ModifyEmployees]
      Me.ModifyInventory = MyRs![ModifyInventory]
      Me.ModifyInvoices = MyRs![ModifyInvoices]
      Me.ModifyPrices = MyRs![ModifyPrices]
      Me.ModifySystem = MyRs![ModifySystem]
      Me.Developer = MyRs![Developer]
      Me.GiveDiscount = MyRs![GiveDiscount]
   End If
   MyRs.Close
   Set MyRs = Nothing
End If
Ramdryve
IT WORKS! thank you. Might I ask you a small favour- Please, would you explain what the code below does? then I will understand how to do it for myself in the future.
IA!
CODE
'obviously pulls the UID
Me.UserID = tLookup("[EmployeeID]", "EmployeeInfo", "UName = '" & Forms!Holder!UName & "'")
' If it isnt null
If Not IsNull(Me.UserID) Then  
' DAO.Recordset?????  isnt this a variable?
Dim MyRs As DAO.Recordset
' create a string variable  
Dim MySQL As String      
' totally lost here. obviously, pulling the data, but doesnt it need brackets and such?
MySQL = "SELECT * FROM Acclevels WHERE [UserID] = " & Me.UserID  
' what does Set do? a command used by dao.recordset?
Set MyRs = CurrentDb().OpenRecordset(MySQL, dbOpenSnapshot, dbReadOnly)      
' If it isnt at the end of the table, ie end of file
If Not MyRs.EOF Then      
' start setting the vars
Me.ACCLEVID = MyRs![ACCLEVID]      
Me.ModifyAccount = MyRs![ModifyAccount]      
Me.ModifyAccountStatus = MyRs![ModifyAccountStatus]      
Me.ModifyContacts = MyRs![ModifyContacts]      
Me.ModifyEmployees = MyRs![ModifyEmployees]      
Me.ModifyInventory = MyRs![ModifyInventory]      
Me.ModifyInvoices = MyRs![ModifyInvoices]      
Me.ModifyPrices = MyRs![ModifyPrices]      
Me.ModifySystem = MyRs![ModifySystem]      
Me.Developer = MyRs![Developer]      
Me.GiveDiscount = MyRs![GiveDiscount]  
' end the if
End If  
' Closing the recordset?
MyRs.Close  
' again, set? why = Nothing?
Set MyRs = Nothing
'end the first if
End If
'end of the code. <G>
Tomolena
It's testing for a null UserID and if it IS null, it then creates a new recordset based on a SELECT query derived from an SQL string (MySQL). It appears that the query results in a single row from which all the variables (or controls) are given values.
If you want to see what the recordset looks like, try putting a Debug.Print MySQL statement right after the "MySQL =" statement. Open a new query and paste the string you see in the immediate window into the SQL view of the query. Then run the query. This should give you a little insight into the "mechanics" of the code.

If this is the code that TLookup is based upon then that explains why things were moving so slow. Its opening a new recordset every time the function is called. This was happening 12 times for each record that was processed. And since the code was manipulating strings (MySQL) that added further to the overhead.
BrianS
This is a prevalent attitude among a lot of people. Domain aggregate function really only become slow when used repeatedly during a loop or especially in the context of a query. Usually Domain aggregate function are a prefectly valid and preferrable way to access information when used in singular operations. They have actually proven faster than home grown replacements in many circumstances.
NoahP
You can find the tLookup function here. It, along with replacements for all the other Domain Aggregates can be found there.
omain Aggregate functions are better than they used to be, but, I still try to use them as little as possible. In my opinion, there's usually a better way. The 'handiness' of the Domain Aggregates makes it really easy to just drop one in whenever.
ScottGem
I'm just curious about something. Why not just use a bound form? You could use a bound form with a Search combo that would eliminate the need to fill the controls from a recordset.
While there are some valid reasons to use unbound forms, I'm not sure this is the case with your needs.
Tomolena
When I said that they are slow, that is exactly what I was referring to...repeated use during loops. They're nothing more than adhoc queries that make it convenient for easy extraction of data. They are to be used very sparingly in queries that produce large recordsets, and in operations like the one the OP was trying to speed up.
Ramdryve
Thanks, guys. frown.gif Still a little confuzzled about the dao.recordset, but the code is moving along swimmingly now.
we you guys yet another one!!!!!!!!
Thanks.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.