Filter Table based on Cell Value

[Domenick]

I have an Excel (2007) worksheet that is linked to an MS Access (2003) table.
When I refresh the data, the entire table comes over. I would like to filter
this data based on the value of a cell in a different worksheet (same
workbook) so that it only brings in the data for the specified value. I would
also like to specify which fields I want to bring in (like I said, the whole
table comes over).

Example: My Access table has daily transaction records and a "transaction
date" field. I would like to only bring in records that have a "transaction
date" that is equal to the date entered in cell "A1" of another worksheet. If
I change the date in cell "A1", I would like to bring in just the records for
the new date the next time I "Refresh All."

Is there a simple way to specify this in Excel or (if necessary) a VBA
solution? I don't see a place where I can specify a criteria for the records
that I want to bring in.

Thanks.

 

[B Lynn B]

Here's the framework for a routine I use for quick extraction of Access data.
It can be adapted to pull in multiple filters.

Notes:
It's necessary to go to References (vba Tools menu) and select the
"Microsoft ActiveX Data Objects 2.7 Library" for this to work.

In my SQL string the clumps of multiple quote marks are to get the quote
character into my query because the field is a text field. I think there
might be a better way to do this using chr(xx) - whatever char is for ".

Quick simple cheat for getting the right SQL structure if you're not
conversant in it is to go into the db, build the query using the query user
interface, then switch to SQL view to see how it's constructed.

Sub GetCRM()

Application.ScreenUpdating = False

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SysID As String
Dim myQry As String

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data " & _
"Source=F:\Data\CRMGrpRecords.mdb;" _
& "Persist Security Info=False"

SysID = Range("RecordID").Value

myQry = "SELECT * FROM CRMgroups " & _
"WHERE (CRMgroups.SystemID=" & """" & SysID & """" & ");"

Set rs = New ADODB.Recordset
rs.Open myQry, cn
If Not rs.EOF Then
Range("TableTop").CopyFromRecordset rs
End If

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub