How to Query and Dump Results Into Table In Access

Question

Need to query my SQL server from Access using an ADO connection (for example), and then using something like:

Currentdb.CreateTableDef()

in Access to create a table (in Access) with the query results.

How can I do this?

Answer 1

Using DAO:

currentdb.execute "SELECT * INTO LocalTableName FROM SQLServerTable;"

The string inside the quotes should be identical in ADO but I don't use ADO much.

Answer 2

You could consider SQL DDL CREATE TEMPORARY TABLE syntax. From the Access 2007 Help:

When a TEMPORARY table is created it is visible only within the session in which it was created. It is automatically deleted when the session is terminated. Temporary tables can be accessed by more than one user.

... my tongue is firmly embedded in my cheek :) This syntax doesn't exist in the Access Database Engine and never has. Instead, it's another example of the appalling state of the Access documentation on the engine side of the house. Caveat emptor.