Query from Access Database based on user selected parameters in Excel

[AG]

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

 

[arjen van der wal]

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

 

[AG]

Hi,

I will give it shot and let you know if it's working. Many thanks for
your help on this.

Regards
-AG