CODEWITHSUNDEEP
ms-accessvb6dao
Malcolm McCaffery
Malcolm McCaffery

Reputation: 2576

Optimize opening Access Database over High Latency Network via DAO in VB6

Inherited application which is being transformed over time to a new platform. However, in the meantime looking for simple fixes to improve performance over high latency connections. In some cases operations that take seconds can take 10 minutes on remote sites.

After analysis the major problem is the same recordset is opened hundreds of times with DAO, each attempt to open record set is initiating a call back to fileshare hosting MDB file. Due to the high latency of connection these multiple calls are expensive operations.

The MDB files themselves are typically small, typically a few MB, the files can be exclusively opened only for the user opening the file.

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DAO.OpenDatabase("<path to mdb>", True, False, "")
Set rs = db.OpenRecordset("Data")

Any suggestions for options to better cache the data or reduce the network traffic with minimal recoding.

While DAO doesn't need to be used, ideally any alternate method would be easy to swap out with DAO.

Upvotes: 0

Views: 572

Answers (1)

Gustav
Gustav

Reputation: 55921

This setup is beyond the recommended usage. At least, a WAN connection should be via a hi-speed low-latency fibre connection.

What can work, however, is to sync a folder in both ends via OneDrive, as the synced file is cached and accessed locally. Of course, in this case only one user can work with the database at the same time, as you have no control over how and when syncing will happen.

Upvotes: 1

Related Questions