Query Def

Hello,
Is there a way to open a Query as QueryDef and change the "New Table Name" (INTO) in access code before running the query?

I want to run the same Make Table query with different Output names.
Thanks In advance
LVL 1
frimyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

frimyAuthor Commented:
ms access
frimyAuthor Commented:
Query Definition
Rey Obrero (Capricorn1)Commented:
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"
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Nick67Commented:
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
Gustav BrockCIOCommented:
To cut it down:
Dim qdy As DAO.QueryDef
Dim SQL As String

Set qdf = CurrentDb.QueryDefs("YourMasterQuery")
SQL = qdf.SQL

qdf.SQL = Replace(SQL, "New Table Name", "FirstTableName")
qdf.Execute
qdf.SQL = Replace(SQL, "New Table Name", "NextTableName")
qdf.Execute
' etc.

Set qdf = Nothing

Open in new window

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
frimyAuthor Commented:
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
Gustav BrockCIOCommented:
You are welcome!

/gustav
Nick67Commented:
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)
frimyAuthor Commented:
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
Nick67Commented:
Does the structure of the tables remain the same, or do the fields, field names, and data types change dynamically?
frimyAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.