Custom DLookup Array - any ideas for more efficiency? 5

[rubbernilly]

I wrote this code because I often found myself needing to return multiple items from a particular lookup, but the syntax was very often the same and I got tired of dimensioning DAO.Recordsets in every procedure.

vDLookup returns a variant containing an array based on the sort of criteria and syntax the normal DLookup would use to return the first match.

I'm wondering if there is some way to make it more efficient or more widely-applicable so that it would be useful for more people.

Here is the code:

Public Function vDLookup(Expr As String, Optional Domain As String, Optional Criteria As String) As Variant
Dim vReturns() As String
Dim rs As DAO.Recordset
Dim a As Long
On Error GoTo Err_vDLookup

If IsMissing(Domain) Or Domain = "" Then Domain = "ProgramData" [green]'a default table for my DB configs[/green]
Domain = "SELECT [" & Expr & "] FROM [" & Domain & "]"
If IsMissing(Criteria) = False Or Criteria <> "" Then Domain = Domain & " WHERE " & Criteria

Set rs = CurrentDb.OpenRecordset(Domain)

If rs.EOF Then
    GoTo Exit_vDLookup
End If
rs.MoveLast
rs.MoveFirst
For a = 0 To rs.RecordCount - 1
    ReDim Preserve vReturns(a)
    vReturns(a) = rs(Expr)
    rs.MoveNext
Next a

Exit_vDLookup:
    Set rs = Nothing
    vDLookup = vReturns
    Exit Function

Err_vDLookup:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_vDLookup

End Function

So, the usage would be...

Let's say you needed to return all of a Customer's managers who are setup in your DB with the authority to order. The table name is "CustAgents", the field is "ManagerName."

dim vManagers() as string, a as long

vManagers = vDLookup("ManagerName","CustAgents","CustomerID = " & lngCustID)

[green]'you could use it as the RowSource of a Listbox with Join()[/green]

me.lstCustAgents.RowSource = Join(vManagers)

[green]'or you can loop through it to process code[/green]

if len(join(vManagers)) >0 then
   for a = 0 to UBound(vManagers)
      [green]'do code[/green]
      debug.print vManagers(a)
   Next a
end if

So, is there a way to make this better? I've thought about declaring an associated Type... off the top of my head:

Public Type vDLookupReturn
   Field1 as Variant
   Field2 as Variant
   Field3 as Variant
   Field4 as Variant
   Field5 as Variant
End Type

Since vDLookup relies on redimensioning the array, and that can only be done for the last dimension of an array, this would be a workaround for getting up to (in this case) 5 fields involved in the return. What would be the changes to vDLookup? Hmm, I know that I'd have to dimension vReturns() as vDLookupReturn so that it would get the properties. I could require that Expr be of DLookupReturn Type, so that five fields could be assigned to the object and then those fields could be read into the SQL for the recordset.

What else? I'd appreciate any help to take this to the next level!

 

[rubbernilly]

Sorry, that Join statement should be...

me.lstCustAgents.RowSource = Join(vManagers,";")

 

[RoyVidar]

Note - I've done no testing, just commenting ;-)

You are fully populating the recordset, to use the .RecordCount for your looping, why not use the .RecordCountto dimension the array? Cause the redim preserve is a resource drag!

Perhaps test with redim preserve doing a do while not rs.eof (without any prior movelast/first) and compare to movelast/first dimension the array and loop without redim?

[tt]Dim lngRC as long
With rs
lngRC = .RecordCount
Redim vReturns(lngRC-1)
For a = 0 To lngRc - 1
vReturns(a) = .fields(Expr).value
rs.MoveNext
Next a
end with[/tt]

[tt]With rs
do while not .eof
ReDim Preserve vReturns(a)
vReturns(a) = .fields(Expr).value
a = a + 1
.MoveNext
loop
end with[/tt]

