code for saving data from a query into a table

A

Anakin Moonwalker

Hi!

When I run a query, the data gets displayed in Datasheet view.

Can someone please show me how to write the code that would save the data in
a table?

Thanks!
 
A

Allen Browne

To save the data into a new table, choose Make Table from the Query menu.

To add the data to an existing table, choose Append from the Query menu.

If you want to do this programmatically, use the RunSQL action in a macro,
or Execute the query in code, e.g.:
dbEngine(0)(0).Execute "Query1", dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
D

Dennis

With your query open in design view, click on the query menu at the top and
then select make table query.
 
A

Anakin Moonwalker

Hi Allen!

This is exactly what I want to do.
But whenever I run this (by clicking on a button).

dbEngine(0)(0).Execute "Query1", dbFailOnError

....an error occurs: Cannot execute Select query.

Please help... anyone...
 
N

Nikos Yannacopoulos

Change your query to a Make-Table or Append query first, like Allen
advised, then run the code! VBA can only execute action queries, not
Select ones.

HTH,
Nikos
 
A

Anakin Moonwalker

How do I change my Select query to a Make-Table/ Append Querry using just VBA
code?
 
A

Allen Browne

If you save the query as a Make Table or Append query, you can execute it.

If you don't want to do that, then you can create the SQL statement as a
string in your VBA code. Switch your Append query to SQL View (View menu in
query design), and use what you see there as an example of the string you
need to create in your code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
 
Top