CODEWITHSUNDEEP
sqlexcelvbams-access
Haphy_Paphy
Haphy_Paphy

Reputation: 73

How to import a microsoft access query with input parameters into excel

I have to import a microsoft access query into Excel.

The issue that I have with the import is that the Microsoft Access query requires two input parameters, i.e current month and previous month.

Based on the input, the Access query will select certain values from a table that fit the criteria and then make certain calculations only for these values.

If I use the import function in Excel I receive an error which states that two inputs were expected but not given.

Any help would be appreciated greatly.

Thank you!

MS Access sql code is similar to this:

Select

table1.value,
table2.value,
table1.value * table2.value as product,

From(

select * 
(from table 1 where date = current month)

inner join

select *
(from table 2 where date = previous month))

(current and previous month are popup input variables)

Upvotes: 1

Views: 1578

Answers (1)

June7
June7

Reputation: 21380

The Access query object cannot have dynamic parameters. Excel will have to provide the criteria. Following is an example of Excel pulling data from Access.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open ""Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Users\June\LabData.accdb'"
strStateNum = InputBox("Enter State Number", "State Number")
rs.Open "SELECT * FROM GeoDataAll WHERE StateNum='" & strStateNum & "';", cn, adOpenStatic, adLockReadOnly

Instead of InputBox, can reference cell to provide parameter.

Your query in Excel like:

rs.Open "SELECT Q1.*, field1 * field2 AS Product FROM (" & _
           "(SELECT * FROM table1 WHERE [date] = " & cell for current month & ") AS T1 " & _
           "INNER JOIN " & _
           "(SELECT * FROM table2 WHERE [date] = " & cell for previous month & ") AS T2 " & _
           "ON T1.ID=T2.ID) " & _
        "AS Q1;", cn, adOpenStatic, adLockReadOnly

Then use CopyFromRecordset method to write the data to a range on worksheet.

Upvotes: 1

Related Questions