Create and save Make Table Query in VBA

Question

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.

<-- What I want to do
<-- 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) & ")));"

Nathan_Sav · Accepted Answer · 2016-05-16 11:19:04Z

1

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

answered May 16, 2016 at 11:19

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create and save Make Table Query in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related