OLE Automation with Excel

[Agamemnon]

Hello,

I'm want to set-up an automation event from excel to access, but I don't have a clue how to start. The situation is as follows:

1) I have a Combobox (or Listbox) in excel and I want to pass the variable(s) selected into an access query.

2) Afterwards I want to return the results of the Access query into an excel worksheet.

What technique should I use to do the above operation. If someone could guide me a bit in the process with e.g. an example I would be very pleased.

Ideally as Access would only function as "data server" it would be best if access doesn't need to be opened physically.

Any Ideas?

Thx,
Aga

 

[richary]

If you don't want access to be openeed, it sound like the best way to do it would be from Excel querying the .mdb file directly (Microsoft Query) or through ODBC.

As regards the combo box, it can either reside directly on the spreadsheet, or on a custom dialog in Excel.

 

[Bat17]

You can use msQuery from excel to build the queries without having to use the ones in Access, this will probably be easier than trying to pass the variable to Access.
In Excel look at Data>Get External Data>New database Query....
I can't give you much direct help on it though as I have broken my copy of msQuery.

HTH

Peter