Can a union query be created from runSQL in VBA code

[Guest]

I have an application that tables are standardized but can be created form
menus by the users. I need to be able to regenerate a union query that reads
all the standardized tables (i.e., tblDimen_XXX, tblDimenYYY). I have the
code that creates the SQL variable in VBA, but I get a error message that I
need a valide SQL statement. Things that I read indicate creating unioun
queries from SQL are not allowed ?!?!?!?!?

 

[John Spencer]

RunSQL can only be used with ACTION queries. A UNION query is not an action
query - it does not add, delete, or modify any records.

You can save the SQL statement as a query and open the query. Or you can
use the SQL to create a recordset.

 

[Guest]

Because a Union query is not an action query, it can't be used with RunSQL.
What is it you want to do with the query? If you can tell me your objective,
I can show you how to get arround this.

 

[Guest]

Thanks for responding -
I figured the runSQL would not work, darn.
I have a multi-dimensional application where today I have 3 tables with the
same field definitions (tblDimen_XXX, tblDimen_YYY, tblDimen_ZZZ) and have an
easy union query that laces the three tables together as one query that then
works into the logic of the rest of the application. But, I have a user
interface that will allow the users to simply copy one of the three, clear
the content, and make a fourth copy with a user-defined name (tblDimen_ABC
for example) and a fifth (tblDimen_MNO) and so on. When they have added the
additional tables, I need to automatically recreate the union query and I
don't need them (nor want them) at the native level.
I would appreciate any ideas you may have.
Thanks

 

[Guest]

Thanks -
How do you save the SQL statement as a query without going into the native
mode? How to you get the SQL to create a recordset as you wrote? Any
examples would help.
Thanks

 

[John Spencer]

You can use code to create a query and save it. For instance, the following
will create a query named TempQuery and open the query.

Public Sub showquery()
'Untested Sample code
Dim qdfAny As QueryDef
Dim dbany As DAO.Database
Dim strSQL As String

Set dbany = CurrentDb()
strSQL = "SELECT * FROM SomeTable"
Set qdfAny = dbany.CreateQueryDef("TempQuery", strSQL)
DoCmd.OpenQuery "tempquery"

End Sub

If you only want to display the results on a form, you could assign the SQL
string as the record source of a form. If I recall correctly, the length of
the string is limited to 2K characters so if your SQL string is longer than
that you would still need to create and store the query.

Recordsets are good for manipulating data in VBA, but generally are not so
good for displaying the data in the user interface.

 

[Guest]

If you are saying you need to be able to recreate the union query
dynamically, you can do that in VBA as you can with building any SQL string.
The way I usually do this so it can be used like any other querydef is to
create a "template" query that contains as much as the query as possible. It
will have to be syntactically correct to save, but that is really no probelm.
Once you have a query defined, you can read it into a string variable,
modify it, and write it back out the the querydef.

Dim strSQL As String

'Get the query into a variable
strSQL = CurrentDb.QueryDefs("MyTemplateQuery").SQL
'Modify the query

'Write it back
CurrentDb.QueryDefs("MyTemplateQuery").SQL = strSQL

Now the query is ready to use any way you can use a query of its type.

If you need to create a new table based on the Union Query, the easiest way
to do it would be to create a Make Table query that uses the Union Query as
its source.

So, basically you will need a form (or controls on an existing form) to get
the information you need to create the union query and the name of the new
table. Then you build the union query in VBA. Build the Make Table query in
VBA to get the table name, and run the Make Table.