Often when analyzing data from a SQL Server table via MS Access, I’ll create a local table to specify the values to query. I’ll then link the local table to the SQL Server table (via INNER JOIN).
I’ve noticed, at least in recent versions of MS Access, that when you link a local table with a remote table (ex. ODBC) it’s incredibly slow. Often my MS Access will freeze up, making me think the application will crash.
I’ve been using MS Access for years, I’ve only noticed this as an issue “recently” — I’m currently using Microsoft 365 Apps for enterprise.
How To Avoid Local Tables in MS Access
The obvious way is to put your data into another remote (SQL Server) table.
My suggested approach is a little faster: rather than creating (or modifying) a new table every time, add WHERE criteria.
As a web developer, I use an IDE (specifically Sublime Text, but this also works with VS Code). You can select multiple lines by clicking the mouse wheel, and then move the cursor over (via “Home” and “End” keys) to surround the text in a single quote and append the word “or”:
You could also do this with an Excel formula:
Then query the (one) SQL server table and specify additional criteria:
This approach may not be reasonable for “a lot” of data, but if you’re querying for a handful of items it may be a viable alternative.
This technique greatly speeds up the time it takes me to query a SQL Server table via MS Access when performing data analysis.
Top comments (0)