Problem Getting External Data in Excel, From Access

[billelev]

I am trying to import data into excel via a query through the functionality found at:

Data -> Import External Data -> New Database Query

Unfortunately, the query I am trying to import contains a user defined function, ClearingCost:

SELECT tbl_ParentOrder.ParentID, tbl_ParentOrder.Qty, [qty]*[costpershare] AS RawCost, ClearingCost([qty]) AS Cost
FROM tbl_ParentOrder, tblClearingCosts;

Excel does not like this.

Does anyone know of a way of importing a query that contains a user defined function, or a work-around?

Thanks!

[Stewart Ross]

Hi. I would turn this one around, so that instead of importing Access data into Excel using Excel's import facilities you use Access to export the query in Excel format to a specified file, then open the spreadsheet in Excel once it is exported from Access.

Access has no problem with exporting calculated fields, whether based on user-defined functions or those built-in.

-Stewart

[billelev]

Hi. I would turn this one around, so that instead of importing Access data into Excel using Excel's import facilities you use Access to export the query in Excel format to a specified file, then open the spreadsheet in Excel once it is exported from Access.

Access has no problem with exporting calculated fields, whether based on user-defined functions or those built-in.

-Stewart

A good suggestion, Stewart. My only hesitation is that it would be best to have Excel grab the data when needed, rather than relying on Access to output the data at a particular time/event. But I could certainly implement your suggestion.

[NeoPa]

You could use Application Automation to open the specific database and copy the data in. It's a lot more complicated though, and requires familiarising yourself with the techniques.

Otherwise, I'm afraid Excel (via MS-Query) cannot see the database itself for functions. It can't even use Nz() unless you explicitly add the Reference in the VBA window to MS Access. This is because MS-Query treats the database simply as a back-end data repository rather than an Access application with access to all the code.

PS. How complicated and / or involved is ClearingCost()?
Maybe it could be duplicated in SQL.