none
Trying to insert recordset into Access table in one go RRS feed

  • 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

    Wednesday, April 18, 2018 5:34 PM

Answers

  • 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...

    • Marked as answer by ryguy72 Wednesday, April 18, 2018 8:18 PM
    Wednesday, April 18, 2018 7:05 PM

All replies

  • 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...

    Wednesday, April 18, 2018 5:43 PM
  • 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

    Wednesday, April 18, 2018 6:17 PM
  • 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...

    Wednesday, April 18, 2018 6:29 PM
  • 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

    Wednesday, April 18, 2018 6:41 PM
  • 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...

    • Marked as answer by ryguy72 Wednesday, April 18, 2018 8:18 PM
    Wednesday, April 18, 2018 7:05 PM
  • 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

    Wednesday, April 18, 2018 8:18 PM
  • Hi,

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

    Wednesday, April 18, 2018 8:32 PM