Query Def

ms access

Query Definition

dim qd as dao.querydef, db as dao.database, ssql as string, strTable as string
set db=currentdb
strTable="NewTable"

set qd=db.querydefs("yourQuery")

ssql="select tblA.*into " & [strTable] & " from tblA"

qd.sql=ssql

docmd.openquery "yourQuery"

Yes, it can be done.
Tables are something that should be created and then persist.
Hopefully you have a good reason (like repeated ETL jobs) for wanting to do this

Dim db as database
set db = currentdb
dim qdf as querydef
set qdf = db.querydefs("TheNameOfMyQuery")
qdf.SQL = "Holy crap I just completely hosed up the SQL of my query!"

Now, clearly you wouldn't actually want "Holy crap I just completely hosed up the SQL of my query!" as your SQL.

Likely you'll do something like

Dim theSQL as string
theSQL = qdf.sql
theSQL = replace(theSQL,"someTableThatWasHereFirst","TheNewTableName")

and then likely
qdf.execute

It's very simple and works just fine.
I appreciate your quick response.

Question,
When i run the code and the "New Table Name" already exists, I get a message "Cant create table".
Is there a way before qdf.execute to automatically delete the existing table and create a new one, like running the query window or i have to delete with Delete object command?
Thanks

You are welcome!

/gustav

Is there a way before qdf.execute to automatically delete the existing table and create a new one, like running the query window or i have to delete with Delete object command?

There are ways.

But that's a bad idea.  Tables are meant to persist.  A boatload of creation/deletion of tables eventually leads to a corrupt database.

There are easy ways to delete all the data from a table and then append new data to it.
You should be creating tables as a permanent place to store data, and then deleting/appending data to them as appropriate.

One exception to this is ETL applications (Export, Transform, Load)

You are right.
I'm only using a small database with three tables just to transfer data to the web.
e.i. a list of the Inventory to upload to the web daily

Does the structure of the tables remain the same, or do the fields, field names, and data types change dynamically?

Everything stays the same.
I just query a list from the inventory and create a table.
I can do an append query or delete all the items from the web table and recreate a list thru code.
I just wanted an easy way out.
Thanks for your help