Is it possible to combine two recordsets using VBA / ADO?

Question

The goal is to join the Access table with the corresponding data from the SQL Server table. I would do this using linked tables in Access, but I ran into the Access BigDt problem (I would have a view created to distinguish BigInt as Int, but this is not an option now).

So, I tried to create two sets of records and join them in VBA / ADO. The left side of the connection will be an Access table with Season and WeekNum, and the right side of the connection will be an SQL Server table with Season and Weeknum and other data. This works fine until I try to create a third record set, which is the result of the connection (in this example, I did not try to make the connection, but only the first part of the connection, selecting Access from the record set). I get a type mismatch error in a string when I set ObjRecordset3 = "SELECT * FROM" and Access_Recordset.

Is it even possible to combine two sets of records? If so, how is this done?

Function Join() Dim SQL_Server_Connection As ADODB.Connection Set SQL_Server_Connection = New ADODB.Connection Dim SQL_Server_Query As String Dim SQL_Server_Recordset As New ADODB.Recordset Dim Access_Recordset As New ADODB.Recordset Dim ObjConnection As ADODB.Connection Set ObjConnection = CreateObject("ADODB.Connection") Dim ObjRecordset3 As New ADODB.Recordset ' Get data from Bump Table 3: Access_Recordset.Open "SELECT * FROM [Bump Table 3]", CurrentProject.Connection ' Open connection to SQL Server: SQL_Server_Connection_String = "DSN=MySQLServer" SQL_Server_Connection.Open SQL_Server_Connection_String ' Define the SQL Server query: SQL_Server_Query = "SELECT Season, WeekNum FROM TE" ' Populate the SQL_Server_Recordset: SQL_Server_Recordset.Open SQL_Server_Query, SQL_Server_Connection, adOpenDynamic, adLockOptimistic 'Join Access_Recordset (Table: Bump Table 3) to SQL_Server_Recordset (Table: TE) Set ObjRecordset3 = "SELECT * FROM " & Access_Recordset ' Type Mismatch error on this line Access_Recordset.Close Set Access_Recordset = Nothing SQL_Server_Recordset.Close Set SQL_Server_Recordset = Nothing SQL_Server_Connection.Close End Function 

* UPDATE *

I figured out how to get to the end goal, which was to get the account number list data in the Access table from SQL Server based on the account number that is common to both tables. Understanding that I can create a permanent temp table on SQL Server, I used a combination of DAO and ADO to get the values ​​from the Access table and create a temporary table. All I had to do was run a pass-through query that references the temp table. The only strange thing (which is not a problem at the moment) is that I create a temporary table and run a pass-through query in VBA, this setting works. But if I create a temporary table in VBA and double-click on the query, Access will report that the temporary table was not found. Anyway, here is the code:

 Public Sub Insert_Into_Access_From_ADO_Recordset_Using_PTQ_Simpler() Dim dbs As DAO.Database Set dbs = CurrentDb() Dim cnn As ADODB.Connection Set cnn = New ADODB.Connection Dim rst As ADODB.Recordset 'Open SQL Server Dim str_cnn As String str_cnn = "MYDSN" cnn.Open str_cnn ' Drop the temp table: Dim str_SQL_Drop_Temp_Table As String str_SQL_Drop_Temp_Table = "IF OBJECT_ID('tempdb..##BumpData','U') IS NOT NULL " str_SQL_Drop_Temp_Table = str_SQL_Drop_Temp_Table & " DROP TABLE ##BumpData " cnn.Execute str_SQL_Drop_Temp_Table ' Create the temp table: Dim str_SQL_Create_Temp_Table As String str_SQL_Create_Temp_Table = " CREATE TABLE ##BumpData " str_SQL_Create_Temp_Table = str_SQL_Create_Temp_Table & " " & "(" str_SQL_Create_Temp_Table = str_SQL_Create_Temp_Table & " " & " ID INT " str_SQL_Create_Temp_Table = str_SQL_Create_Temp_Table & " " & " , AccountNumber VARCHAR(Max)" str_SQL_Create_Temp_Table = str_SQL_Create_Temp_Table & " " & ")" cnn.Execute str_SQL_Create_Temp_Table ' Insert values from the Access table into the temp table ' by looping through the Access table as a recordset: Dim rst_DAO As DAO.Recordset Set rst_DAO = dbs.OpenRecordset("Bump Data") Dim str_SQL_Insert As String rst_DAO.MoveFirst With rst_DAO Do While Not rst_DAO.EOF 'str_Loan_Number_List = str_Loan_Number_List & "'" & Trim(rst![Loan Number]) & "'" & "," str_SQL_Insert = " INSERT INTO ##BumpData VALUES (" & rst_DAO![ID] & ",'" & Trim(rst_DAO![Loan Number]) & "') " cnn.Execute str_SQL_Insert .MoveNext Loop End With ' Run the pass-thru query which joins to the temp table: DoCmd.SetWarnings False DoCmd.RunSQL "SELECT * INTO [Bump Results] FROM [Bump PTQ]" DoCmd.SetWarnings True End Sub 

Answer 1

You reported that this line causes an error:

 Set ObjRecordset3 = "SELECT * FROM " & Access_Recordset 

Right side of the = sign = trying to combine the string with the ADODB.Recordset object. This is probably the immediate cause of the compilation error. However, this is not the only problem with this line. Set <recordset object> = "any string value" will not work. Finally, Access SQL does not support FROM <recordset object> for any type of record set object (ADO or DAO).

I think you should look for a simpler approach. In the following query, dbo_BigIntTable is an ODBC reference to a SQL Server table. It includes the bigint_fld field, whose SQL Server data type is BigInt. However, Access sees this field as a Text Type. Therefore, I can join it with the string equivalent of a long integer field ( tblFoo.id ).

 SELECT tblFoo.id, dbo_BigIntTable.bigint_fld FROM tblFoo INNER JOIN dbo_BigIntTable ON CStr(tblFoo.id) = dbo_BigIntTable.bigint_fld; 

Access query designer complained that it could not display this connection in Design View, but I managed to create a connection to SQL View, and it worked fine.