On Dec 19, 1:42 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:

Tom van Stiphout wrote:
On Tue, 18 Dec 2007 06:31:01 -0800 (PST), lyle

<lyle.fairfi...@gmail.comwrote:
Number of Records: 520378

DLookup:(100 iterations) CAZN / 0.047 seconds

>DAO :(100 iterations) CAZN / 0.031 seconds

>ADO :(100 iterations) CAZN / 0.141 seconds

Thanks Lyle. I would not have guessed this outcome.

Is this a db downloadable from USDA? Do you have a link?

On top of that, Lyle used Workspaces(0)(0) to avoid the overhead of

CurrentDB collection refreshing. If DLookup() were compared to a DAO

Recordset using CurrentDB (something that many people are likely to do) then

I suspect that the recordset would lose what little speed advantage it has

in Lyle's code.

--

Rick Brandt, Microsoft Access MVP

Email (as appropriate) to...

RBrandt at Hunter dot com

Yes.

I tried that and the results for a hundred iterations of CurrentDB

and DLookup were always identical or very, very close:

Number of Records: 520378

<1>

***********************************

ADO:(100 iterations) CAZN / 0.172 seconds

CurrentDB:(100 iterations) CAZN / 0.047 seconds

DBengine:(100 iterations) CAZN / 0.031 seconds

DLookup:(100 iterations) CAZN / 0.047 seconds

***********************************

<2>

***********************************

ADO:(100 iterations) CAZN / 0.156 seconds

CurrentDB:(100 iterations) CAZN / 0.047 seconds

DBengine:(100 iterations) CAZN / 0.031 seconds

DLookup:(100 iterations) CAZN / 0.047 seconds

***********************************

<3>

***********************************

ADO:(100 iterations) CAZN / 0.141 seconds

CurrentDB:(100 iterations) CAZN / 0.047 seconds

DBengine:(100 iterations) CAZN / 0.031 seconds

DLookup:(100 iterations) CAZN / 0.047 seconds

***********************************

<4>

***********************************

ADO:(100 iterations) CAZN / 0.14 seconds

CurrentDB:(100 iterations) CAZN / 0.047 seconds

DBengine:(100 iterations) CAZN / 0.031 seconds

DLookup:(100 iterations) CAZN / 0.047 seconds

***********************************

<5>

***********************************

ADO:(100 iterations) CAZN / 0.157 seconds

CurrentDB:(100 iterations) CAZN / 0.046 seconds

DBengine:(100 iterations) CAZN / 0.032 seconds

DLookup:(100 iterations) CAZN / 0.047 seconds

***********************************

<6>

***********************************

ADO:(100 iterations) CAZN / 0.14 seconds

CurrentDB:(100 iterations) CAZN / 0.047 seconds

DBengine:(100 iterations) CAZN / 0.031 seconds

DLookup:(100 iterations) CAZN / 0.047 seconds

***********************************

<7>

***********************************

ADO:(100 iterations) CAZN / 0.141 seconds

CurrentDB:(100 iterations) CAZN / 0.047 seconds

DBengine:(100 iterations) CAZN / 0.031 seconds

DLookup:(100 iterations) CAZN / 0.047 seconds

***********************************

<8>

***********************************

ADO:(100 iterations) CAZN / 0.156 seconds

CurrentDB:(100 iterations) CAZN / 0.047 seconds

DBengine:(100 iterations) CAZN / 0.031 seconds

DLookup:(100 iterations) CAZN / 0.047 seconds

***********************************

<9>

***********************************

ADO:(100 iterations) CAZN / 0.141 seconds

CurrentDB:(100 iterations) CAZN / 0.047 seconds

DBengine:(100 iterations) CAZN / 0.031 seconds

DLookup:(100 iterations) CAZN / 0.047 seconds

***********************************

<10>

***********************************

ADO:(100 iterations) CAZN / 0.14 seconds

CurrentDB:(100 iterations) CAZN / 0.047 seconds

DBengine:(100 iterations) CAZN / 0.047 seconds

DLookup:(100 iterations) CAZN / 0.047 seconds

***********************************

No, I don't know why USDA uses strings for numeric values.