Niclas Niclas - 1 year ago 82
SQL Question

Create and save Make Table Query in VBA

I am not able to create and save a

make table
-query through VBA. I am able to execute it using (DAO.Database) currentdb.execute, but not save it as a query like I can with a
select query
. I want it to be saved as shown on the screenshow below.

Can/Should I use the
? If yes, can you provide an example.

enter image description here <-- What I want to do

enter image description here <-- What I can do using

strSQL = "SELECT PRODUCT.product_id, PRODUCT.upc,, PRODUCT.size, PRODUCT.uom, PRODUCT.manufacturer, PRODUCT.category, PRODUCT.colour INTO PRODUCT IN 'C:\LIVE IMAGES\" & frm.cboManu.Value & ".mdb'" & _
"WHERE (((PRODUCT.desc_a) IN (SELECT desc_a FROM PRODUCT WHERE manufacturer LIKE " & Chr(34) & "*" & frm.cboManu.Value & "*" & Chr(34) & ")));"

Answer Source

You need to create a QueryDef in order to do this. Use the Access help on QueryDef. Hope this helps.

    Dim q as QueryDef    
    Set q = New QueryDef
    q.SQL = strSQL
    q.Name = "TEST_SQL"
    CurrentDb.QueryDefs.Append q