VB - Access join statement

I am writing an application with a VB front end and Access back end. I usually code using SQL server and stored procedures, so I’m not sure if this is an Access issue or not. For some reason I cannot get this particular sql statement to run. If I replace it with a statement that does not contain a join it runs fine. I copy the statement and run it in access and it runs there. The error I get is: Method ‘Open’ of object’_Recordset’ failed. Any ideas would be greatly appreciated.

Function SqlGetAgeOfOnset(inIndex As Integer) As ADODB.Recordset

Dim rsFDAge As ADODB.Recordset
Set rsFDAge = New ADODB.Recordset


rsFDAge.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\VBProjects\Disease\dr.mdb;"

Dim strSqlStatement As String

'Open the recordset.
strSqlStatement = "SELECT Cross.DiseasecondId, Cross.AgeOfOnsetId, AgeOnset.Description, Cross.Text " & _
"FROM [Cross] INNER JOIN AgeOnset ON Cross.AgeOfOnsetId = AgeOnset.Id " & _
"Where (((Cross.DiseasecondId) = " & inIndex & ")) " & _
"ORDER BY AgeOnset.SortOrder"



rsFDAge.Open strSqlStatement, , adOpenStatic, _
adLockReadOnly, adCmdText

Set SqlGetAgeOfOnset = rsFDAge

End Function

This has nothing to do with the SQL statement, but is a problem with the way you are trying to open the connection.

Open the connection using an ADODB.Connection, then return the recordset against the connections execute method.

Also with the above code, before you exit the function, you should close rsFDAge