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.

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
250     Resume Next
260   Case Else
270     MsgBox Err.Description, vbInformation, "Error"
280     GoTo ExitProcedure
290 End Select
End Sub

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.