Make table query via vba

[Trebormac]

I have a make table query that I want to run from VBA. What is the syntax to add or change the table name?

Thanks for any help!

[DonRayner]

When in query design select (View - SQL View) from the menu bar and copy the sql text. Paste this into your vba, it will be something like this.

SELECT YourOldTable.* INTO YourNewTable
FROM YourOldTable;

You then need to convert this into a string to run the sql from vba

Dim stSQL, YourNewTable
YourNewTable = "???????"
stSQL = "SELECT YourOldTable.* INTO " & YourNewTable & " FROM YourOldTable;"
DoCmd.RunSQL stSQL

The YourOldTable would be replaced by whatever your source table is and the YourNewTable would be a string variable variable that you set with the name of the table you wish to create.

[NeoPa]

The table name within an Access QueryDef is part of the design and cannot be changed when running the query from code.

A change to the design would be required.

Alternatively, you can get the SQL from the saved QueryDef into a string and modify that string in VBA, then execute that string as SQL as Don has in line #4 of his VBA code.

Welcome to Bytes!