We are SQL Server Experts. Call Us TODAY! +17738095456 or email us at sales@itimpact.com
  • The Team
  • Our Services
    • Dashboards Developers
    • Microsoft SQL Server
    • Microsoft SQL Server hosting for Access
    • SQL Server Integration Services (SSIS)
    • SQL Server Reporting Services (SSRS)
    • SQL Server Data Quality Services (DQS)
    • Microsoft Access
    • Microsoft VB.Net
    • Cross Functional / Business Unit Integration
    • Financial Statement Consolidation
    • Website Design
  • Case Studies
    • Automatic Reporting for Security Services Firm
    • Amazon API Integration with Access
    • Custom Quoting and Proposal Solution
    • Data Quality Analysis for Government Program
    • New Claim System Reduces Processing Time
    • Pipe Tracking Software
    • Product Search Analysis
    • Safety Training, Incident and Accident Tracking Tool
    • Union Dues and Membership Management System
  • Blog
  • Contact
  • The Team
  • Our Services
    • Dashboards Developers
    • Microsoft SQL Server
    • Microsoft SQL Server hosting for Access
    • SQL Server Integration Services (SSIS)
    • SQL Server Reporting Services (SSRS)
    • SQL Server Data Quality Services (DQS)
    • Microsoft Access
    • Microsoft VB.Net
    • Cross Functional / Business Unit Integration
    • Financial Statement Consolidation
    • Website Design
  • Case Studies
    • Automatic Reporting for Security Services Firm
    • Amazon API Integration with Access
    • Custom Quoting and Proposal Solution
    • Data Quality Analysis for Government Program
    • New Claim System Reduces Processing Time
    • Pipe Tracking Software
    • Product Search Analysis
    • Safety Training, Incident and Accident Tracking Tool
    • Union Dues and Membership Management System
  • Blog
  • Contact
Toggle panel Social icons
SQL Server

Create temp tables in Access from SQL Server tables

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.

