Can MSQuery access multiple data sources?

[Griffyn]

Hi all,

I write lots of reports in Excel that use MSQuery to get data.

Whenever I need to write some SQL that gets data from multiple sources (I need to mix tables from an Access database with those from an ODBC source) I'm forced to use Access, because I can't seem to get MSQuery to access more than one data source at a time. Is there a way around this?

To get around this I've been using Access to construct the query, and then using MSQuery to pull the data from the saved Access query. This works really well, except when I need to filter out results based on data in the Excel workbook.

For example:

In Access I have a query with this SQL:

SELECT CodeField, DateField FROM LocalTable

UNION ALL
SELECT CodeField, DateField FROM ODBCTable

But in Excel, I just want data for one date, so in MSQuery, the SQL looks like

SELECT CodeField, DateField
FROM SavedAccessQuery
WHERE DateField = [DateParam]

And Excel prompts for the DateParam when the query is returned. The big problem is that ALL of the data for all dates is returned to MSQuery when then filters it out, resulting in a long time to return a small amount of data. How can I get around this?

Obviously in this example, it would be simpler to retrieve those tables separately into Excel and merge them there - but I need a proper solution because I have heaps of very complex SQL queries like this.

Many thanks!

 

[almir]

Hi,

You could link an excel file/sheet within an existing Access DB. Then, make a query connecting access tables with excel sheet - if possible.

However, excel part will become a part of access DB, and - therefore - you will access it through a single data source.

Regards,

 

[Griffyn]

Hi almir,

That's one way I hadn't thought of doing it. The parameter link cells are usually located on a macro sheet (yes, I still use Excel 4 macros), but it wouldn't be hard to move those to a new tabular sheet, and set it up with the parameter name in Column A and the parameter value in Column B.

The problem is though that the Excel workbook would need to be saved prior to querying the datasource (because Access would open it's own instance of the workbook to grab those parameter values) - and by design all of these reports are read-only.

 

[Griffyn]

bump - anyone got any thoughts on this?