I don't quite understand the implicit casting - in the sample you declare the array as string, the same as you're working with within the function, but you're returning a variant. I think I'd considere returning a string array (which won't work for 97 and previous versions, though), then if no occurrences, just assign split(""), which will make Ubound return -1 (re the link I gave here thread705-1136873)

IsMissing - I thought this only applied to variants. To test for contents in strings, Getz et all recommends

[tt]if len(mystring)>0 then ' or just
if len(mystring) then[/tt]

I don't do much DAO, but isn't there a way of explicitly state what kind of recordset you want (forwardonly/snapshot)?

Roy-Vidar

 

[rubbernilly]

Thanks for the response, Roy...

I've made the following changes:

rs is opened as a snapshot
vReturns() is only redimmed once, based on the recordcount property
IsMissing() is now... missing. I test using the Len() function

I'm testing now the ability to use a user-defined type to get multiple fields from the function. I have a couple of questions regarding the setup...

Here is the code:

Public Type vdlrField
    sName As String
    vValue As Variant
End Type
Public Type vDLookupReturn
    Fields(0 To 4) As vdlrField
End Type

Public Function vDLookup_NEW(Expr As vDLookupReturn, Optional Domain As String, Optional Criteria As String) As vDLookupReturn
Dim sFields As String
Dim vReturns() As vDLookupReturn
Dim rs As DAO.Recordset
Dim a As Long, b As Integer
On Error GoTo Err_vDLookup

If Len(Domain) = 0 Then Domain = "ProgramData"

For b = 0 To UBound(Expr.Fields)
    If Expr.Fields(b).sName = "" Then Exit For
    sFields = sFields & "[" & Expr.Fields(b).sName & "],"
Next b
sFields = Left(sFields, Len(sFields) - 1)

Domain = "SELECT " & sFields & " FROM [" & Domain & "]"
If Len(Criteria) > 0 Then Domain = Domain & " WHERE " & Criteria

Set rs = CurrentDb.OpenRecordset(Domain, dbOpenSnapshot)

If rs.EOF Then
    GoTo Exit_vDLookup
End If
rs.MoveLast
rs.MoveFirst

ReDim Preserve vReturns(rs.RecordCount - 1)

For a = 0 To rs.RecordCount - 1
    For b = 0 To UBound(Expr.Fields)
        If Expr.Fields(b).sName = "" Then Exit For
        vReturns(a).Fields(b).sName = Expr.Fields(b).sName
        vReturns(a).Fields(b).vValue = rs(Expr.Fields(b).sName)
    Next b
    rs.MoveNext
Next a

Exit_vDLookup:
    Set rs = Nothing
    vDLookup_NEW = vReturns
    Exit Function

Err_vDLookup:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_vDLookup

End Function

Doing this, I got an error: "Compile Error: Can't Assign to Array" when I did the following test...

Dim rTest As vDLookupReturn
Dim rReturn() As vDLookupReturn

rTest.Fields(0).sName = "Value"
rTest.Fields(1).sName = "Argument1"
rTest.Fields(2).sName = "Argument2"
rTest.Fields(3).sName = "Argument3"
rTest.Fields(4).sName = "Argument4"

[highlight]rReturn() = [/highlight]vDLookup_NEW(rTest, , "Type = 'Dynamic Query'")

So I'm not sure what I'm doing wrong there. rReturns should be the same type as the vDLookup function, and both are arrays.

How am I handling the arrays/types incorrectly?

 

[PHV]

First, to compile properly, define your function like this:
Public Function vDLookup_NEW(Expr As vDLookupReturn, Optional Domain As String, Optional Criteria As String) As vDLookupReturn[highlight]()[/highlight]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rubbernilly]

That was the trick, PHV! It seems so intuitive now that I see it, but sometimes until you see it... you just don't see it.

Thank you both to PHV and Roy who helped with this code!

Here is the end result...

Public Type vdlrField
    sName As String
    vValue As Variant
End Type
Public Type vDLookupReturn
    Fields(0 To 4) As vdlrField
End Type

Public Function vDLookup(Expr As vDLookupReturn, Optional Domain As String, Optional Criteria As String) As vDLookupReturn()
Dim sFields As String
Dim vReturns() As vDLookupReturn
Dim rs As DAO.Recordset
Dim a As Long, b As Integer
On Error GoTo Err_vDLookup

If Len(Domain) = 0 Then Domain = "ProgramData"

For b = 0 To UBound(Expr.Fields)
    If Expr.Fields(b).sName = "" Then Exit For
    sFields = sFields & "[" & Expr.Fields(b).sName & "],"
