ADO recordset filter doesn't seem to stick!

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,912
Office Version
  1. 365
Platform
  1. Windows
Hi

I have a recordset from a select * query to an access DB table. I want to be able to filter the recordset (using filter property). The users enter the filter expression in a textbox. After that they have a choice to take the data into a pivot table, or a csv file. The code runs without a hitch (provided valid expression is entered), but when I check the outputs it still returns the unfiltered recordset.

Thoughts?

Code:
Private Sub cbtRun_Click()
    Dim objRecordset As ADODB.RecordSet
    Dim wkb As Workbook, wks As Worksheet
    Dim pvc As PivotCache
    Dim lngFile As Long, strFile As String
    Dim vararrData As Variant, lngRow As Long, lngCol As Long
    
    Set m_clsDB = New clsManageDB
    With m_clsDB
        .DBpath = Evaluate(ThisWorkbook.Names("str_const_DBPATH").RefersTo)
        Set objRecordset = .RunScript(Me.SQL)
    End With
    
    If CBool(Len(Me.tbxExpression.Text)) Then
        'MsgBox Me.tbxExpression.Text
        objRecordset.Filter = Me.tbxExpression.Text
    End If
    
    With Me
        If .Output = pvt Then
            Set wkb = Workbooks.Add
            '// delete all but 1 sheet
            Set wks = wkb.Sheets(1)
            Set pvc = wkb.PivotCaches.Add(xlExternal)
            Set pvc.RecordSet = objRecordset
            pvc.CreatePivotTable TableDestination:=wks.Range("A1")
        ElseIf .Output = CSV Then
            If Not CBool(Right(UCase$(.tbxFileName.Text), 3) = "CSV") Then
                MsgBox "No csv file, file name required!", vbExclamation
            Else
                'consider .txt with pipe delim
                lngFile = FreeFile
                strFile = .tbxFileName.Text
                Open strFile For Output As #lngFile
                    vararrData = Application.Transpose(objRecordset.GetRows)
                    For lngRow = LBound(vararrData, 1) To UBound(vararrData, 1)
                        For lngCol = LBound(vararrData, 2) To UBound(vararrData, 2)
                            Print #lngFile, "'" & vararrData(lngRow, lngCol) & "',";
                        Next lngCol
                        Print #lngFile,
                    Next lngRow
                Close #lngFile
            End If
        End If
    End With
    Set m_clsDB = Nothing
    Unload Me
End Sub
 
Last edited:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Thanks again Andrew.

I've decided to post back the snippet of code I've gone with because I don't think this problem has much coverage on the web and haven't seen it covered on our forum before.

So one again the problem is that 'exporting' a filtered recordset discards the filter and exports everything.

Preserving the recordset Filter:
Code:
    If CBool(Len(strFilterText)) Then
        objRecordset.Filter = strFilterText
        Set objStream = New ADODB.Stream
        With objRecordset
            .Save objStream, adPersistXML
            .Close
            .Open objStream
        End With
    End If

Exporting the recordset to pivot cache:
Code:
    Set wkb = Workbooks.Add
    Set wks = wkb.Sheets(1)
    Set pvc = wkb.PivotCaches.Add(xlExternal)
    Set pvc.RecordSet = objRecordset
    pvc.CreatePivotTable TableDestination:=wks.Range("A1")


Works a treat! :)

Further reading:
Recordset Object (ADO)
Stream Object (ADO)

Hope this helps someone.... somewhere... one day...
 
Upvote 0

Forum statistics

Threads
1,224,609
Messages
6,179,882
Members
452,948
Latest member
Dupuhini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top