CODEWITHSUNDEEP
sqlms-accessvba
Niclas
Niclas

Reputation: 1252

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) & ")));"

Upvotes: 0

Views: 562

Answers (1)

Nathan_Sav
Nathan_Sav

Reputation: 8531

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

Upvotes: 1

Related Questions