Excel VBA: Copying one recordset to another

[MZsarko]

I have been beating my head against the wall on this one for a couple of days.

I'm trying to get a recordset from SQL based on a query using ADO, replace all but the first four fields of the single record, and write it back to a table in SQL. I can read the recordset from SQL and I can turn the range in the sheet into a recordset, but every time I try to edit the recordset from SQL it gives me an error, "Run-time error '438': Object doesn't support this property or method.
It's time to ask the more learned for some assistance or direction. Here's the code:

Sub WriteToSQL()
        Dim rsCon As Object
        Dim rsData As Object
        Dim strConn As String
        Dim strSQL As String
        Dim rngSrc As Range
        Dim rsSrcData As Object
        Dim xlXML As Object
        Dim fCount As Long




        strConn = "Driver={SQL Server Native Client 11.0};Server=CC000287\SQLEXPRESS;Database=Data1120;Trusted_Connection=yes"


        strCompNum = Sheets("E-1_TR").Range("A2")


        strSQL = "SELECT * FROM TR_2013 Where FileName = '" & ActiveWorkbook.Name & "';"


        'Read the data from SQL into a recordset
        Set rsCon = CreateObject("ADODB.Connection")
        Set rsData = CreateObject("ADODB.Recordset")
        rsCon.Open strConn
        rsData.Open strSQL, rsCon, 1, 3


        Set rngSrc = Sheets("E-1_TR").Range("I25:I464")


        'Read the data from rngSrc into a recordset
        Set rsSrcData = CreateObject("ADODB.Recordset")
        Set xlXML = CreateObject("MSXML2.DOMDocument")
        xlXML.LoadXML rngSrc.Value(xlRangeValueMSPersistXML)
        rsSrcData.Open xlXML


        'Add all the data from rsSrcData into rsData.
        rsSrcData.MoveFirst
        Do While Not rsSrcData.EOF
            For fCount = 0 To rsSrcData.Fields.Count - 1
                With rsData
                    .Edit
                    .Fields(fCount + 4).Value = rsSrcData.Fields(0).Value
                    .Update
                 End With
                    rsSrcData.MoveNext
            Next fCount
        Loop


End Sub

 

[MZsarko]

Re: Excel VBA: Copying one recordset to another(Solved)

I figured it out!
Once I adjusted the SQL permissions it ran fine.
A classic P.I.C.N.I.C situation...

Problem In Chair, Not In Code