If you’ve ever asked Access to do a join between a local Access table and SQL Server table (which is called a heterogeneous join) you may have experienced first hand how slow it can be to process results. The situation could be vastly superior if you can afford to download the SQL table as a temporary table to your Access front-end and then process the join. This post will provide you with an easy solution to download SQL Server data into Access using a subroutine you can call from code.

ADVERTISING
MS Access Consulting

Pass Through Query + Make Table Query Combo (No Linked Table Approach)

There is no direct way to download SQL Server data into Access using code, you can’t use a ADODB recordset or command to download the data, since they only “see” the SQL Server side and don’t have exposure to your Access data. The technique I’m providing here will use a temporary pass-through query, then using it with a make table query to create the local Access table copy of your SQL data.

The procedure takes three arguments:

  • strTable: Name you wish to use for your local Access table
  • strSQL: SQL statement used to retrieve data from SQL server. You can therefore extract multi-table joins into a local Access table.
  • bolExportExcel: Boolean variable you pass-along if you wish to also extract the data into Excel

Example 1: Extract one table from SQL Server called tblCustomers

Notice how I appended the _SQL to my local Access table name. I will later delete all _SQL tables on program exit.

Example 2: Multi-table join extract

In the second example the code will also launch Excel with the extracted data, allowing my customer easy Access to data from SQL Server without the use of ODBC in Excel.

Add indexes and primary keys if needed

Once you’ve downloaded the data you may have a need to add indexes or primary keys to your local copy of the table, click here for a post that will walk you through that using Alter SQL commands in Access.

Another Approach using Linked Tables

If you have the SQL Server table already linked to your Access front-end you can extract the data using this procedure:

 

Some Caveats

  • Don’t download too much data. There’s a good reason your data is on SQL Server. Pulling large amount of data only eats up network traffic and may cause contention issues or even deadlocks. I usually use this technique for small reference tables that I later use in a all local joins. If your data is small and the data rarely changes in the table you’re downloading then this code is for you.
  • Beware of file bloating: Ideally you should keep your temp tables in a separate Access file, otherwise your front-end may start getting pretty large and require frequent compacting to slim it back down.