RUN UNION QUERY USING VBA??? 1

[jane30]

Hi,can I use VBA to run a union query in Access97 like this:

docmd.runsql("Select tbl.* from tbl union select tbl2.* from tbl2)

I tried and didn't work. Please help. I need to extract data from more than one table using union query, not table join. Thanks a lot.

 

[cmmrfrds]

In a union query the first select dictates the columns and data type that will be returned by the following select statements. Unless table 2 has the same number of columns and data types the select * will not work.

 

[VBslammer]

As far as I know, the 'RunSQL' action is reserved for action queries or data-definition queries.

If you're just opening a recordset, you should use the method that does just that: OpenRecordset()

Dim strSQL As String
Dim db as Database
Dim rst As Recordset

strSQL = "SELECT tbl.* FROM tbl UNION SELECT tbl2.* FROM tbl2;"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)

While Not rst.EOF
' do stuff
Wend VBSlammer