Filtering OpenRecordSet

[dfarland]

I am trying to open an Access DB in an Excel VBA. This following code works well, but now I would like to filter the the records by one of the fields in the table "data". Can I add a SQL statement somehow to accomplish that?

Here is my simple example:

Sub GetTable2()
Dim rs As Recordset

Set rs = DBEngine.OpenDatabase("C:\Sales\StoreDB.mdb&quot.OpenRecordset("data&quot
Range("A1&quot.CopyFromRecordset rs

End Sub

Dean

 

[aine]

Hi dfarland

You Can Use A query to get a filtered recordset like this

Dim DbEngine As DataBase //DAO, Can Also use ADO
Dim rs As Recordset
Dim strSQL As String

Set DbEngine = OpenDatabase("C:\Sales\StoreDB.mdb&quot
strSql = "SELECT * FROM data ORDER BY /*ENTER FIELD HERE*/;"
Set rs = DbEngine.OpenRecordset(strSQL)

Or you Can use sort once you have the rs entered in Excel

xlSht.Cells.Sort Key1:=xlSht.Range("A1&quot, Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Hope this helps

Aine