Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query from Access Database based on user selected parameters in Excel
Hi All,
I store my raw data in access database. This database gets updated on daily basis with the information from previous day. I have a dashboard in excel for management reporting purpose. For the review purposes we also need to extract the raw data from Access database into Excel spreadsheet based on the queries. Is it possible to write the queries in such a way that the parameters (fields of the tables in Access Database) of the query can be defined by the user as cell values (using drop down lists etc.) in Excel and then using macro we can run the query based on those parameter values. I will appreciate if anyone can help me with the same. Thank you. Regards, -AG |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query from Access Database based on user selected parameters in Ex
Hi AG, One way to do it is to use comboboxes (I used the Activex controls) and have them on a worksheet. In this example I have two comboboxes and have string variables set to the values of those comboboxes. Sub ParameterQuery() Dim strParam1, strParam2, strSQL, strConnect As String strParam1 = Sheet1.cmbParam1.Value strParam2 = Sheet1.cmbParam2.Value strSQL = "SELECT " & strParam1 & ", " & strParam2 & ", " & _ " AMOUNT, UNITS " & _ "FROM TransactionsFRS" strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=S:\FINOPS\Data\TransQuery.mdb;" Dim rsData As ADODB.Recordset Set rsData = New ADODB.Recordset rsData.Open strSQL, strConnect, adOpenForwardOnly, _ adLockReadOnly, adCmdText If Not rsData.EOF Then Sheet1.Range("A1").CopyFromRecordset rsData Sheet1.UsedRange.EntireColumn.AutoFit Else MsgBox "No Records Returned", vbCritical End If rsData.Close Set rsData = Nothing End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query from Access Database based on user selected parameters inEx
Hi,
I will give it shot and let you know if it's working. Many thanks for your help on this. Regards -AG |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query from Excel to Access - Passing Parameters? | Excel Programming | |||
Run query to get data from Access based on user input | Excel Programming | |||
Run an Access Query from Excel VBA with Parameters | Excel Programming | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
How to do an Access Database Query in Excel | Excel Programming |