Filter Access query results based on selected information
 

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!
--
Drew

Filter Access query results based on selected information
 

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

You can easily see this by recording a macro and manually performing
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 6
boxes.


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

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


If you don't want to perform a query you can open the access database
using ADO or DBO methods and retrive what is call a recordset using the
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 the
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 your
PC)
2) Microsoft ActivedX Data Object 2.8 Library (or latest verision on
your PC)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147026

Filter Access query results based on selected information
 

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.
--
Drew


"joel" wrote:


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

You can easily see this by recording a macro and manually performing
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 6
boxes.


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

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


If you don't want to perform a query you can open the access database
using ADO or DBO methods and retrive what is call a recordset using the
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 the
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 your
PC)
2) Microsoft ActivedX Data Object 2.8 Library (or latest verision on
your PC)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147026

.

Filter Access query results based on selected information
 

Here is my recorded macro and changes I made

recorded macro

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"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(Arra y("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(Arra y("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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147026