Using VBA to Integrate Excel/Acess 2003?

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Hi,

Right now I have a collection of Excel spreadsheets that access an
Access 2003 database via individual Access queries where Field1 criteria
is unique for each query. What I would like to do is replace all the
individual queries with one query with a field1 parameter that I can can
change in VBA code then run the query so that the Excel spreadsheet data
is dynamically updated. Can this be done? If anyone can prove an
example of a VBA program executing an Access 2003 query after specifying
a criteria value to update an excel spreadsheet's external data range I
would find this most helpful.

TIA,
Dan
 
You can specify the Parameter value from VBA using either DAO or ADO. Here's a DAO sample:

Dim rs As Recordset 'holds query resultset
Dim qdfParmQry As QueryDef 'the actual query object
Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs("Qry1")
qdfParmQry("Please Enter Code:") = 3

' or try this alternate method
' to pass the parameter

qdfParmQry![Please Enter City:] = "New York"
Set rs = qdfParmQry.OpenRecordset()
 
Back
Top