CODEWITHSUNDEEP
sqlexcelvbarecordset
Patrick Bender
Patrick Bender

Reputation: 467

Add data to ADODB Recordset dynamically

I wonder if it is possible to add data to an ADODB recordset using a list of named ranges without specifying the names on separate rows.

I have tried to evaluate the name but the recordset takes my variable as a name insted of evaluating it. This leads to an error as there is no name with my variable name in the database.

Set rst = New ADODB.Recordset

SqlStr = "SELECT * From " & table & ";"
rst.Open SqlStr, conn, adOpenDynamic, adLockOptimistic 'adLockPessimistic

If rst.EOF Then
    rst.AddNew
End If

namedCells = Array("orgNr", "name")

With rst
    For i = LBound(namedCells) To UBound(namedCells)
        MsgBox (namedCells(i) & " = " & Range(namedCells(i)))
        !orgNr = Range(namedCells(i)) 'works fine
        !namedCells(i) = "!orgNr" 'gives error du to "namedCells(i)" is not present in database
    Next i
End With

Upvotes: 2

Views: 879

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

The ! bang operator is just a shorthand notation for an implicit default member call.

When you do this:

Debug.Print rs!SomeField

What's really happening is this:

Debug.Print rs.[Default].[Default]("SomeField").[Default]

Three implicit default member calls - the first against the Recordset object is the Recordset.Fields property, which yields a Fields object reference, which itself has a default member Item, that accepts a String argument - the ! bang operator passes the identifier that follows it as the value for this string argument (that's why you need to enclose it in square brackets if it's not a valid VBA identifier name) - this Item member yields a Field object, which also has a default member, Value. So the fully spelled-out bang operator notation becomes:

Debug.Print rs.Fields.Item("FieldName").Value

Now, default member calls that are parameterized (/indexed) are usually considered fine, so this is perfectly ok as well:

Debug.Print rs.Fields("FieldName").Value

Anything else involves implicit default member calls and makes your code say one thing... and actually do another.

Note that these unqualified Range calls you have in that snippet, are implicitly referring to whatever the ActiveSheet is, and this is an extremely common source of bugs.

See Modern VBA Best Practices: Default Members for more info on default members and best practices around them (I wrote that article).

Upvotes: 3

EddiGordo
EddiGordo

Reputation: 686

You can try with .fields :

For i = LBound(namedCells) To UBound(namedCells)
    MsgBox (namedCells(i) & " = " & Range(namedCells(i)))
    rst!orgNr = Range(namedCells(i)) 'works fine
    rst.fields(namedCells(i)) = Range(namedCells(i))
Next i

Upvotes: 3

Related Questions