Using ADODB to print recordset to an Excel sheet

Question

I'm trying to use an ADODB connection to create a record set from a query, then copy the contents of that record set into a range of cells

Below is the outline of my code, but I keep getting errors

Run-time error '-2147467259 (80004005)' Unspecified error

(I've replaced my specific references with all caps dummy references)

Sub Subroutine()

'establish ADODB connection to retrieve data from TABLE

Dim objConnection As New ADODB.connection
Dim objRecordset As New ADODB.Recordset

With objConnection
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source = MYDATASOURCE" & _
    "Extended Properties = Excel 8.0;HDR = Yes;"
    .Open
End With

'use query to record data to Excel range
Set objRecordset = objConnection.OpenRecordset("Select * From TABLE Where FieldNm = NAME")

With objRecordset
.Open
.MoveFirst

Do Until .EOF
    Worksheets("WORKSHEET").Cells(14, 1) = objRecordset.Fields.Item("FieldNm")
    .MoveNext
Loop

End With

End Sub

The debug goes to the .Open in my With objConnection block. Before that I was having problems with the .MoveNext method.

Did you tried Worksheets("WORKSHEET").Range("A14").CopyFromRecordset objRecordset? — user8753746
– user8753746, Commented Nov 27, 2017 at 19:33
I usually do it like this: objRecordset.Open sSelectStatement, objConnection — braX
– braX, Commented Nov 27, 2017 at 19:35
Please provide actual query as this is not compatible with Excel workbooks: Select * From TABLE Where FieldNm = NAME. Also, wrap your sub in error handling to get specific message. — Parfait
– Parfait, Commented Nov 27, 2017 at 19:39
@Parfait my data is in an Excel table in another location, so TABLE is the name of my range. What exactly about my query is not compatible with Excel? — Phillip Hamilton
– Phillip Hamilton, Commented Nov 27, 2017 at 19:46

Parfait · Accepted Answer · 2017-11-27 20:12:54Z

1

Assuming your SQL is correctly specifying worksheet range, consider adjusting some items in and outside of With...End With block.

OpenRecordset is a DAO method. Use Recordset.Open for ADO
Remove the second .Open call
Remove the recordset name inside With
Loop through worksheet down the rows instead of reassign same cell
Use error handling for more informative error message to capture runtime exceptions

VBA

Sub Subroutine()
On Error Goto ErrHandle

    '...same as above...

    objRecordset.Open "Select * From TABLE Where FieldNm = NAME", objConnection

    With objRecordset
        .MoveLast
        .MoveFirst

        i = 0
        Do Until .EOF
            Worksheets("WORKSHEET").Cells(14 + i, 1) = .Fields("FieldNm")

            i = i + 1
            .MoveNext
        Loop    

        .Close
    End With

    objConnection.Close

ExitHandle:
    Set objRecordset = Nothing
    Set objConnection = Nothing
    Exit Sub

ErrHandle:
    Msgbox Err.Number & " - " & Err.Description, vbCritical, "RUNTIME ERROR"
    Resume ExitHandle
End Sub

answered Nov 27, 2017 at 20:12

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

YowE3K Over a year ago

If FieldNm is a non-numeric field, you may also need to use apostrophes around the value, i.e. Select * From TABLE Where FieldNm = 'NAME'.

Parfait Over a year ago

Indeed. OP seems to suggest query is OK and SQL is likely pseudo code placeholder.

YowE3K Over a year ago

It makes it really hard to help them when they do that. It's so much easier if they obfuscate their data by just changing alphanumeric characters to other alphanumeric characters, and numbers to other numbers, and leave all special characters untouched.

Phillip Hamilton Over a year ago

Thanks YowE3K, that's a good idea. I'll do that next time to try and preserve the types

Collectives™ on Stack Overflow

Using ADODB to print recordset to an Excel sheet

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related