Excel/Access Program
 

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

Excel/Access Program
 

Dan wrote ...
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.

Sounds like you are using MS Query (Data, Import External Data). If
so, there are two ways to go:

EITHER

- Add parameters to the SELECT query in each of the workbooks.

OR

- In the database, create a stored procedure ('parameterized stored
query' for the MS Access user) e.g.

CREATE PROCEDURE
MyStoredProc (
start_date DATETIME,
end_date DATETIME
)
AS
SELECT
RefID,
DateEffective,
Earnings
FROM
EarningsHistory
WHERE
DateEffective
BETWEEN start_date AND end_date
;

- In the workbooks (MS Query), replace the SELECT query with a call
to the stored proc e.g.

EXEC MyStoredProc '01 JAN 2003', '01 JAN 2004'

- Once you've got it working for the hard-coded values, replace them
with parameters in MS Query.

The second option requires more work but offers a better structure
e.g. if you need to change the SELECT query you only need DROP then
re-CREATE the stored proc, rather than recall all the workbooks.

For info on parameters in MS Query, see Dick Kusleika's site:

http://www.dicks-clicks.com/excel/Ex...htm#Parameters

But if you are using VBA code anyhow, you may want to check out using
ADO.

Jamie.

--