CODEWITHSUNDEEP
vbaexcelappendadodbrecordset
L Kadue
L Kadue

Reputation: 83

VBA: add field with value to Recordset

I am accessing a DB through Excel VBA via ADODB.Connection and ADODB.Recordset. My query returns four fields of data. I want to a fifth field with the same value (ie 5) for every "row" of my recordset. I do not want to add this to the DB.

I want to do this as a field because sometimes my query returns five fields, and the fifth one replaces the 5 in the multiplication for column E.

My current code is such:

Dim MyConn As ADODB.Connection
Dim MyRecords As ADODB.Recordset
Set MyConn = New ADODB.Connection
Set MyRecords = New ADODB.Recordset
MyConn.Open location, username, password
MyRecords.ActiveConnection = MyConn
MyRecords.CursorLocation = adUseServer
MyRecords.Source = query 'really long, not included here
MyRecords.Open , , 1, 1
Do While Not MyRecords.EOF
    Range("A2").Offset(cl, 0).Value = MyRecords.Fields.Item(0).Value
    Range("B2").Offset(cl, 0).Value = MyRecords.Fields.Item(1).Value
    Range("D2").Offset(cl, 0).Value = MyRecords.Fields.Item(2).Value
    Range("E2").Offset(cl, 0).Value = MyRecords.Fields.Item(3).Value * 5
    cl = cl + 1 'go to the next line
    MyRecords.MoveNext
Loop
MyRecords.Close
MyConn.Close

I tried appending a field after .Open, but that threw an error. I then attempted the following, based on similar questions, examining locals and vba documentation:

Set MyRecords = New ADODB.Recordset
With MyRecords
    .Fields.Append "MA", adNumeric, 19
    .Fields.Append "PN", adVarChar, 12
    .Fields.Append "Qty", adNumeric, 19
    .Fields.Append "Data", adNumeric, 19
    .Fields.Append "Mult", adTinyInt, , adFldUpdatable, 1
End With

MyRecords.ActiveConnection = MyConn
MyRecords.CursorLocation = adUseServer
MyRecords.Source = query 'really long, not included here
MyRecords.Open , , 1, 1

But this throws an error before the With ends.

tl;dr My SQL query retrieves four columns, I want to add a fifth column locally in vba, filled entirely with the value 5. How do I do this?

Upvotes: 2

Views: 5149

Answers (1)

JimT
JimT

Reputation: 169

Are you saying that your query asks for four fields but you want your recordset to contain a 5th field when the data is returned?

If that's what you want couldn't you do something like this (untested!!)?

Select
    Field_1,
    Field_2,
    Field_3,
    Field_4,
    SUM(5)
From
    Your_Table

In this way, I believe EVERY row should have the number 5, in the fifth column of the recordset.

Upvotes: 0

Related Questions