unable to search multiple tables

[jason_kelly]

Hi There,

I really need your help.

I cannot seem to be able to search both my tbl_active and tbl_inactive tables in my MDB file.

When I use the following code, it just message boxes me back all the records contained in tbl_active and not tbl_inactive.



Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "DBQ=" & dbPath & ";Driver={Microsoft Access Driver (*.mdb)};"

mySQL = " SELECT * FROM tbl_active, tbl_inactive "

rs.Open mySQL, cn, 1, 1

Do While Not rs.EOF
MsgBox rs(0)
rs.MoveNext
Loop


any help with this is greatly appreciated.

Thanks very much.

Jay

[CreganTur]

It's because SQL doesn't work that way. If you want to return all of the records in both tables and both tables have the exact same design, then you can perform a Union between the two tables. If they have different design, then you will have to figure out which Join type to use.

http://www.w3schools.com/sql/default.asp is a good resource for learning the basics of SQL.