Next b
sFields = Left(sFields, Len(sFields) - 1)

Domain = "SELECT " & sFields & " FROM [" & Domain & "]"
If Len(Criteria) > 0 Then Domain = Domain & " WHERE " & Criteria

Set rs = CurrentDb.OpenRecordset(Domain, dbOpenSnapshot)

If rs.EOF Then
    GoTo Exit_vDLookup
End If
rs.MoveLast
rs.MoveFirst

ReDim Preserve vReturns(rs.RecordCount - 1)

For a = 0 To rs.RecordCount - 1
    For b = 0 To UBound(Expr.Fields)
        If Expr.Fields(b).sName = "" Then Exit For
        vReturns(a).Fields(b).sName = Expr.Fields(b).sName
        vReturns(a).Fields(b).vValue = rs(Expr.Fields(b).sName)
    Next b
    rs.MoveNext
Next a

Exit_vDLookup:
    Set rs = Nothing
    vDLookup = vReturns
    Exit Function

Err_vDLookup:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_vDLookup

End Function

You can use this if you want to return more than one field from more than one record at a time. If you want to return more than the 5 fields allotted, change the upper bound of the Fields() statement in the Type declaration. vDLookup should work with however many you have declared there, since it works with the UBound() of that collection.

Thanks again, guys!

 

[SiberBob]

OK,

So can you help me with how to use this?

I have the code pasted into a modVDLookup module, and want "SELECT [County] FROM [MissouriCounties] WHERE [Troop] LIKE 'D'" to be read into an strCounties() array dimensioned to the Dcount of the Counties in Troop D.

I just don't know how you are filling the arguments of your function. IE: is EXPR the field name, or the table name, or something else. I assume Domain is the table name right? Criteria: would that be an sql statement or some other type?

And is vDLookup an array in itself that I just set strCounties() = vDLookup(arguments...) to?

I realize that if vDLookup is an array, I wouldn't have to copy it to my own array, but while I'm taking baby steps it may help me to visualize what's going on...

this is my first real attempt to use anything ADO/DAO so bear with me...

Thanks!

 

[rubbernilly]

Let me explain it two ways.

You can do what you need to do with a simpler procedure more like what I originally posted in this thread (before I added the user defined type of vdLookupReturn).

Once you understand how that code works, it will be easier to understand what is going on in the longer, more versatile code that I last posted.

OK, so here is the basic version with the suggestions that RoyVidar made:

Public Function vDLookup(Expr As String, Optional Domain As String, Optional Criteria As String) As Variant
Dim vReturns() As String
Dim rs As DAO.Recordset
Dim a As Long
On Error GoTo Err_vDLookup

If Domain = "" Then Domain = "ProgramData"
Domain = "SELECT [" & Expr & "] FROM [" & Domain & "]"
If Criteria <> "" Then Domain = Domain & " WHERE " & Criteria

Set rs = CurrentDb.OpenRecordset(Domain)

If rs.EOF Then
  GoTo Exit_vDLookup
End If
rs.MoveLast
rs.MoveFirst

ReDim Preserve vReturns(rs.RecordCount)

For a = 0 To rs.RecordCount - 1
  vReturns(a) = rs(Expr)
  rs.MoveNext
Next a

Exit_vDLookup:
  Set rs = Nothing
  vDLookup = vReturns
  Exit Function

Err_vDLookup:
  MsgBox Err.Number & ": " & Err.Description
  Resume Exit_vDLookup

End Function

The usage of this function should resemble the usage of the DLookup function.

[blue]Expr as String[/blue]
This represents the Field you want to return (ie, "County")

[blue]Optional Domain as String[/blue]
This represents the table/query you are going to search in (ie, "MissouriCounties"). Note that if this argument is left blank, the default of "ProgramData" is assumed. ProgramData is a table in my databases which contains configuration-type information about the database, and is most likely the source of the information I am after. You can change this defaulting in the code to point at a different table for yourself.

[blue]Optional Criteria as String[/blue]
Just as with the DLookup function, here the Criteria is a "WHERE" SQL statement without the word "WHERE" (ie, "[Troop] Like 'D'").

