Access Make Table Query

Question

I'm having some trouble with running a Make Table Query. The query works fine as a select query, returning a datasheet with all the columns that I need. However, when I try to run it as a Make Table query, it gets stuck(No progress for hours).

Is there a way to make a table with the results of a select query without using the Make Table query? The dataset is approximately 350,000 records. The Make Table query worked with a test database of about 30,000 records. Running the Make Table query with a subset(30,000) of the 350,000 record database does not work either.

After a bit of research I came across http://support.microsoft.com/kb/178650. While I don't think this is necessarily the solution to your problem it does bring to light that it is probably the complexity of the query causing the issue. I assume that even though you limited the query to 30k the query is still just as complex and is likely why it is still failing. Though that is just a guess. — Newd, Mar 11 '15 at 18:13
I've had some really slow queries as well. I usually run them overnight. You can try to copy and paste it into a new table. I'm thinking maybe you could try a VBA solution that would create a table from your query, but I've not tried that. — EvDev, Mar 11 '15 at 18:27

score 0 · Answer 1 · edited May 23 '17 at 12:28

Possibly what's happening is a memory and disk resource limit. The Make-Table query on the N=350,000 records is expanding the database and doubling the size of existing data (i.e., in addition to its derived data). Remember number of rows is not the only factor in size: how wide the table (up to max of 255) and data types (double vs integers, memo vs text fields, etc.) impact database size as well. As this SO post shows, you can have a four-column integer table of 7 million records that comes under 200 MB!

Also, compared to the ordinary Select query, the Make-Table query runs the same full or select table scans multiple times depending on the number of joins (think Cartesian products on records) plus it extends to create a new database object, namely the table with all its definitions. This process can easily bloat and reach Access' 2GB limit or close to it slowing down the processing. It's possible if you stay long enough, the error message: Not enough space on temporary disk. (Error 3183) may appear.

Consider passing the Make Table into an external, empty or smaller Access database whose table can then be linked to current database:

SELECT * INTO [NewTable] 
IN 'C:\Path\To\ExternalDatabase.accdb' 
FROM [QueryName]

With automated VBA table link:

DoCmd.TransferDatabase acLink, "Microsoft Access", _
"C:\Path\To\ExternalDatabase.accdb", acTable, _
"SrcTableName", "DestTableName"

Alternatively, link a pre-existing external, empty/smaller Access table and run an append query inside current database:

INSERT INTO [LinkedTabe] SELECT * FROM [QueryName]

Access Make Table Query

1 Answers1