SQL Intergrated with VBA

[Ripley]

Hi, i've searched the forums, no look with this one im afraid.

What i want is when a button is pressed, i want the following to happen:

1. I want a new blank table to be created.
   
2. I want records from an SQL query to be added into the table.
   
3. I want to send that table to an external MS Access file.
   
4. I want the tempory table to be deleted.

I 've done a lot of reasearch into this, and i have found that the best way to do it is using an SQL statement, and then the DoCmd.RunSQL property.

But i cant figure out the sql code to do the above, can anyone give me some generic code that i can follow to help me?

Thanks!

 

[The_Doc_Man]

You've got a couple of choices here. Macros or VBA code. The button wizard for a form will run a macro for you if need be.

In overview, do these things:

First, build the SQL query that is referenced in your objective #2.

Next, turn it into a make-table query (which meets objective #1.)

Next, look into the Export functions, which include the option to send a table to another place, and that place COULD be another .MDB file. Just watch out for name conflicts if you create the same name twice in a row. Look specifically for help on the TransferDatabas action. That would take care of objective #3.

Next, look into the VBA function to delete something from a collection - in this case, the TableDefs collection. I believe that if you have the name you can do a TableDefs("table-name").Delete method to get rid of what you just built. That table name could be in a string variable, too, and would work just as well.
Better, maybe. And that is objective #4.

Next, commit your choice to either a macro or a VBA code sequence as the master stager for these objectives. Write the sequence of steps that will execute your MakeTable query, your TransferDatabase action, and your TableDefs(x).Delete method. Make this sequence something that once you start it, everything is automagically done with no further intervention.

Finally, on the form that is to hold this button, use the button wizard to create the button and, at the appropriate time, choose the button action to run the sequence, either a RunMacro or a RunCode as appropriate.

HINT: Some members of this forum suggest that macros are NOT the way to go because of their inferior error analysis-and-response abilities. To me, if you weren't planning to include error trapping, a macro is OK. And there is ALWAYS the ability to turn a macro into VBA code. So you aren't stuck with the decision forever.

 

[RoyVidar]

Say your query name is myQuery, the other db is c:\mydb.mdb and the new table name is newTable why not just

currentdb.execute "SELECT * INTO newTable IN 'c:\mydb.mdb' FROM myQuery", dbfailonerror

It will fail if the table already exists, though.