Go to Top

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.

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

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.

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

12 Responses to "Create temp tables in Access from SQL Server tables"

  • Howard George
    December 29, 2015 - 6:28 am Reply

    Hi Juan

    Great info – I devour your writes and articles – however they often [and by design] create more questions than they solve – but that is what learning about.

    This article has generated such a problem – creating local tables to circumvent heterogeneous joins. Then you caution about importing too much info and bloating the local Access dbase – sensible
    However, what is too much info and when do you link or when do you use SP –

    What I find difficult is when to link or not to link – when to import to local – or when to use SP.

    However, on top of this is a pure design [or good programming technique] learnt the hard way – ie ODBC linked tables MAKE LIFE EASY – and make use of DAO [which has made a resurgence in 2013] – just easy to use as the linked table just becomes part of Access without thinking about the network aspects. However one major disadvantage is break the link and your queries tumble.

    One solution is – import data from SQl to local tables – close link – run query on local tables – never break them – can become bloated – but can run query for solution then empty local tables

    Recently I created a program solution frontend Access – backend giant SQL Server 2012 – which [I wasn’t told ] was somewhere out there on the network – ODBC links worked well – all worked well – and makes it easy via DAO to view, edit,update – however – once you go realtime this network linkage created a real lag in the package. New records were however created via ADO – open connection – addnew – close. View, edut/Update was DAO.

    And then there is a problem that when you open an ADO OLE connection to an SQL dbase table – and you create a Rst – it isn’t then easy to put that data into a local table without a recursive loop.

    Question 1. Your 1st approach uses a PTQ +Make Table Query combo – but is this faster than an ADO connection bypassing ACE – creating a RST then just using a recursive loop to populate a local table
    Question 2. Your comments on using linked tables in queries -and how do you not do this except by copying data to a local table.
    Question 3. If the Sql dbase is not too large – and you do import all relevant tables – then run queries to view your data – if you need to edit – is that easy to do in this situation
    Question 4 – or is it better to create views in SQL – link to those via ADO – and so longas these views aren’t too complex [making them read only] edit/update can occur.

    Regards HHG

  • LauraNorth
    December 31, 2014 - 10:51 am Reply

    Juan,
    Your code works well for queries selecting tables and SQL views, but obviously I can’t use it to get data from a parameterized SQL UDF or SP. My desire is to populate the temp table in Access, and then use the temp table as the record source for my Access report. Question #1 – Is this the best approach to populate my Access report from SQL data? Question #2 – How would I best populate an Access “temp” table from a SQL stored procedure using Access VBA?
    Thank you!
    Laura

    • Juan Soto
      December 31, 2014 - 6:39 pm Reply

      Luara,

      Change line 120 to strSQL = strTable and pass along your entire SQL Statement with the UDF or SP. For example, if you have a SP called usp_Dashboard that takes a date as a parameter, pass the following strTable value:
      “Exec usp_Dashboard ’12/31/14′” — Notice the single quotes around the date.

      I have not tested a UDF but it should work as well.

      This January 7th I’ll be doing a session online on how to create dashboards with SQL Server and Access! You can see more details here:
      http://accessusergroups.org/europe/event/access-europe-2015-01-07/

      Please join us!

      Happy New Year!

  • Anirudha Kadam
    August 19, 2014 - 6:42 pm Reply

    Hi,

    Thanks a lot of this example… I have been looking for a way to do exactly this.. I had tried using passthrough query, but even that was really slow, but this is excellent. Cut the speed by almost Half.

    Best Wishes

    Anirudha Kadam

  • DAO or ADODB? Which one should you use and when?accessexperts.com | accessexperts.com
    April 9, 2013 - 12:44 am Reply

    […] When you need to download data into temp tables in Access […]

  • Andrew Richards
    January 11, 2012 - 11:44 am Reply

    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

    • Juan Soto
      January 11, 2012 - 8:18 pm Reply

      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

      • erwin
        January 18, 2012 - 9:24 pm Reply

        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

        • Ben Clothier
          January 21, 2012 - 9:33 pm Reply

          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!

  • erwin
    January 10, 2012 - 8:19 pm Reply

    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.

    • Juan Soto
      January 10, 2012 - 11:09 pm Reply

      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

      • erwin
        January 11, 2012 - 8:58 am Reply

        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.

Leave a Reply

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

 

Contact Us
[gravityform id="16" title="false" description="false"]