I have chased this problem for years and finally found the cause and the solution.
MS Access is a little like OneDrive where it boggs when syncing to too much data. MS Access it is syncing to every table linked to the application and that is why it runs slow.
If you save off a copy and delete most of your linked tables, the speed with radically improve. Ok, that proves the problem, but not the solution...
The solution involves creating a table of table names and connection strings, then you dynamically add or remove table links that you do or do not need. (code sample below)
I have a button on the main form called “Remove Add On Demand Tables” This deletes tables not needed at program launch. I run this when I start coding so it runs quickly.
All other linked tables are added using VBA code as the user enters one module or the next.
It's a pain at first, to code it all out, but a couple functions later and it works under the hood without anyone being the wiser.
BTW, the same code can be ran to ensure your connection to SharePoint has not converted to Offline mode.
Here's some sample code to get you started.
Sub UpdateSharePointOnlineConnection()
' Specify the name of the linked SharePoint list
Dim linkedTableName As String
linkedTableName = "YourLinkedTableName"
' Specify the SharePoint Online site URL
Dim sharePointURL As String
sharePointURL = "
https://your-sharepoint-site-url.com"
' Build the new connection string
Dim newConnectionString As String
newConnectionString = "ODBC;DRIVER=Microsoft SharePoint List " & _
"Driver; " & _
"SHAREPOINT; " & _
"LIST=" & linkedTableName & "; " & _
"STUBNAME=LIST_" & linkedTableName & "; " & _
"DATABASE=" & sharePointURL & ";"
' Update the connection string for the linked table
Dim db As DAO.Database
Set db = CurrentDb
db.TableDefs(linkedTableName).Connect = newConnectionString
db.TableDefs(linkedTableName).RefreshLink
' Release the database object
Set db = Nothing
End Sub
