MS Access & Excel: Turning a query with dynamic parameters into something useful

Question

I got stuck in the problem beneath, because I don´t use Access or Excel much and I have some basic programming language. So here's the deal:

I just made a fairly simple database in MS Access (2007) with a nice query to retrieve data, depending on which parameters you pass. In Excel (2007), I have this big 'template' which basically has parameters for the query. These parameters change per column & per row!

Perhaps superfluously, e.g.

column A contains paramA (10 different options)
column B contains paramB (8 different options)
column C contains paramC (2 different options)

What I'd like to do is to fill this template with dynamic data from Access, minding the continously changing parameters. e.g.

column D contains Query (ParamA, ParamB, ParamC)

Best way to go I think is to make a (inline?) function that retrieves results from the query, also passing the parameters depending on the relative cell position. And this function is then copied as a normal inline excel function (like: SUM()).

I just don't know how to call /execute an MS Access query from inside an Excel Macro function.

Could someone help me with it? Thank you very much in advance!

Answer 1

A few notes.

Dim cn As Object
Dim rs As Object

''See: http://www.connectionstrings.com/access

strFile = "C:\Docs\AccessDB.mdb"
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";User Id=admin;Password=;"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT SomeField, OtherField FROM SomeTable " _
  & "WHERE SomeText='" & Range("A1") & "'"
rs.Open strSQL, cn

s = rs.GetString
MsgBox s

'' Or
Sheets("Sheet2").Cells(2, 1).CopyFromRecordset rs

Answer 2

To add to Remou's answer also see Modules: Sample Excel Automation - cell by cell which is slow and Modules: Transferring Records to Excel with Automation

Late binding means you can safely remove the reference and only have an error when the app executes lines of code in question. Rather than erroring out while starting up the app and not allowing the users in the app at all. Or when hitting a mid, left or trim function call.

This also is very useful when you don't know version of the external application will reside on the target system. Or if your organization is in the middle of moving from one version to another.

For more information including additional text and some detailed links see the "Late Binding in Microsoft Access" page