User Defined Function not allowed in Excel Query of Access DB?

[Jim Parsells]

Using Office 2003 Pro.
I wish to use a subset of data in an AccessDB Table as the data source for
an Excel PivotTable. This is normally easy and I have done it many times.
However, in this case one of the fields in the Table has several spellings
for the same entity. I need to generate a name mapping function to
standardize this field.
This is also simple. I define a VBA routine in Access to perform the mapping.
Now the Access Query has syntax (abbreviated here) like this:

SELECT AccountQuery.TransDate, NameMapper([AccountQuery]![Memo]) AS CurName,
....FROM AccountQuery ...

In this case, NameMapper is my VBA function defined in Access. Naturally, it
gives the proper results in Access.

Any reference, in Excel, to this Access Query fails complaining that
NameMapper is unknown.

I can work around this using VBA in Excel to perform the name mapping, but
that leads me to some fairly complex code in getting the PivotTable set up
and, more
difficult, to catching the PivotTable Refresh so that I can do the right
thing.

Before I get into that, I would like to know if there is a way to get
MSQuery to accept/use the Access VBA routine as defined in the Access Query?
In other words, I want to define the query in Access, using the name mapping
function, and then have Excel able to directly use that query as the data for
a PivotTable.

An alternative would be to get MSQuery to run the name mapping function as
defined in an Excel VBA module.

Since this one case is not too vital to me, I can work around it in other
ways, however, I can forsee other cases in which a user defined function in
Access would be very useful when used in a Access query providing data to
Excel.

 

[Joel]

I'm not sure if you are just using Excel Queries or you are using an Access
object like

