Help to delete a column in a recordset 1

[AlexTardif]

Hi,

I got an Excel 2003 worksheet that I want to fill with data from Oracle. My query is on 5 fields, I want to display 4 of them but, the fifth one is only for some work and calculations that updates the recordset. I don't want the fifth field to be displayed.

So, I want to hide, remove or delete the field from my recordset. If I try rst.Fields.delete(3), I get no error but, my field is still there. I've read on some forums to use the update method after the deletion. If I add rst.Fields.Update, I get a runtime error saying this object cannot execute this action.

Dim strSQL as String
dim rst as ADOBD.Recordset

strSQL = "SELECT FIELD1, FIELD2, FIELD3, FIELD4, FIELD5" & VBCRLF & _
"FROM MY_TABLE " & VBCRLF & _
"WHERE FIELD2 = 'MY_VALUE'"

'My connection "conn" comes from another procedure and it works because I get the data I want
rst.open strSQL conn adOpenForwardOnly

Set rst = CalcAndWorks(rst)

'I wanna get rid of the FIELD4
rst.close 'the recordset gotta be closed to execute the Delete
rst.Fields.Delete(3)
'rst.Fields.Update

'Reopen to copy into Excel
rst.open

ActiveCell.CopyFromRecordset rst

rst.close
set rst = nothing


Any help would be welcome!

Thanks!

Alex

 

[SkipVought]

hi,

the fifth one is only for some work

strSQL = "SELECT FIELD1, FIELD2, FIELD3, FIELD4"
If [some work] Then
    strSQL = strSQL & ", FIELD5"
End If
strSQL = strSQL & vbCrLf
strSQL = strSQL & "FROM MY_TABLE "
strSQL = strSQL & vbCrLf
strSQL = strSQL & "WHERE FIELD2 = 'MY_VALUE'"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Can't you just:

rst.open

ActiveCell.CopyFromRecordset rst

rst.close
set rst = nothing
[blue]
Columns("D:D").Delete Shift:=xlToLeft[/blue]

Or whatever column you don't want to have in Excel?

Have fun.

---- Andy

 

[AlexTardif]

Hi,

Thanks for your answers.

To Skip :

Let me define the work I gotta do with my field AFTER getting it.

IF NOT isnull(rst.Fields(2).Value) AND isnull(rst.Fields(4).Value) Then
    rst.Fields(4).Value = rst.Fields(0).Value
Else
    If NOT isnull(rst.Fields(4).Value) AND NOT isnull(rst.Fields(3).Value) Then
        Do While rst.Fields(4) <= Constant
            Select Case rst.Fields(3).Value
                Case 7
                    rst.Fields(4).Value = ...
                Case 5
                    rst.Fields(4).Value = ...
                Case 8
                    rst.Fields(4).Value = ...
                Case 9
                    rst.Fields(4).Value = ...
            End Select
        Loop
    End If
End If

So, as you can see, I NEED the field (Field4 in the query rst.Fields(3) in this code) to do my work. There is no condition to get it.


To Andy,

I like the way you see this. I wanted to get rid of it BEFORE displaying the field. But, getting rid of it AFTER the display is a really good idea.

I'll give it a try.

I'll post again later.

Thanks all!

Alex

 

[Andrzejek]

OK, how about this: just ask the data to be ready to display from your DB? Just ask (create a few Selects) with your logic from DB, something like:

strSQL = "SELECT [blue]FIELD5[/blue], FIELD2, FIELD3, FIELD4, FIELD5" & VBCRLF & _
         " FROM MY_TABLE " & VBCRLF & _
         " WHERE FIELD2 = 'MY_VALUE' " & VBCRLF & _
         [blue]" AND (FIELD5 IS NOT NULL AND FIELD3 IS NOT NULL) [/blue]" & VBCRLF 
strSQL = strSQL & " UNION "
strSQL = strSQL & " SELECT ... "

Dump all manipulation on the DB site, and when you get your recordset, it is ready to be displayed with no manipulation.

Just straight:
[tt]
ActiveCell.CopyFromRecordset rst
[/tt]

Have fun.

---- Andy

 

[Andrzejek]

On the second thought, why figth with the rst?
Why not simple - since you need some VBA code anyway - just do simple manipulation of you data like:

Dim col1 As String
Dim col2 As String
Dim col3 As String
Dim col4 As String
Dim i As Integer

strSQL = "SELECT FIELD1, FIELD2, FIELD3, FIELD4, FIELD5" & VBCRLF & _
         "FROM MY_TABLE " & VBCRLF & _
         "WHERE FIELD2 = 'MY_VALUE'"

rst.open strSQL conn adOpenForwardOnly

i = 1

Do While NOT rst.EOF
    IF NOT isnull(rst.Fields(2).Value) AND _
        isnull(rst.Fields(4).Value) Then
            col4 = rst.Fields(0).Value
    ElseIf 
      ...
    End If
    col2 = ...
    ....
    col3 = ...
    col4 = ...
    i = i + 1
    Range("A" & i & ":D" & i).Value = Array(col1, col2, col3, col4)
    rst.MoveNext
Loop
rst.close
set rst = nothing

You may - if you prefer - define your columns in an array. Read your rst record by record, assign what you want to display and drop it into Excel.

I know [tt]ActiveCell.CopyFromRecordset rst[/tt] is very handy to use, but that's whan you have your rst ready to be dropped straight into Excel, but if you need to manipulate it, I would do it this way. Acctually, I do it this way A LOT because almost all of our reports (~200 or so) are in Excel

Have fun.

---- Andy

 

[AlexTardif]

Hello,

Thanks for all your ideas.

Since I have many queries in many worksheets, it's easier (and faster) for me to use the column deletion at the end of all the queries.

Still, it's strange to have a delete method and we're unable to use it.

Anyway, my worksheets now display well.

Thanks again!

Alex