HOW TO RUN A UNION QUERY USING VBA

[jane30]

Hi, I'm Access97 user. I have a code running select count(*) statement from multiple tables as followings: (the names of these tables are the combination of [tblName] in "QryPF list" and [tbl1] in "TBL DATA&quot

Function test()
On Error GoTo test_Err
DoCmd.SetWarnings False
Dim db As Database, rst1 As Recordset, rst2 As Recordset
Set db = CurrentDb
Set rst1 = db.OpenRecordset("QryPF list&quot
Set rst2 = db.OpenRecordset("TBL DATA&quot
rst1.MoveFirst
Do Until rst1.EOF
DoCmd.RunSQL "select ' " & rst1("tblName&quot & " " & rst2("tbl1&quot & "' as TBL, count(*) " _
& " as count into [tblChk count] from [" & rst1("tblName&quot & " " & rst2("tbl1&quot & "];"

rst1.MoveNext
Loop
rst1.Close
DoCmd.SetWarnings True
test_Exit:
Exit Function
test_Err:
MsgBox Error$
Resume test_Exit

End Function

This code works perfectly. The result tbl [tblChk count] contains only one record. But what I want to do is to union all counts from those tables in one table. How to run a union query through vba code?

Thanks a bunch in advance. ;-)

 

[bustell]

Not quite sure what you are trying to do. Could you please explain it a little more detailed?
Pat B

 

[jane30]

basically, what I want to do is to write a vba code to run a union query which has variables in it as shown in my sample code.

Function test()
On Error GoTo test_Err

DoCmd.SetWarnings False

Dim db As Database, rst1 As Recordset, rst2 As Recordset
Set db = CurrentDb
Set rst1 = db.OpenRecordset("QryPF list&quot
Set rst2 = db.OpenRecordset("TBL DATA&quot

rst1.MoveFirst
Do Until rst1.EOF

DoCmd.RunSQL "select ' " & rst1("tblName&quot & " " & rst2("tbl1&quot & "' as TBL, count(*) " _
& " as Count from [" & rst1("tblName&quot & " " & rst2("tbl1&quot & "];"

rst1.MoveNext
DoCmd.RunSQL "union select ' " & rst1("tblName&quot & " " & rst2("tbl1&quot & "' as TBL, count(*) " _
& " as Count from [" & rst1("tblName&quot & " " & rst2("tbl1&quot & "];"

Loop
rst1.Close

DoCmd.SetWarnings True


test_Exit:
Exit Function

test_Err:
MsgBox Error$
Resume test_Exit

End Function

It returns an error msg: syntax error in From clause.


What did I do wrong? Thank you.