No Comments on “Create temp tables in Access from SQL Server tables”

  1. erwin January 10, 2012 at 8:19 pm

    Hi! good day Juan, I would like to ask about N-Tier Architecture. A SQLserver in a server accessed by a client pc with ms access or vb frontend is a client server model or 2-tier. What if both SQLserver and client frontend was installed in the same server but a terminal server and accessed by the client using remoteApp or remote Desktop? still a 2-tier or 1-tier model? is it possible to implement a 3-Tier model in ms access a.k.a MVC? . I’m also an access die hard but instead of using sqlserver as a backend, I find mySQL and Firebird more compatible with access (that is my own observation- migration and conversion is very straight forward and I dont have to worry even with the boolean Y/N field in access). Thanks and more power to you.

    Reply
    1. Juan Soto Post authorJanuary 10, 2012 at 11:09 pm

      Hi Erwin,

      Great questions! Yes, it’s still a two tier model regardless if it’s via RDC, WAN or Local network. I’m not sure what MVC is, perhaps you can elaborate?

      In regards to a three tier model, here’s one to consider:
      Main database on SQL Server
      Access database on client PC
      SharePoint Database holding temporary or transient data
      Access Web Services running on IPad or other web device.

      Hope that helps!
      Juan

      Reply
      1. erwin January 11, 2012 at 8:58 am

        Thanks again Juan,

        below is the definition from MS

        The Model-View-Controller (MVC) pattern separates the modeling of the domain, the presentation, and the actions based on user input into three separate classes [Burbeck92]:

        Model. The model manages the behavior and data of the application domain, responds to requests for information about its state (usually from the view), and responds to instructions to change state (usually from the controller).

        View. The view manages the display of information.

        Controller. The controller interprets the mouse and keyboard inputs from the user, informing the model and/or the view to change as appropriate.

        Controller. The controller interprets the mouse and keyboard inputs from the user, informing the model and/or the view to change as appropriate.

        Reply
  2. Andrew Richards January 11, 2012 at 11:44 am

    Hi Juan

    Interesting read – and it just goes to show that as always, there are multiple ways to skin a cat…

    I have a question. In this sort of scenario – one where I wanted a temporary table, but one which may have relationships to other tables – I’ve always been likely to do the work in two steps. Firstly a DDL SQL statement to create the table , along with its contraints, indexes and foreign keys. Then secondly, a straightforward DML statement to append the data into that table.

    I’m certain that in fact this is simply down to “the way I’ve always done it” – but I suppose I’ve also instinctively felt that creating the indexes etc on an empty table and then appending data would be faster than using an ALTER TABLE command once the data already resides there.

    Do you have any thoughts on whether one way or other is likely to yield a performance benefit?

    Thanks for a thought-provoking read!

    Andrew

    Reply
    1. Juan Soto Post authorJanuary 11, 2012 at 8:18 pm

      Hi Andrew,

      Thanks for sharing your technique! I can’t tell you for sure if doing the indexes first and then filling the table with data is faster but your approach does require more discipline.

      Sometimes a temp table is the derivative of a multi-join query, so I commend you in taking the time to create the table first, I for one use the INTO statement and then ALTER but each of us has our preferences!

      Sincerely,
      Juan

      Reply
      1. erwin January 18, 2012 at 9:24 pm

        Hi Juan good day to you! is it possible to create a 3 tier model or application in pure or native ms access app with a server db backend. It says in 3 tier architecture that the business logic or rules is put into separate server hardware..im puzzled if its possible and really applicable in ms access. Although im very satisfied with 2 tier implementation with access, i just want to explore some of the possibilities (no sharepoint). Hoping for your prompt response and tnx so much. erWIN

        Reply
        1. Ben Clothier January 21, 2012 at 9:33 pm

          Erwin, good question.

          N-tiered architecture in general depends on having a middleware… this could be a web server, a DLL or something similar sitting between the client and the database server. As such, you wouldn’t use ODBC, OLEDB or similar technologies when you build a middleware but rather implement the functionality in forms of web services using protocols like SOAP or REST or a properitiary interface which the client must support in order to connect to the middleware. N-tiered architecture is a wonderful when you have a need to support multiple numbers of architectures (think of FaceBook or Twitter – they need/want clients running on not just Windows, but also Mac OS X, iOS, Windows phone, Android… list goes on).

          Hopefully, it may become clear why in general Access doesn’t really lend itself as a n-tiered client. We typically are more likely to use it in a corporate environment where there everyone is supposed to use same OSes, is constrained in how they can access data (e.g. may not get to it from home unsecured). In that context, n-tiered architecture would possibly be overkill. That doesn’t mean we have to use 2-tiered architecture exclusively. Juan already cited SharePoint as an example. You want another example not using SharePoint? Write procedures to consume a SOAP web service would effectively make Access a client in a n-tiered architecture. Even better, because it’s going over port 80, it may mean it now can use data from sources even when firewall setting is excessively restrictive and there’s no chance of getting the network guy to change it. Microsoft has an example of calling SOAP web service from VBA:
          http://msdn.microsoft.com/en-us/library/aa140260(v=office.10).aspx

          I hope that help. If you need assistance building a solution that would enable Access to participate in a n-tiered architecture, feel free to make use of our consulting service and call us at 773.809.5456.

          Best of luck!

          Reply
  3. Pingback: DAO or ADODB? Which one should you use and when?accessexperts.com | accessexperts.com

Click here to cancel reply.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

XHTML: You can use these tags <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Recent Posts

  • 23 Jan. 2019 7 essential SQL skills that can help you command a higher paycheck

  • 18 Jan. 2018 Quick Guide on Importing Data from SQL Server (or SQL Azure) to Cosmos DB

  • 10 Jan. 2018 Top 7 Questions about Basic Availability Groups

  • 04 Jan. 2018 An introduction to mssql-cli, a command-line client for SQL Server

  • Share and work together on the intranet with SharePoint Team Sites

ITImpact

About IT Impact, Inc.

IT Impact, Inc.

IT Impact, Inc. is a minority-owned software company specializing in custom software solutions. We work with SQL Server, Microsoft Access, web applications, and much more.

Our staff is top of the line and includes two Microsoft MVPs!

Twitter

Tweets by @ITImpactInc

Facebook

© ITImpact. Webdesign by GreatCustomWebsites.com