hi to the all! first sorry for my terrible english... Please tell me "10 gold rules" to speed up connection & recordset with ADO and Access mdb shared on lan dir...! In effect when i use vb project to piont the mdb in c:\... all work very fast... But very very slow when the project point to the mdb on lan dir. Note: The lan dir is in the my same farm server.
Unfortunately, the best way to speed up database access to an MDB file on a server, is to take it off the server... Access data files require a lot of disk I/O for record locking, and all this I/O and handshaking takes place over the network. In one case, I was inserting into an access database over the network; about 3 records a second was the best I could get. When I moved the file to the local drive, the insertion rate increased to 320 records per second. When retrieving records, it is important to make sure that all fields used in where clauses, or those used to join tables, are indexed. If any aren't, the entire table must be copied from the data file to the ADO client before the query can complete. The same is true for updates. Don't pull all the records from the file to the client, and don't retrieve ANY fields you don't need. (avoid Select *) Consider using asynchronous recordsets, even though it will require additional code. Use batch update recordsets (connect, load the recordset, disconnect, process, re-connect, update) If possible, one of the free versions of database servers (msde/sql express/postgresql) is far better than Access in these cases.
