CODEWITHSUNDEEP
sqlsql-serverms-access
Sev
Sev

Reputation: 15737

How to Query and Dump Results Into Table In Access

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?

Upvotes: 0

Views: 541

Answers (2)

onedaywhen
onedaywhen

Reputation: 57073

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.

Upvotes: 0

Tony Toews
Tony Toews

Reputation: 7882

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.

Upvotes: 2

Related Questions