CODEWITHSUNDEEP
vbams-access
Eric
Eric

Reputation: 3

VBA very slow after splitting Access DB (local PC, not network)

After splitting my Access DB I have found that VBA code run in MS Project (called from within Access) has slowed down significantly (by a factor of about 6).

I am using MS Access 2016 and Project 2016, both on my local PC with no network traffic.

The VBA code in MS Project does a series of lookups against the Access database and it is these procedures that are running slowly. I would have expected a slowdown if my Access Backend was on a corporate network, but am surprised to see a slowdown when both back end and front end and MS Project are all local.

Any suggestions?

Thanks

Eric

Upvotes: 0

Views: 382

Answers (1)

kismert
kismert

Reputation: 1692

Most likely, the slowdown you are experiencing is the backend file repeatedly opening and closing when it is being accessed by the frontend. The accepted solution is to open a persistent link between the frontend and the backend. This way the backend file will stay open for as long as a frontend is open.

To set this up, you will need to create an empty table in the backend that isn't used anywhere else in the project. I'll call it tblKeepOpen

Sample code: (called in the AutoExec macro)

Public Sub KeepBackendLinkOpen() 
    Static rsOpen As DAO.Recordset ' Static makes the Recordset reference persist
    Set rsOpen = CurrentDb.OpenRecordSet("SELECT * FROM tblKeepOpen", dbOpenSnapshot)
End Sub

This reference will persist until the VBA interpreter crashes or loses state. You can reload the link by running KeepBackendLinkOpen from the Immediate window, or running AutoExec again.

Some people like to manage persistent links in a hidden form. Your choice.

PS -- there are lots of examples on the internet on how to do this, but I couldn't find any with a nice simple example.

Upvotes: 1

Related Questions