[red]USAGE[/red]
So, your usage would be:

dim sTroop() as string
dim a as integer
sTroop = vDLookup("County", "MissouriCounties", "[Troop] Like 'D'")

That is the way you would implement the idea with the basic code. You don't need the DCount to dimension the array, since it can be dynamically set to the size of the return from the function. Since the function is already doing the work of ascertaining how big the array should be, it would be redundant to execute a DCount.

***

Now, the second explanation has to do with the more complicated code. There, you are not returning only one field. You are actually returning up to 5 fields (based on the "Fields(0 to 4)" declaration). You can change that number for more or less fields.

Start at the beginning:

Public Type vdlrField
    sName As String
    vValue As Variant
End Type

This is the basic field you will be returning. Every field has a name (sName), as in "County", and a value (vValue), as in "St. Louis". I'm guessing that there is actually a St. Louis county in Mizzou.

How many fields do you want to return from a single record is handled by the next piece:

Public Type vDLookupReturn
    Fields(0 To 4) As vdlrField
End Type

Here we are allowing up to 5 fields to be returned from one record. Since the fields are declared as vdlrField type, they have a sName and vValue attribute:

[maroon]Fields(0).sName = "County"
Fields(0).vValue = "St. Louis"[/maroon]

Properly, the Fields() property would be attached to the variable that was declared as the vDLookupReturn type.

[maroon]Dim MyLookup as vDLookupReturn
MyLookup.Fields(0).sName = "County"
MyLookup.Fields(0).vValue = "St. Louis"[/maroon]

[red]USAGE[/red]
So, let's say you wanted to return the same information using the more complicated piece of code (Counties where the Troop field is like 'D'). Your usage would be:

dim vdTroop() as vDLookupReturn
dim TroopInfo as vdLookupReturn
dim a as integer

TroopInfo.Fields(0).sName = "County"

vdTroop = vDLookup(TroopInfo, "MissouriCounties", "[Troop] Like 'D'")

'this time, to get at the info, you have to use the vValue property of the first "Fields" of any particular entry in the vdTroop() array

for a = 0 to UBound(vdTroop)
  debug.print vdTroop(a).Fields(0).vValue
next a

This is a bit of overkill for just returning one field, but the functionality is there so that you can return multiple fields. If you wanted to return not only the County field but the TroopLeaderName field, for instance, you just have to write "TroopLeaderName" to the sName property of Fields(1) in the TroopInfo variable before you pass TroopInfo to the vDLookup function. To return the name of the Troop leader, just access the appropriate "Fields" of the returned array:

[maroon] debug.print vdTroop(a).Fields(1).vValue[/maroon]

***

That is the logic and usage behind the code. I hope it made sense, and I hope there are no bugs left in my suggested implementation (unable to test at the moment). Hopefully this will give you enough info to decipher what the DAO code is doing in the function. If you have any questions, post back and I will try to help.

 

[Zion7]

Rubbernilly, that is nice code.
What was wrong with GetString() or GetRows()?

 

[MichaelRed]

I'm sure that I'm missing the point. DLookup (and the other similar "functions") are just wrappers for generating an SQL statement. Doing a number of lookups is just expanding the use of the wrapper. Why not cut out the middle man and just write the SQL statement and use the runsql or execute sql (to a temp recordset)? In the end, probably easier than all that code and bypass the middle man (wrapper).




MichaelRed

 

[rubbernilly]

Thanks for that, Zion!

This is a solution that grew up out of a very specific need/desire, and then was applied more universally to a wider set of uses. As I said above, I have a table of configuration information for the database, unlinked to any data tables. DLookup provided a quick way to get a particular piece of information out of the table, but I grew tired of writing SQL and DAO for the same information over and over if the information that I needed was a collection of entries in the table.

I took that code, generalized it, and wrote it out to its own function to consolidate the SQL and to simplify my calling procedures.

And that, Michael, is why I use the wrapper. Basic programming philosophy: repetitive code should go in its own procedure so that (1)calling procedures are kept clean, and (2) any changes to the code can occur in one place.

As for why not GetRows(), I didn't use that in place of the procedure because, as I mentioned, I wanted all of the DAO consolidated. I suppose, though, that I could use it in the procedure, to get the data into the array...

 

