Microsoft Access Database Performance

The topic of Microsoft Access performance came up recently so posting some of the discussion points here. The issue is not always with Microsoft Access itself, but might include variables such as network speed and hardware so as always, a more holistic view should be taken when analyzing performance.

  • When possible, avoid using an .MDB/.ACCDB as the back-end data engine across a network. Doing so can result in intermittent data corruption. When possible, use a back-end database such as Microsoft SQL Server, MySQL, Oracle (anything designed for “enterprise” / network / multi-user settings).

 

  • If the concurrent user load on the Microsoft Access back-end isn’t huge (What is “huge”? Depends <as always> on the many variables at play…) and the server and network are beefy enough and not under significant load by other resource demands, then you certainly can run Microsoft Access as the back-end database but if you have access to an enterprise database or can have it installed (there is a free version of most enterprise databases), go ahead and do that and eliminate at least one variable. Migrating an existing Microsoft Access back-end database is not difficult (especially if migrating to Microsoft SQL Server) and pointing a Microsoft Access front-end (user interface: forms, reports, etc.) is also easy to do.

 

  • In the front-end/use interface .MDB/.ACCDB Microsoft Access file, try to avoid things such as:
    • Multi-record forms. Try to use single-record forms when possible or at least require forms to filter data being returned from the back-end database versus always bringing back all records first and then filtering/browsing.
    • In forms, reports or queries, when possible consider not using intrinsic (built-in) Microsoft Access functions such as DLookup. If you are using ODBC to link to a non-Microsoft Access back-end database and you (for example) fire a query that uses Microsoft Access function that the back-end doesn’t know how to deal with, all records are returned to the front-end application to allow it to then apply the intrinsic function which can mean a lot of records being unnecessarily returned across the network.

 

  • Finally, when linking tables, add some code that fires on application start-up to refresh table links to the back-end enterprise database. This ensures that table links (in this example links to Microsoft SQL Server tables) are always available and current (in terms of any back-end table design/definition changes). I typically call a Sub like this in the start or opening/main form load event when the Access application first starts. Include error handling (here and in all your VBA coding) so you (and your users) know what happened and what to do next if the re-linking fails.
    Sub ReLinkTables()Dim strMSSQLConnect As String
    Dim td As TableDefOn Error GoTo Error_Handler

    ‘Use trusted authentication or pass credentials
    ‘strConnect = “ODBC;DRIVER=SQL Server;SERVER=MyServer;DATABASE=MyDatabase;Trusted_Connection=Yes”
    ‘This will save the username and the password with the linked table information.
    sMSSQLConnect = “ODBC;Driver={SQL Server};Server=MyServer\SQLEXPRESS;Database=MyDatabase;uid=MyUID;pwd=MyPWD;”

    ‘First remove all linked tables
    For Each td In CurrentDb.TableDefs
    If td.Connect <> “” Then
    CurrentDb.TableDefs.Delete td.Name
    End If
    Next

    Set td = CurrentDb.CreateTableDef(“MyTable1”, dbAttachSavePWD, “dbo.MyTable1”, sMSSQLConnect)
    CurrentDb.TableDefs.Append td

    Set td = CurrentDb.CreateTableDef(“MyTable2”, dbAttachSavePWD, “dbo.MyTable2”, sMSSQLConnect)
    CurrentDb.TableDefs.Append td

    Error_Handler:
    If Err.Number <> 0 Then
    MsgBox “Table re-linking error: ” & Err.Description
    End If