Recordset multi-table query failure

[Guest]

I am running a query in VBA that uses parameters and multiple data sets.
However, it is failing with a Operation Not Allowed When Object is Closed
error when I attempt to Open the RecordSet Any ideas?? Thanks in advance
for your time - Mike

'-- Start Code
rs As New ADODB.Recordset

strSQL = "SELECT tblMaster.sym, tblProbability.CustID, tblMaster.Company,
tblFormula.Group, "FROM tblMaster INNER JOIN (tblProbability INNER JOIN
tblFormula ON tblMaster.CustID = tblProbability.CustID WHERE
(((tblMaster.myDate)=#" & datMyDate & “# ))â€

rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'-- The Open above Fails
'-- End Code

 

[Guest]

Hi Mike

Check your inverted commas and brackets in the SQL string:
- the " before FROM shouldn't be there.
- you have unmatched parenthesis - 4 ('s but only 3 )'s.

Cheers.

BW

 

[Guest]

Yes, I notice the unmatched parenthesis after I posted. the real code is a
larger SQL str so I cut it down manually. I know the str is correct cause I
use a MsgBox to test it out first. The main problem I am having is with
trying to Open multiple tables is a recordset. Is that possible????

 

[Guest]

Hi Mike

No probs in opening multiple tables through a SQL command The command merely
returns a single recordset which is what you need.

Your coding is correct (i've copied it to my PC and it works fine) so I can
only assume the SQL statement isn't. Try copying the SQL code into a SQL
query - you will have to change the datMyDate etc. to real values and see if
it ruturns the table you want.

Cheers.

BW