set obj = getobject(C:\temp\database.mdb")

I usually try to get the macro running 1st in the application they are using
and then convert the macro to excel.

 

[Jim Parsells]

Just a regular query from Excel. The query and function run as expected in
Access. I am not using CreateObject. If MSQuery would handle the use of the
Access function, there would be no VBA required in Excel.

The desired behavior is: In Excel-Create a PivotTable using an external data
source, the Query as defined in Access. Finish setting up the PivotTable. Be
able to simply Refresh data on the PivotTable as more data is added to the
AccessDB.
--
Jim Parsells

I'm not sure if you are just using Excel Queries or you are using an Access
object like

set obj = getobject(C:\temp\database.mdb")

I usually try to get the macro running 1st in the application they are using
and then convert the macro to excel.

Using Office 2003 Pro.
I wish to use a subset of data in an AccessDB Table as the data source for
an Excel PivotTable. This is normally easy and I have done it many times.
However, in this case one of the fields in the Table has several spellings
for the same entity. I need to generate a name mapping function to
standardize this field.
This is also simple. I define a VBA routine in Access to perform the mapping.
Now the Access Query has syntax (abbreviated here) like this:

SELECT AccountQuery.TransDate, NameMapper([AccountQuery]![Memo]) AS CurName,
...FROM AccountQuery ...

In this case, NameMapper is my VBA function defined in Access. Naturally, it
gives the proper results in Access.

Any reference, in Excel, to this Access Query fails complaining that
NameMapper is unknown.

I can work around this using VBA in Excel to perform the name mapping, but
that leads me to some fairly complex code in getting the PivotTable set up
and, more
difficult, to catching the PivotTable Refresh so that I can do the right
thing.

Before I get into that, I would like to know if there is a way to get
MSQuery to accept/use the Access VBA routine as defined in the Access Query?
In other words, I want to define the query in Access, using the name mapping
function, and then have Excel able to directly use that query as the data for
a PivotTable.

An alternative would be to get MSQuery to run the name mapping function as
defined in an Excel VBA module.

Since this one case is not too vital to me, I can work around it in other
ways, however, I can forsee other cases in which a user defined function in
Access would be very useful when used in a Access query providing data to
Excel.

 

[Joel]

You may need to do this with a simple macro. Can you add an OR to your query
where each or is a different spelliing of the database item.

Just a regular query from Excel. The query and function run as expected in
Access. I am not using CreateObject. If MSQuery would handle the use of the
Access function, there would be no VBA required in Excel.

The desired behavior is: In Excel-Create a PivotTable using an external data
source, the Query as defined in Access. Finish setting up the PivotTable. Be
able to simply Refresh data on the PivotTable as more data is added to the
AccessDB.
--
Jim Parsells

I'm not sure if you are just using Excel Queries or you are using an Access
object like

set obj = getobject(C:\temp\database.mdb")

I usually try to get the macro running 1st in the application they are using
and then convert the macro to excel.

Using Office 2003 Pro.
I wish to use a subset of data in an AccessDB Table as the data source for
an Excel PivotTable. This is normally easy and I have done it many times.
However, in this case one of the fields in the Table has several spellings
for the same entity. I need to generate a name mapping function to
standardize this field.
This is also simple. I define a VBA routine in Access to perform the mapping.
Now the Access Query has syntax (abbreviated here) like this:

SELECT AccountQuery.TransDate, NameMapper([AccountQuery]![Memo]) AS CurName,
...FROM AccountQuery ...

In this case, NameMapper is my VBA function defined in Access. Naturally, it
gives the proper results in Access.

Any reference, in Excel, to this Access Query fails complaining that
NameMapper is unknown.

I can work around this using VBA in Excel to perform the name mapping, but
that leads me to some fairly complex code in getting the PivotTable set up
and, more
difficult, to catching the PivotTable Refresh so that I can do the right
thing.

Before I get into that, I would like to know if there is a way to get
MSQuery to accept/use the Access VBA routine as defined in the Access Query?
In other words, I want to define the query in Access, using the name mapping
function, and then have Excel able to directly use that query as the data for
a PivotTable.

An alternative would be to get MSQuery to run the name mapping function as
defined in an Excel VBA module.

Since this one case is not too vital to me, I can work around it in other
ways, however, I can forsee other cases in which a user defined function in
Access would be very useful when used in a Access query providing data to
Excel.

 

[Jim Parsells]

I could modify the query, but it would be very unweildly and difficult to
change. I plan to fix this one by adding a name mapping table to Access and
incorporating that into my Access query.
However, using user defined Access functions to Access queries is a handy
technique which works in Access but not in MSQuerys that use that view of the
data as their source. My real question is how to get MSQuery to accept the
use of such user defined functions in the Access query.
--
Jim Parsells

You may need to do this with a simple macro. Can you add an OR to your query
where each or is a different spelliing of the database item.

Just a regular query from Excel. The query and function run as expected in
Access. I am not using CreateObject. If MSQuery would handle the use of the
Access function, there would be no VBA required in Excel.

The desired behavior is: In Excel-Create a PivotTable using an external data
source, the Query as defined in Access. Finish setting up the PivotTable. Be
able to simply Refresh data on the PivotTable as more data is added to the
AccessDB.
--
Jim Parsells

I'm not sure if you are just using Excel Queries or you are using an Access
object like

set obj = getobject(C:\temp\database.mdb")

I usually try to get the macro running 1st in the application they are using
and then convert the macro to excel.

:

Using Office 2003 Pro.
I wish to use a subset of data in an AccessDB Table as the data source for
an Excel PivotTable. This is normally easy and I have done it many times.
However, in this case one of the fields in the Table has several spellings
for the same entity. I need to generate a name mapping function to
standardize this field.
This is also simple. I define a VBA routine in Access to perform the mapping.
Now the Access Query has syntax (abbreviated here) like this:

SELECT AccountQuery.TransDate, NameMapper([AccountQuery]![Memo]) AS CurName,
...FROM AccountQuery ...

In this case, NameMapper is my VBA function defined in Access. Naturally, it
gives the proper results in Access.

Any reference, in Excel, to this Access Query fails complaining that
NameMapper is unknown.

I can work around this using VBA in Excel to perform the name mapping, but
that leads me to some fairly complex code in getting the PivotTable set up
and, more
difficult, to catching the PivotTable Refresh so that I can do the right
thing.

Before I get into that, I would like to know if there is a way to get
MSQuery to accept/use the Access VBA routine as defined in the Access Query?
In other words, I want to define the query in Access, using the name mapping
function, and then have Excel able to directly use that query as the data for
a PivotTable.

An alternative would be to get MSQuery to run the name mapping function as
defined in an Excel VBA module.

Since this one case is not too vital to me, I can work around it in other
ways, however, I can forsee other cases in which a user defined function in
Access would be very useful when used in a Access query providing data to
Excel.