title: Possible to join two recordsets using VBA/ADO?
body: <p>The goal is to join an Access table with matching data from a SQL Server table. I would do this using linked tables in Access but I'm running into Access' BigInt problem (I would have a view created to cast BigInt as Int but that isn't an option right now). </p>
  
  <p>So I've been trying to create two recordsets and join them in VBA/ADO. The left side of the join would be the Access table with Season and WeekNum and the right side of the join would be the SQL Server table with Season and Weeknum and other data. This works fine until I try to create a third recordset that is the result of the join (in this example, I haven't tried to do a join, just the first part of the join by selecting from the Access recordset). I'm getting a Type Mismatch error on the line when I do Set ObjRecordset3 = "SELECT * FROM " &amp; Access_Recordset '.</p>
  
  <p>Is it even possible to join two recordsets? If so, how is this done?</p>
  
  <pre><code>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 " &amp; 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
  </code></pre>
  
  <p><strong>* UPDATE *</strong></p>
  
  <p>I figured out how to get to my ultimate goal which was to get data about a list of account numbers in an Access table from SQL Server based on the account number field which is common to both tables.  Realizing that I can create a persistent temp table on SQL Server, I used a combination of DAO and ADO to get the values from the Access table and create a temp table. All I had to do then is run the pass-through query which references the temp table. The only odd thing (which is not a problem at this point) is if I create the temp table and run the pass-through query in VBA, this setup works. But if I create the temp table in VBA and double-click on the pass-through query, Access says that the temp table can't be found. Anyway, here's the code:</p>
  
  <pre><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 &amp; " 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 &amp; " " &amp; "("
          str_SQL_Create_Temp_Table = str_SQL_Create_Temp_Table &amp; " " &amp; " ID INT "
          str_SQL_Create_Temp_Table = str_SQL_Create_Temp_Table &amp; " " &amp; " ,   AccountNumber VARCHAR(Max)"
          str_SQL_Create_Temp_Table = str_SQL_Create_Temp_Table &amp; " " &amp; ")"
  
          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 &amp; "'" &amp; Trim(rst![Loan Number]) &amp; "'" &amp; ","
                  str_SQL_Insert = " INSERT INTO ##BumpData VALUES (" &amp; rst_DAO![ID] &amp; ",'" &amp; Trim(rst_DAO![Loan Number]) &amp; "') "
                  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
  </code></pre>