• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get Filtered Access Data

rumshar

Member
Hi All,
Is there a way to get the filtered access data(filtered rows) in excel? As of now I am importing everything and delete the unwanted rows in excel. Since the data is huge, it is very slow.

I am looking for a VBA code which brings filtered data from access to excel.

Thank you very much.
 
Hi:

The easiest way is to build a query in access to filter for the data you need and export it into excel using the data connection wizard.

Thanks
 
Thanks for all the replies. I was bit busy so couldn't respond. Actually queries are already there but when I try to connect to that query it doesn't fetch any data but gives me few blank rows in an excel table.

This is how I am trying to retrieve data:
From Excel Data=>From Access=>
upload_2016-5-11_12-16-36.png


This is what I am getting,
upload_2016-5-11_12-16-3.png

There are thousands of rows in the query.
The strange thing is few other queries like 'Qry_Bucket_Status' and 'Qry_Export_Full_Master' are working fine.

Hence I am trying to import filtered data from 1Main table so that I could finish work quickly.

With Regards
Rudra
 
Here is the SQL of the query.

SELECT *
FROM 1Main
WHERE ((([1Main].Client) Like "*BLR"))
ORDER BY [1Main].Status_1;
 
I suspect the where clause is the problem. Change the * to % and try the extract again
Hi Debaser,
The query is working fine within access and I can export data to excel as well but it is somewhat slow. Moreover access is developed/maintained by someone else and I prefer importing access data to excel rather than exporting access to excel.

In the SQL query, he had used all the columns( I am sure he didn't write the query using SQL, but with design view. Upon seeing the query in SQL I found that all the fields names were there, so for brevity, I removed them and entered an asterisk, as you had asked for it. I haven't made(can't make) any changes in Access.
 
Last edited:
That's not actually what I said. Access uses * as a wildcard, but the ANSI SQL standard is to use % and that is what Excel's external query expects too. If you can't change the SQL in the database, you'll need to use ADO or DAO to extract the data by specifying the SQL string you want.
 
That's not actually what I said. Access uses * as a wildcard, but the ANSI SQL standard is to use % and that is what Excel's external query expects too. If you can't change the SQL in the database, you'll need to use ADO or DAO to extract the data by specifying the SQL string you want.

Would you be able to help with the VBA codes? I know little bit of VBA but not to that extent.
 
For example:
Code:
Sub LoadData()
    Dim DB                    As Object
    Dim rst                   As Object
    Dim sDatabase             As String
    Dim sQuery                As String
    Dim n                     As Long

    ' change path to your database
    sDatabase = "C:\Testing\Database2.accdb"
    ' change query name

    sQuery = "Qry_Export_BLR_Master"
   
    Set DB = CreateObject("DAO.DBEngine.120").OpenDatabase(sDatabase)
    Set rst = DB.QueryDefs(sQuery).OpenRecordset

    If Not rst.EOF Then
        For n = 1 To rst.Fields.Count
            Cells(1, n).Value2 = rst(n - 1).Name
        Next n
        ActiveSheet.Range("A2").CopyFromRecordset rst
    End If
    rst.Close
    DB.Close

End Sub
 
Hi:

I know you got a solution, but if you build the query as a make table query and link that table using the wizard it will work just fine.

Thanks
 
Hi:

I know you got a solution, but if you build the query as a make table query and link that table using the wizard it will work just fine.

Thanks
Hi Thanks for the suggestion. Since Access DB is maintained by someone else, I can't make any changes there.
 
For example:
Code:
Sub LoadData()
    Dim DB                    As Object
    Dim rst                   As Object
    Dim sDatabase             As String
    Dim sQuery                As String
    Dim n                     As Long

    ' change path to your database
    sDatabase = "C:\Testing\Database2.accdb"
    ' change query name

    sQuery = "Qry_Export_BLR_Master"
 
    Set DB = CreateObject("DAO.DBEngine.120").OpenDatabase(sDatabase)
    Set rst = DB.QueryDefs(sQuery).OpenRecordset

    If Not rst.EOF Then
        For n = 1 To rst.Fields.Count
            Cells(1, n).Value2 = rst(n - 1).Name
        Next n
        ActiveSheet.Range("A2").CopyFromRecordset rst
    End If
    rst.Close
    DB.Close

End Sub
Hi Debaser,
I ran into another problem now. Although it is retrieving data but the query needs to be run before importing data to excel. Is it possible to refresh query/run query with VBA code before importing data?
If yes what that code would be?
 
Back
Top