Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make Tables with MS Query (E2K, SP-3)

    It's hard to say whether this post should be in the Access forum, the Excel forum, or general office, but here goes:

    I have written a database to integrate two other databases. Everything here works fine.

    The problem is that the "reporting" tool is Excel. I'm using MS Query to bring in the data from Access, but some of the queries are horribly slow (they're queries built on queries built on queries...). I have found MS Query can run a make table query, which would speed the process considerably. The problem is how do I run a make table query in MS Query from Excel?

    My thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Tables with MS Query (E2K, SP-3)

    Interactively, you'd view the SQL of a query in MS Query and change its SQL to that of a make-table query (SELECT ... INTO ... FROM ...), but I have never actually tried this.

    I'd do it in VBA, using DAO or ADO to connect to the database, and run an SQL statement.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Tables with MS Query (E2K, SP-3)

    I did experiment with altering the SQL in MS Query to make a new table, and it worked like a charm, Hans. The problem was that it returned no data to Excel, so I couldn't record the action to "deconstruct" the macro and alter it accordingly. I'll see what I can do in VBA, but I have no real experience with ADO/DAO programming. Thanks for your reply.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Tables with MS Query (E2K, SP-3)

    To use DAO:
    - Set a reference to the Microsoft DAO 3.6 Object Library in Tools | References.
    - Use code like this:

    Dim dbs As DAO.Database
    Dim strPath As String
    Dim strSQL As String

    strPath = "C:AccessMyDatabase.mdb"
    strSQL = "INSERT INTO tblNew (Field1, Field2) SELECT (Field1, Field2) FROM tblOld"

    Set dbs = DAO.DBEngine.OpenDatabase(strPath)
    dbs.Execute strSQL, dbFailOnError
    dbs.Close
    Set dbs = Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •