Help Creating Small Table In Memory

Hi Gang

I have a large VB program that at one point does a lookup to a small
table (26 rows by 3 columns). With this table I have to do some
counting and retrieval of data. I'm finding that this slows the
program right down.

Is there any way I can take a copy of the table in memory and access it
there. Perhaps a cursor or something? Can someone help with a code
sample. Here's my code to do the lookup.

'-------------------------------------------------------------------------------------
lcFoundCADIMUOM = "False"
lcPrintUOMNotFound = ""

Set rsUOMCheck = db.OpenRecordset("SELECT cadimuom FROM CADIM_UOM WHERE
cadimuom = '" & lcUOM & "'")
If rsUOMCheck.RecordCount <> 0 Then
lcFoundCADIMUOM = "True"
End If

If lcFoundCADIMUOM = "False" Then
Set rsUOMCheck = db.OpenRecordset("SELECT cadimuom FROM CADIM_UOM
WHERE cadimuomconvert = '" & lcUOM & "'")
If rsUOMCheck.RecordCount <> 0 Then
lcFoundCADIMUOM = "True"
lcUOM = rsUOMCheck("cadimuom")
End If
End If

If lcFoundCADIMUOM = "False" Then
lcPrintUOMNotFound = lcPrintUOMNotFound & lcUOM & Chr(10)
End If
'-------------------------------------------------------------------------------------


Thanks
Andy

re: Help Creating Small Table In Memory

Per andy.mcvicker@siemens.com:[color=blue]
> Perhaps a cursor or something? Can someone help with a code
>sample. Here's my code to do the lookup.[/color]

Just in plain English, what are you trying to do?
--
PeteCresswell

re: Help Creating Small Table In Memory

Andy, to create a "table" in memory, of sorts, you can use
multi-dimensional arrays (or arrays of arrays, as someone pointed out
in a recent thread). Then you would read the table into your array and
access the information stored in memory. I would think that a Recordset
does basically the same thing, but I don't know - that's something the
smarter, more experienced folks on here could probably answer. However,
if the Recordset is reading data from the drive continually, it would
be much faster to use a copy in memory. I notice, though, that under
some circumstances you reopen the recordset to get a different
field...could you just get both fields in the original recordset?

Multidimensional arrays work like this:
Dim arrRay As String(26, 3)

arrRay(0,1)="Value of Row0, Col1"
arrRay(0,2)="Value of Row0, Col2"
arrRay(0,3)="Value of Row0, Col3"

arrRay(1,1)="Value of Row1, Col1"

Making sense?

There's even a Recordset method that turns the Recordset into a
multidimensional array for you. You can then use loops to iterate
through the array, like you would a table, and do your calculations.

re: Help Creating Small Table In Memory

Andy, ADO does disconnected recordsets: you can load the
recordset into memory, and use Find on it. If you want
to use ADO, perhaps you could repost with something
like "How to do ADO disconnected recordset?"

It's not clear from your code: do you call this function
repeatedly? If it is only called once, there is not
much you can do to make it faster. You still have to
load the data from disk into memory.

You might find that your code runs faster if you
only load the recordset once. For example if
CADIM_UOM is a table in db, you could open it as
a table object, and use SEEK

(air code)

Set rs = db.OpenRecordset("CADIM_UOM")
rs.index = "cadimuom"
rs.seek lcUOM

(david)

<andy.mcvicker@siemens.com> wrote in message
news:1136920159.892841.70320@o13g2000cwo.googlegro ups.com...[color=blue]
> Hi Gang
>
> I have a large VB program that at one point does a lookup to a small
> table (26 rows by 3 columns). With this table I have to do some
> counting and retrieval of data. I'm finding that this slows the
> program right down.
>
> Is there any way I can take a copy of the table in memory and access it
> there. Perhaps a cursor or something? Can someone help with a code
> sample. Here's my code to do the lookup.
>
> '-------------------------------------------------------------------------------------
> lcFoundCADIMUOM = "False"
> lcPrintUOMNotFound = ""
>
> Set rsUOMCheck = db.OpenRecordset("SELECT cadimuom FROM CADIM_UOM WHERE
> cadimuom = '" & lcUOM & "'")
> If rsUOMCheck.RecordCount <> 0 Then
> lcFoundCADIMUOM = "True"
> End If
>
> If lcFoundCADIMUOM = "False" Then
> Set rsUOMCheck = db.OpenRecordset("SELECT cadimuom FROM CADIM_UOM
> WHERE cadimuomconvert = '" & lcUOM & "'")
> If rsUOMCheck.RecordCount <> 0 Then
> lcFoundCADIMUOM = "True"
> lcUOM = rsUOMCheck("cadimuom")
> End If
> End If
>
> If lcFoundCADIMUOM = "False" Then
> lcPrintUOMNotFound = lcPrintUOMNotFound & lcUOM & Chr(10)
> End If
> '-------------------------------------------------------------------------------------
>
>
> Thanks
> Andy
>[/color]

re: Help Creating Small Table In Memory

andy.mcvicker@siemens.com wrote:[color=blue]
> Hi Gang
>
> I have a large VB program that at one point does a lookup to a small
> table (26 rows by 3 columns). With this table I have to do some
> counting and retrieval of data. I'm finding that this slows the
> program right down.
>
> Is there any way I can take a copy of the table in memory and access it
> there. Perhaps a cursor or something? Can someone help with a code
> sample. Here's my code to do the lookup.
>
> '-------------------------------------------------------------------------------------
> lcFoundCADIMUOM = "False"
> lcPrintUOMNotFound = ""
>
> Set rsUOMCheck = db.OpenRecordset("SELECT cadimuom FROM CADIM_UOM WHERE
> cadimuom = '" & lcUOM & "'")
> If rsUOMCheck.RecordCount <> 0 Then
> lcFoundCADIMUOM = "True"
> End If
>
> If lcFoundCADIMUOM = "False" Then
> Set rsUOMCheck = db.OpenRecordset("SELECT cadimuom FROM CADIM_UOM
> WHERE cadimuomconvert = '" & lcUOM & "'")
> If rsUOMCheck.RecordCount <> 0 Then
> lcFoundCADIMUOM = "True"
> lcUOM = rsUOMCheck("cadimuom")
> End If
> End If
>
> If lcFoundCADIMUOM = "False" Then
> lcPrintUOMNotFound = lcPrintUOMNotFound & lcUOM & Chr(10)
> End If
> '-------------------------------------------------------------------------------------[/color]


For raw speed my money is on:
Loading each individual set of fields into a delimited string and using
the instr() function to search for your target values.