"Undefined function" error when accessing query with custom function using VB/DAO
I have an application in VB6 which works on an Access 97 database using DAO.
After discovering a bug with the datepart function I have included a custom function (saved in a module in the .mdb) to correctly calculate a week number.
However in the VB6 app any time I use SQL (i.e. to open a recordset) which uses a query with this custom function I get an "Undefined function" error.
As I understand this is because DAO cannot "see" the function as saved in the database and so cannot resolve the SQL (but it can see the built-in functions such as datepart).
Does anyone know how I can get around this problem?
I have tried writing the function into a .dll and referencing this in the database rather than having the code in a module in the .mdb but when I do this the query does not see the function in Access.