Niclas Niclas - 6 months ago 21
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
using
CreateQueryDef
. I want it to be saved as shown on the screenshow below.

Can/Should I use the
.CreateTableDef
? 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
CreateQueryDef




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

Answer

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
    CurrentDb.QueryDefs.Refresh
Comments