Solved: Join RecordSets

[Atravis]

Hi everyone,
I have created two recordsets from a workbook. The recordsets contain the records from the used range from a sheet s5 and a sheet s10 providing the criteria is met. The code below creates the first two recordsets fine, however I am trying to Join these
two recordsets to create a third where s5.column(A) = s10.column(B) values. I am getting errors stating the objRecordset cannot be found. Any ideas?

Thanks

'set record set variables
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
'ser connection and recordset
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
Set objRecordset2 = CreateObject("ADODB.Recordset")
Set objRecordset3 = CreateObject("ADODB.Recordset")
Set objConnection = GetExcelConnection(ThisWorkbook.FullName)
objRecordset.Open "SELECT * FROM [" & s5.Name & "$" & s5.UsedRange.Address(False, False) & "] WHERE F8 LIKE '" & s1.Cells(6, "B") & "%'", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
objRecordset2.Open "SELECT * FROM [" & s10.Name & "$" & s10.UsedRange.Address(False, False) & "] WHERE F1 LIKE '" & s1.Cells(6, "B") & "%'", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
objRecordset3.Open "SELECT * FROM objRecordset RIGHT JOIN objRecordset2 ON objRecordset.F1=objRecordset2.F2", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

s11.Cells(1, "A").CopyFromRecordset objRecordset3



This gives the connection i am using.

'set up a connection (used with xls), set not to use headers
Public Function GetExcelConnection(ByVal Path As String, _
Optional ByVal Headers As Boolean = False) As Connection
Dim strConn As String
Dim objConn As ADODB.Connection

'set connection (use excel 8, mixed data as text and headers set off as standard)
Set objConn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Path & ";" & _
"Extended Properties=""Excel 8.0;IMEX=1;HDR=" & _
IIf(Headers, "Yes", "No") & """"

'open connection
objConn.Open strConn

'give back connection
Set GetExcelConnection = objConn
End Function

[Atravis]

for those concerned i solved this using;

'set record set variables
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
'ser connection and recordset
Set ObjConnection = CreateObject("ADODB.Connection")
Set ObjRecordset3 = CreateObject("ADODB.Recordset")
Set ObjConnection = GetExcelConnection(ThisWorkbook.FullName)
rs1 = "[" & s5.Name & "$" & s5.UsedRange.Address(False, False) & "]"
rs2 = "[" & s10.Name & "$" & s10.UsedRange.Address(False, False) & "]"
ObjRecordset3.Open "SELECT T1.*,T2.F3,T2.F4 FROM " & rs1 & " AS T1 LEFT JOIN " & rs2 & " AS T2 ON T1.F1=T2.F2 AND T1.F8=T2.F1 WHERE T1.F8 LIKE '" & s1.Cells(6, "B") & "%' AND T2.F1 LIKE '" & s1.Cells(6, "B") & "%'", _
ObjConnection, adOpenStatic, adLockOptimistic, adCmdText