You can try the NextRecordset method of rs object. Build your select list separating with a semicolon like so:

strSQL= "SELECT * FROM Authors; " & _
"SELECT * FROM stores; " & _
"SELECT * FROM jobs"

And then fetch the next result set like so:
Set myRS = myRS.NextRecordset