[Zion7]

Yes rubbernilly, It would be just to avoid the loop, that
GetRows, may expedite the procedure.
...At least in code lines, anyway?

One Comment, is it prudent to have the function value
returned, at the exit point?
If an error occurs, you may get an incorrect return.

 

[rubbernilly]

Good point, the return should move to right before the Exit_ label. Any call to this function would have to test for Ubound() of the array, or Len(Join())>0, to be sure that any information filled, so that would be better. Good catch!

 

[MichaelRed]

When you are 'married' to the soloution, suggestions will not change you opinion or approach, so I have no interest in further discourse. You DID ask for suggestions to speed up the process. My own experience provided dramatic speed up of functions when dropping the lookup functions in favor of the embedded generation of the sql strings and either executing them (action queries) or generating temporary recordsets for use as simple value pools.

Writing code to fufill the wrapper requirements is probably at least as involved as the equivalent code to generate the Sql statement. Providing the same (or at least similar) variable substution in the (sql) strings is not substantially different than inserting the same (or similar) information in the wrapper, it just avoids the overhead of the wrapper.



MichaelRed

 

[rubbernilly]

Michael,

Just an observation, but every post of yours that I have read has been contrary in nature. So it doesn't surprise me that you are not interested in further discourse, nor does it sadden me that you are uninterested.

I am not 'married' to the solution except that the thread was intended to present a tool that people could make use of. To say that it might be faster to handle code without making use of the tool does nothing for a reader of the thread except to say, 'do it the way you always do it.' Not that there is anything wrong with that, but it does discount the many situations where a tool like this might be helpful.

As in my circumstance... where application development is a rushed process, and updates come often. Development is most rapid by using pre-written wrapped up procedures like this. Also, any updates then happen in one location rather than fifty. In that case it works quite well for me. I'm sure that there are other cases when something like this would work for others, too.

You are more than welcome to do things your way. I may do things a similar way too, depending on the circumstances and the requirements.

But, to take your argument, if you are married to your contrariness, then I doubt anyone will ever have a good idea in your opinion. Discourse, as you say, is probably a bad idea at that point.

 

[Zion7]

rubbernilly, if I may interject.
I think Mike's "indignation",
is simply aroused by the fact that, his suggestion to avoid using a wrapper and a dAO.recordset, was ignored,
where it might clearly have lowered the overhead, on the function.

I personally, am not aware how an execute command,
can get you the multiple values you want
(but if possible, would love to know),
and can't see how opening a recordset, can be avoided?
(may be, due to my lack of experience).

Excuse me for belabouring the issue but,
I am intrigued how, inadvertantly, this thread has
brought up some good "best practices" issues.

Either way rubbernilly, it's a useful function with some clever techniques. A lot to learn from.

 

[rubbernilly]

I guess my reading of what Michael was suggesting was that the whole idea of wrapping up the DAO recordset in a different function was a bunk idea to start with. It wasn't reducing the overhead of the function, it was doing away with the function altogether. If I'm wrong about that reading, then I certainly apologize to Michael.

If I am right in my reading, then like I said, no problem there, that's the way I default to doing things - inline with the fastest code possible. This code arose to answer a specific set of needs and can handle various requests for people who might not see the way to code this to begin with.

BTW, I also do not see how an execute command can retrieve the values that you need, but if possible that would be a powerful tool, indeed!

 

[RoyVidar]

For the fun, not an execute on the command object, but on a connection with the above generated string (using the vReturns array of string). The disadvantage is the need for the split function to retreive the values. But the speed degradation of that, vs using a variant/looping a recordset ....?

[tt]vReturns = Split(currentproject.connection.execute(Domain,, _
adcmdtext).getstring(adclipstring, ,";", ";"), ";")[/tt]

Need to trap for no records of course ... but is this in the direction?

Roy-Vidar

 

[Zion7]

Thank-you Roy, that definately sated my curiosity,
...not to mention, increased my knowledge!

And sorry rubbernilly,
I didn't mean to imply your retort to Michael was inappropriate ( even though, it may have sounded that way).
Michael appears to be an extremely competent programmer,
but yes, as you said, more often than not,
he likes to play devil's advocate!