ODBC Error 1 'Undefined Function'

[sametch]

I am trying to run a query in an Access Database from another program using ODBC.

The query contains a user defined function and works fine when I run it in Access.

When I try to run the same query externally via ODBC I get the following error:

"ERROR 1 - [Microsoft][ODBC Microsoft Access Driver] Undefined function 'replace_chr13' in expression"

If I replace my user defined function wit a standard Access function such as LCase, the query can be accessed externally fine!

Can anyone suggest a solution?

 

[KeithG]

I don't think you can use user defined functions in a pass through query. What I would do is make another query from the pass through query with the pass through being the recordset of your new query. You should be able to use your function there.

 

[sametch]

Thanks Keith

I fear you may be right, but I just tried your method of using the user defined function in one query and then creating a pass through query with it and I still get the same error.

Does anyone know a way of replacing a SQL Char(13) in a string with a "," without using a user defined function?

 

[KeithG]

Did you make a regular Access query with the user function from the pass through query? I believe that should work as I have done this before.

 

[sametch]

I created two queries, qryOne as follows:

SELECT tblProjects.CH_CODE, tblProjects.CH_NAME, replace_chr13([CH_DESCRIPTION]) AS Address
FROM tblProjects;

Then made another query based on this one (qryTwo) I then tried to connect to qryTwo externally using ODBC. qryTwo is as follows:

SELECT qryOne.*
FROM qryOne;

 

[KeithG]

query1 is your ODBC query correct?

 

[sametch]

Keith

I was using qryTwo as the ODBC one. If I use qryOne, that was how I started off when I first had the problem.

 

[KeithG]

The first query just brings the information to the database. The second qeury you make (with the first query as the recordset) will contain the the custom function.