Filter Access query results based on selected information

[Drew]

I am a newby to VBA, but I could use some help with some code to filter data
imported from Access based on selected information from a cell within Excel.
For example, in cell A1 I have a drop-down list of vendor names, when I
select Acme from this list, I would the queried information (from Access) for
purchases ("Purchase" tab) to be updated with only purchases from Acme (or
what ever vendor I have chosen).

Any thoughts? Thanks!

 

[joel]

When pulling dta from Access you use a SQL (String query Language). th
VBA queries call this Commandtext.

You can easily see this by recording a macro and manually performin
the query by using the worksheet menu

Data - Import External Data - Import Data - New Database query.

The wizard allows you to specify a filter on the menu that has
boxes.


You can acutally see the SQL statement when you get to the last men
onin the wizrd (the one with finish) if you clcik the button edit quer
and then press finish. You will see in the query editor the SQL butto
(or in the menu).

The SQL is a string (or as VBA calls it Command Text) and you ca
modify the string.


If you don't want to perform a query you can open the access databas
using ADO or DBO methods and retrive what is call a recordset using th
SQL. You can look at the Access VBA help under ADO open, DBO open
recordset, or SQL.

any Access VBA method can be used in Excel VBA if you delcar th
correct reference in Excel VBA menu -tools - Reference

The common references you use to get access data are


1) Microsoft Access 11.0 Library Object (or latest verision on you
PC)
2) Microsoft ActivedX Data Object 2.8 Library (or latest verision o
your PC

 

[Drew]

Thanks! Any thoughts on how to get the results to update and filter based on
changing the value within a given cell. From the example below, I would like
the results of the query to return only purchases from Acme when I choose
Acme from a drop down in a different tab.

 

[joel]

Here is my recorded macro and changes I made

recorded macro

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=C:\temp\submission.mdb;DefaultDir=C:\temp;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5" _
), Array(";")), Destination:=Range("A1"))
CommandText = Array( _
"SELECT Submissions.Task_ID, Submissions.`Client Name`,
Submissions.`Effective Date`, Submissions.`Imp Mgr`, Submissions.`Due
Date`, Submissions.`Actual Date`, Submissions.`Date Difference`" &
Chr(13) & "" & Chr(10) & "FROM `C:\t" _
, _
"emp\submission`.Submissions Submissions" & Chr(13) & "" &
Chr(10) & "WHERE (Submissions.`Client Name`='Test')" _
)
Name = "Query from MS Access Database"
FieldNames = True
RowNumbers = False
FillAdjacentFormulas = False
PreserveFormatting = True
RefreshOnFileOpen = False
BackgroundQuery = True
RefreshStyle = xlInsertDeleteCells
SavePassword = True
SaveData = True
AdjustColumnWidth = True
RefreshPeriod = 0
PreserveColumnInfo = True
Refresh BackgroundQuery:=False
End With


Change to make easier to read. I also replace chr(13) and chr(10) with
vbcrlf
I also added a commar at the end of the FROM line because the string
was too long

With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;" &
_
"DSN=MS Access Database;" & _
"DBQ=C:\temp\submission.mdb;" & _
"DefaultDir=C:\temp;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5"),
_
Array(";")), _
Destination:=Range("A1"))


CommandText = Array( _
"SELECT Submissions.Task_ID," & _
"Submissions.`Client Name`, " & _
"Submissions.`Effective Date`, " & _
"Submissions.`Imp Mgr`, " & _
"Submissions.`Due Date`, " & _
"Submissions.`Actual Date`, " & _
"Submissions.`Date Difference`", vbCrLf & _
"FROM `C:\temp\submission`.Submissions Submissions", vbCrLf &
_
"WHERE (Submissions.`Client Name`='Test')")

Name = "Query from MS Access Database"
FieldNames = True
RowNumbers = False
FillAdjacentFormulas = False
PreserveFormatting = True
RefreshOnFileOpen = False
BackgroundQuery = True
RefreshStyle = xlInsertDeleteCells
SavePassword = True
SaveData = True
AdjustColumnWidth = True
RefreshPeriod = 0
PreserveColumnInfo = True
Refresh BackgroundQuery:=False
End With

Now I replace 'Test' with a reference to the worksheet

Set Pulldownbox = Sheets("sheet2").range("A1")
With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC;" &
_
"DSN=MS Access Database;" & _
"DBQ=C:\temp\submission.mdb;" & _
"DefaultDir=C:\temp;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5"),
_
Array(";")), _
Destination:=Range("A1"))


CommandText = Array( _
"SELECT Submissions.Task_ID," & _
"Submissions.`Client Name`, " & _
"Submissions.`Effective Date`, " & _
"Submissions.`Imp Mgr`, " & _
"Submissions.`Due Date`, " & _
"Submissions.`Actual Date`, " & _
"Submissions.`Date Difference`", vbCrLf & _
"FROM `C:\temp\submission`.Submissions Submissions", vbCrLf &
_
"WHERE (Submissions.`Client Name`='" & Pulldownbox & "')")

Name = "Query from MS Access Database"
FieldNames = True
RowNumbers = False
FillAdjacentFormulas = False
PreserveFormatting = True
RefreshOnFileOpen = False
BackgroundQuery = True
RefreshStyle = xlInsertDeleteCells
SavePassword = True
SaveData = True
AdjustColumnWidth = True
RefreshPeriod = 0
PreserveColumnInfo = True
Refresh BackgroundQuery:=False
End With