code for saving data from a query into a table

[Guest]

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!

 

[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]...

 

[Guest]

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

 

[Guest]

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...

 

[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

 

[Guest]

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

 

[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