Trying to insert recordset into Access table in one go

Question

I am trying to make the code shown below work.

Sub PullDataUsingADO()

Dim con As New ADODB.Connection
Dim recordset As New ADODB.recordset

con.ConnectionString = "Provider=SQLNCLI11;" _
   & "Server=Server_Name;" _
   & "Database=DB_Name;" _
   & "Integrated Security=SSPI;" _
   & "DataTypeCompatibility=80;" _
   & "MARS Connection=True;"
con.Open

Dim recordsaffected As Integer
Set recordset = con.Execute("select * from dbo.fn_ExtractRawData ('all','all','03/30/2018')", recordsaffected, adCmdText)

' Write records to table named 'RAW_DATA_IMPORT'
Set db = CurrentDb
db.Execute "INSERT INTO TBL_FR2052A_RAW_DATA_IMPORT (Agg_ID, AggID, Tgt_ID, SrcID_Description, SrcID, reportScope, [Currency], Converted, PID, product, SID, SID2, subProduct, subProduct2, CID, counterPartySector, maturityValue, marketValue, lendableValue, maturityBucket, collateralClass, collateralValue, CollateralCurrency, insured, trigger, Rehypothecated, forwardStartValue, forwardStartBucket, internal, internalCounterParty,primeBrokerage,treasuryControl,unencumbered,effectiveMaturityStart,effectiveMaturityEnd,effectiveMaturityBucket,customerNumber,Portfolio,DESC1,CompanyName,reportType,maturityDate,AsofDate,event,settlementMechanism,reasonableness_Customer,reasonableness_Sector,reasonableness_Company,reasonableness_Contract_DealNo) " & _
     "SELECT Agg_ID, AggID, Tgt_ID, SrcID_Description, SrcID, reportScope, [Currency], Converted, PID, product, SID, SID2, subProduct, subProduct2, CID, counterPartySector, maturityValue, marketValue, lendableValue, maturityBucket, collateralClass, collateralValue, CollateralCurrency, insured, trigger, Rehypothecated, forwardStartValue, forwardStartBucket, internal, internalCounterParty,primeBrokerage,treasuryControl,unencumbered,effectiveMaturityStart,effectiveMaturityEnd,effectiveMaturityBucket,customerNumber,Portfolio,DESC1,CompanyName,reportType,maturityDate,AsofDate,event,settlementMechanism,reasonableness_Customer,reasonableness_Sector,reasonableness_Company,reasonableness_Contract_DealNo " & _
     "FROM recordset"

con.Close
Set con = Nothing

End Sub

I am getting an error that says 'Microsoft Access could not find the input table or query 'recordset'. Make sure that it exists and that its name is spelled correctly'.  All I am trying to do is run a SProc in SQL Server and load the data into a table in Access.  I'm pretty sure this is close, but obviously I am missing something here. 

---

MY BOOK

Answer 1

Hi,

Just a guess but maybe because "recordset" is a *reserved* word in Access. If you can't rename the table, try enclosing it with square brackets "[recordset]."

Hope it helps...

Answer 2

No, that's not it.  I changed it to another name and I'm still getting the same result.  One thing that has to be wrong is that has to be wrong is that recordsaffected = -1.  I have over 84k records in the table I am querying. 

---

MY BOOK

Answer 3

Hi,

Just to make sure I understand it, you create a recordset and set it with data using:

Set recordset = con.Execute(...)

However, in your APPEND query, you're saying:

db.Execute "INSERT INTO... () SELECT... FROM recordset"

Did I read that correctly? If so, I don't think you and "SELECT" from a recordset object. It's probably why you're getting the "cannot find table or query" error because you need an actual table as the source of the data rather than a recordset object.

Hope it makes sense...

Answer 4

Yes, it's probably correct.  I'm calling a function on SQL Server and pulling out data based on 3 parameters that are passed into the function.  The function is not exposed, so I can't get to the actual script that does the work.  I don't know the table names that the function pulls from.  I can't just go to the table, or tables, and pull in data.  I need to load everything from the 'recordset', now called 'rawdata', into my Access table.

---

MY BOOK

Answer 5

So, if you're able to create a recordset object filled with data you pulled from SQL Server using a Stored Procedure, one way to populate your Access table is to loop through the recordset and add each record to your table. For example:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("TBL_FR2052A_RAW_DATA_IMPORT", dbOpenDynaset)

With rawdata
   Do While Not .EOF
      rs.AddNew
        rs!Agg_ID = !Agg_ID
        rs!AggID = !AggID
        ...
      rs.Update
      .MoveNext
   Loop
End With

rs.Close

Set rs = Nothing

Hope it helps...

Answer 6

Oh, yeah!  I totally forgot about that option.  I got it working and it's pretty fast, despite having to loop more than 84k times.  Thanks for the suggestion!!  Very helpful!!

---

MY BOOK

Answer 7

Hi,

You're welcome. Glad to hear you got it to work. Good luck with your project.