Create query dynamically in VBA?

[Guest]

I need to run a transfertext command based on data specified on a form. Part
of this is a multi-select liist box. So I need an WHERE... IN(... ... ...) in
my SQL based on the listbox selections. Transfertext requires a query so how
do I create a query in VBA. I have all the code figured out to construct the
SQL, I just need to know how I create the query. Thanks.

 

[John W. Vinson]

I need to run a transfertext command based on data specified on a form. Part
of this is a multi-select liist box. So I need an WHERE... IN(... ... ...) in
my SQL based on the listbox selections. Transfertext requires a query so how
do I create a query in VBA. I have all the code figured out to construct the
SQL, I just need to know how I create the query. Thanks.

The SQL *IS* the query. You can use the SQL directly in the Transfertext
command.

If you want to save the SQL into a saved query, you can use the CreateQuerydef
method:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim strSQL As String
Set db = CurrentDb
strSQL = "some valid SQL string"
Set qd = db.CreateQuerydef("NewQueryName", strSQL)
<if you want to run an action query you can then do
qd.Execute, dbFailOnError

John W. Vinson [MVP]

 

[Allen Browne]

Assign the SQL statement to the SQL property of the QueryDef.

Create an example query, and save it with a name such as qryExport. Then
code like this:
Dim strSql As String
strSql = "SELECT ...
CurrentDb.QueryDefs("qryExport").SQL = strSql