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.

Create temp tables in Access from SQL Server tables

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.

'Insert this constant in a public module:
Public Const conConnectionQry As String = _
"ODBC;DRIVER={SQL Server};SERVER=YourServerIPAddressGoesHere;" & _
"UID=YourUserIDGoesHere;PWD=DittoPassWord;" & _
"DATABASE=NameOfYourSQLServerDatabase"

Public Sub CreateLocalSQLTable( _
strTable As String, _
strSQL As String, _
Optional bolExportExcel As Boolean _
)
Dim qdf As DAO.QueryDef
Dim strQuery As String

10 On Error GoTo ErrorHandler
20 strQuery = "qryTemp"
30 DoCmd.Close acTable, strTable

40 DoCmd.DeleteObject acQuery, strQuery
50 DoCmd.DeleteObject acTable, strTable
60 Set qdf = CurrentDb.CreateQueryDef(strQuery)
70 With qdf
80 .Connect = conConnectionQry
90 .SQL = strSQL
100 .Close
110 End With
120 strSQL = "Select * INTO " & strTable & " FROM " & strQuery
130 CurrentDb.Execute strSQL
140 DoCmd.DeleteObject acQuery, strQuery
150 If bolExportExcel Then
Dim strFile As String
160 strFile = CurrentProject.Path & "" & strTable & _
Month(Date) & Day(Date) & Year(Date)
170 If Dir(strFile) <> "" Then
180 Kill strFile
190 End If
200 MsgBox "Table " & strTable & " is ready for export to Excel"
202 DoCmd.TransferSpreadsheet acExport, , strTable, strFile, True
204 FollowHyperlink strFile & ".xlsx"
210 End If

ExitProcedure:
220 Set qdf = Nothing
230 Exit Sub

ErrorHandler:
240 Select Case Err.Number
Case 3376, 3010, 7874, 2059, 7873
250 Resume Next
260 Case Else
270 MsgBox Err.Description, vbInformation, "Error"
280 GoTo ExitProcedure
290 End Select
End Sub

Create temp tables in Access from SQL Server tables

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
CreateLocalSQLTable "tblCustomers_SQL", "Select * from 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
CreateLocalSQLTable "tblCustomers_SQL", "Select CustomerID, CustomerName, CustomerStatus from tblCustomers Inner Join tblCustomerStatus On tblCustomers.CustomerStatusID = tblCustomerStatus.CustomerStatusID", True
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:
Public Sub CopyTableLocally(strTable As String)
Dim strSQL As String

10 On Error GoTo CopyTableLocally_Error
20 Application.Echo True, "Working on table " & strTable
30 DoCmd.DeleteObject acTable, strTable & "_SQL"
40 strSQL = "Select * Into " & strTable & "_SQL From " & strTable
50 CurrentDb.Execute strSQL
ResumeExit:
60 On Error GoTo 0
70 Exit Sub
CopyTableLocally_Error:
80 Select Case Err.Number
Case 3376, 3010, 7874, 2059 'Trying to delete an object that does not exist, continue
90 Resume Next
100 Case Else
110 MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CopyTableLocally of Module mdlGeneral"
120 Resume ResumeExit
130 End Select
140 Resume ResumeXit
End Sub
 

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.