Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    17

    Unanswered: Make Table query takes longer than Select query?

    Hey All,

    I've got an MS Access Query that, while complicated with many different tables involved, still opens into Datasheet view in about 8 seconds. It opens with 1800 records, I can go to the end of the recordset with no delay, etc...., it's obviously all done computing and loading into memory.

    However, if I make this query into a Make Table query, it turns my MS Access instance unresponsive. Same thing if I make a new Make Table query based on it.

    Any idea wtf is going on with Access here?

    Thanks!

    -Ben

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Try to split your complicated query into small queries then binde them together forming the Maketable Query....

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Make table method

    good response hammbakka. We often split up the make-table (or append-tables) queries into ones which require the least amount of time running first and then link that table with the other ones to produce other tables or update fields in the original table. It surprisingly is a technique which few seem to understand which works quite well (especially with linked tables to SQL Server.) The only thing I would add to that is that if it the tables are SQL Server tables, you might want to create a vbsscript (see attached) to duplicate the main-source table with a main-source-username table and opens that table for them. Then they don't get into a bind with someone else having the report open (goes to lunch) and is it bound on the make table and other's can't run the routine. If you're running this with Access tables, this is something to keep in mind. I'm not sure if making them queries on the report would help.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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