A helpfull soul once posted a routine to run a make table query
programatically, it went something like this:

Dim db as DAO.Database
Set db = CurrentDb
db.Execute "Select MyTable.* INTO NewTable From MyTable", dbFailOnError

And it works great.

My problem is that I want the table to be made in a different database
on a different drive. Any Takers?

    Next Question. Why can't I cut and paste a select query SQL
statement into a procedure. Every time I try I get no end of errors.
Could someone post a simple procedure that programatically runs a select
query? I already learned that you cannot "Execute" a select query.

Any Help will be greatly appreciated.
--

Thank You

Bill Rodgers

I'm not too sure about your first question (creating the table in a
different mdb).  As for programatically running the select query, you are
correct in saying you can't execute a SELECT query.  But you can create a
recordset from a SELECT query.  For example, if you wanted to Debug.Print
the contents of Field1 for every record in Table1, you could do the
following:

Sub PrintOut
     Dim db As Database
     Dim rs As Recordset
     Set db = CurrentDB
     Set rs = db.openrecordset("SELECT Table1.Field1 FROM Table1;")
     rs.MoveFirst
     Do While Not rs.EOF
          Debug.Print rs!Field1
          rs.MoveNext
     Loop
End

Or, if you have a query called Query1 (defined as the SQL used above), and
you want to do the same thing, you can do the following:

Sub PrintOut2
     Dim db As Database
     Dim qd As QueryDef
     Dim rs As Recordset
     Set db = CurrentDB
     Set qd = db.QueryDefs("Query1")
     Set rs = qd.OpenRecordset
     rs.MoveFirst
     ....etc.
End

As for cutting-and-pasting the SQL, you can kinda do that.  What I've found
usefull is to define a string variable, and store the results of the paste
into the string variable, and then use the string variable in place of the
"Query1" or the "SELECT" as used above.  For example, if you cut and paste
the SQL from a query into a module, you'll get something like:
     SELECT *
     FROM Table1
     WHERE(((Field1)="A"));

You'll want to change it so it looks like:
     Dim sSQL As String
     sSQL = "SELECT * FROM Table1 WHERE(((Field1)=""A""));"

Note:  The double-quotes in the string (around the A) will convert to a
single-quote within the variable.  Then you could do things like:
     Set rs = db.OpenRecordset(sSQL)

Does that help?

Michael Kucan

SELECT MyTable.* INTO [full path to other db\otherdb.mdb].NewTable
FROM MyTable

Joan

 To fidle with data in an external db instead of this:
Set dbTbl_ImportPaid = CurrentDb

try this.
 Set dbTbl_ImportPaid = OpenDatabase("VIDP20041999-343467_Paid.mdb")