[AccessD] Major Performance Issue! + Code + Time

Hi Sad

I don't seem to find the CI code so it's dificult to suggest you
something.

Altough 1 remark that I experianced in some heavy testing I done in
Access 2K concerning the use of DAO or ADO. I see that u are using
Access 2K with an MDB backend.

First of all, use of ADO should be (future wise) be stimulated over DAO.
However.
In a pretty heavy app I have I had procedures that took 5 to 30 minutes
to perform, and therefor I done some heavy testing with ADO/DAO to
improve speed on these.

My conclusings where that when using MSA2K and the jet database (MDB
file) the use of ADO should be avoided or even compleetly ignored in
time consuming or itteration functions.
ADO is, when using, JET, much much slower than DAO in almost every case.
I noticed that some simple function are by average 2 to 7 times slower.
In some case they where 15 to 30 times slower.

So using ADO with Jet is fine when you will not use the function in an
itteration.
For example your function GetMaxDate(). If this is used in an itteration
x thousend of times, you will get high speed advantages when recoding
the function with DAO or even a simple dlookup or dmax.
Also putting indexes on the filter fiels of an sql string in a function
used in itteration is ofcourse also important but this is beside the
ADO/DAO issue.

Please note for the record that the use of ADO should be motivated
(certainly with SQL server as a backend) but ADO does have long delays
compared with DAO in at least Access 2000.
Did no testing in 2K2 or 2K2, so I don't know for these, but I expect it
to be the same.
I decided for my self not to use ADO as long as I work with JET (mdb
backend files) (except when using ASP/ASPX)



Erwin