Access 2007 VBA code for Make Table Query

Posted on 2009-12-21
Last Modified: 2013-11-27
Hi Experts

I have an Access 2007 database which includes some  pass-through queries to a Great Plains database. The data in these queries is not updated very frequently however because of the way I'm using the db (Excel forms based interrogation of the Access db) the response times when these queries are invoked makes the Excel form user experience very slow. The GP databases are in USA and I'm in London.

So - what I want to do is to make a VBA routine in Access (which I can fire up from the Excel interface) which effectively makes a static table from the pass through. I have tested the db using this technique and the Excel forms run way faster. I don't need to make the static table from the actual pass-through query, I have a query on the query which allows me to Trim etc.

This is what I'd like to automate

1) Select the Query "CustomersTrimmed" (which is a query based on a pass through query called "Customers__GP")

2) Make a table from "CustomersTrimmed" called "Customers" - this table already exists and I'd prefer it to be recreated each time the VBA runs (unless the advice is to the contrary). The Excel forms will point to this table for Customer data rather than the pass-through query.

3) Suppress all warning messages so the user can can just click on the Excel form to update the table

I'm comfortable with Excel VBA but I've never touched Access VBA so I'm hoping someone can take me step by step through this.

I'm sorry if this is answered elsewhere but on searching today I haven't found anything I recognised.



Question by:pfmurray
    LVL 119

    Accepted Solution


    place this codes in a module in Excel

    sub createTable()
    on error resume next
    Dim db As Database, strSQL As String, dbPath As String

    set db=opendatabase (dbPath,True)

    db.execute "drop table customers"   'delete the table customers

    strSql="select CustomersTrimmed.* into Customers from CustomersTrimmed"

    db.execute strSql


    end sub


    Author Comment

    Capricorn1 - Thank you very much indeed! I got an error initially in Excel  regarding  "Dim db as database" but I already had code that defines it thus: Set MyDatabase = New ADODB.Connection.

    Once I replaced "db" in your example with "MyDatabase" this worked.


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now