Get Filtered Access Data

[rumshar]

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.

 

[Debaser]

How are you getting the Access data into Excel?

 

[Nebu]

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

 

[rumshar]

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=>



This is what I am getting,


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

 

[Debaser]

What's the SQL for the query?

 

[rumshar]

Here is the SQL of the query.

SELECT *
FROM 1Main
WHERE ((([1Main].Client) Like "*BLR"))
ORDER BY [1Main].Status_1;

 

[Debaser]

I suspect the where clause is the problem. Change the * to % and try the extract again

 

[rumshar]

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.

 

[Debaser]

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.

 

[rumshar]

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.

 

[Debaser]

For example:

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

 

[rumshar]

Wow!!! It is working fine.
Tonnes of THANKS to you for helping me out.

Have a nice day!!!!

 

[Debaser]

You're welcome.

 

[Nebu]

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

 

[rumshar]

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.

 

[rumshar]

For example:

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?

 

[Debaser]

I don't follow. The code does run the query to obtain the recordset.