Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query from Access Database based on user selected parameters in Excel | Excel Programming | |||
Filter query results with a cell value | Excel Programming | |||
Run SQL against Access DB/Filter Results | Links and Linking in Excel | |||
Why are access query results different in Excel | Excel Discussion (Misc queries) | |||
Getting Information from a query in ACCESS | Excel Worksheet Functions |