ADO from DAO

I have done the preliminary conversion from DAO to ADO on MSACCESS (350 megs
in 5 tables) and it is much slower in multi-user (3 workstations) on a Wndows
2000 server.

I tried adUseClient and it was worse

QUESTION: There are so many variables to adjust I am sure there is an
Optimized setup. Which properties should be changed to optimize or even
improve the speed.

Set DB = New ADODB.Connection
DB.Provider = "Microsoft.JET.OLEDB.4.0"
DB.Properties("Data Source") = DbLoc$ & "\" & DataBaseName$
'=====================================
'== Lock Rows with Locking Mode = 1
'=====================================
DB.Properties("Jet OLEDB:Database Locking Mode") = 1
' == DB.Properties("RecordSource") = adCmdTable
DB.CursorLocation = adUseServer
DB.Open

Set PatDATA = New ADODB.Recordset
PatDATA.Open "Visits", DB, adOpenstatic, adLockOptimistic

This has 36000 rows.

patdata.findfirst ("[PT_ID] = " & pt_NUM

This will take 30 - 40 seconds to pull up.

Similar code in DAO is up in seconds?


.