Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access program | Excel Discussion (Misc queries) | |||
Is there a way to unload the loaded XLL file in Excel? Hi all, I amdebugging XLL link library using Visual C++. Everytime I rebuild the XLL, Ihave to close the whole Excel program and relaunch the Excel program again,and then load in the newly gene | Excel Discussion (Misc queries) | |||
Calling Access Program from Excel | Excel Programming | |||
Converting Access dll calls to Excel program | Excel Programming | |||
How to access a called sub program to edit? | Excel Programming |