Slow ADO Query over Access with VB6 over a LAN

[frankge973]

My program uses MS Access as DB and Visual Basic 6. I've experienced an issue loading a recordset with about 300000 records, some fields indexed, when I place a SELECT SELECT * from Materials m INNER JOIN Quantity s ON m.code=s.materialcode ORDER BY code

in a ADO recordset with the following configurations:

Provider: Jet;OLEDB CursorType: adOpenForwardOnly LockType: adLockReadOnly CursorLocation: adUseClient

I place the query and pass to a datagrid, I've not to edit or modify the records ***
Strangely, if I use on the same machine where I've the .mdb file is fast, from other clients is slow (approx. 15 seconds).

What Can I change? Thanks

[AndreRet]

Firstly, Access over a network with multiple (max 5) users can slow down to a crawl, hence the use of MySql or MS Sql, which has a stacking feature which will run all queries as they arrive, speeding up data transfer considerably.

Another culprit might be the LAN connection transfer speed, which is hardware and might cost a few bucks to sort.:)

Your problem does not lie with the code or the sql statements. Its mainly the database or network hardware that is your culprit here.

[BitBlt]

Not sure if this will help, but have you tried creating a separate MDB file on the local machine and linking to the tables in the network MDB? Then you can write your VB to go after the local MDB and let the local jet engine deal with the latency. Not a great solution, because that requires you to distribute a separate MDB with your app, plus you have to go into mSysObjects and fix the link to use UNC names instead of drive letter references (big pain). Alternative is to use DSN-less ODBC or OLEDB connection, populate a local in-memory recordset object and only load your datagrid on open. More memory intensive but should run faster. Last possibility is instead of using "Select *..." explicitly name the columns in the select. If there are any Memo columns, see if you can get away with leaving them out. Memo columns are notorious for causing long latency over networks. Hope this helps.

[frankge973]

Thank you very much for your help.
1) solution to duplicate mdb DB, too complex and expensive
2) I use this provider string:
Provider=Microsoft.Jet.OLEDB.4.0
I populate a local memory ADO RS and I use datagrid.datasource=RS only when I click on Search button
3) I'll try to specify only the required fields but.... ahi ahi.... I've memo fields (description, long text field) which I must use....and display on datagrid....