Chris_Forsyth
asked on
"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.
Any help would be appreciated.
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.
Any help would be appreciated.
ASKER
Problem is that I have a situation like this (example):
Table1
Field1 : Product - Text
Field2 : Volume produced- Long
Query1
Uses table to do calculation on volume produced
In VB program, have SQL with Query1 (i.e. using the stored query, as saved in access). If I then have an action query using Query1 as the source and I try to run this under VB6, obviously the problem as discussed. Unfortunately I use stored queries from the access .mdb quite a bit in my application, is this bad practice???? This way I do the work in a stored query (i.e. calculations) and use that stored query in my application. So unfortunately I think I may be scuppered, even if I have the function in my VB application, as I am trying to use a stored query that has that function DAO will not be able to see it. Is this correct??
Table1
Field1 : Product - Text
Field2 : Volume produced- Long
Query1
Uses table to do calculation on volume produced
In VB program, have SQL with Query1 (i.e. using the stored query, as saved in access). If I then have an action query using Query1 as the source and I try to run this under VB6, obviously the problem as discussed. Unfortunately I use stored queries from the access .mdb quite a bit in my application, is this bad practice???? This way I do the work in a stored query (i.e. calculations) and use that stored query in my application. So unfortunately I think I may be scuppered, even if I have the function in my VB application, as I am trying to use a stored query that has that function DAO will not be able to see it. Is this correct??
post the SQL of the QUERY, from Access. What FUNCTION are you trying to execute?
POST the VB code as well.
AW
POST the VB code as well.
AW
ASKER
The function I am calling is called "WeekNumber" (below), this calls the function "Days" (also below). This is the function given by microsoft to cover the bug in the DatePart function (as in the knowledge based article below).
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q200/2/99.asp&NoWebContent=1
--------------------------
Public Function WeekNumber(InDate As Date) As Integer
On Error GoTo ErrorTrap
Dim DayNo As Integer
Dim StartDays As Integer
Dim StopDays As Integer
Dim StartDay As Integer
Dim StopDay As Integer
Dim VNumber As Integer
Dim ThurFlag As Boolean
DayNo = Days(InDate)
StartDay = WeekDay(DateSerial(Year(In
StopDay = WeekDay(DateSerial(Year(In
' Number of days belonging to first calendar week
StartDays = 7 - (StartDay - 1)
' Number of days belonging to last calendar week
StopDays = 7 - (StopDay - 1)
' Test to see if the year will have 53 weeks or not
If StartDay = 4 Or StopDay = 4 Then ThurFlag = True Else ThurFlag = False
VNumber = (DayNo - StartDays - 4) / 7
' If first week has 4 or more days, it will be calendar week 1
' If first week has less than 4 days, it will belong to last year's
' last calendar week
If StartDays >= 4 Then
WeekNumber = Fix(VNumber) + 2
Else
WeekNumber = Fix(VNumber) + 1
End If
' Handle years whose last days will belong to coming year's first
' calendar week
If WeekNumber > 52 And ThurFlag = False Then WeekNumber = 1
' Handle years whose first days will belong to the last year's
' last calendar week
If WeekNumber = 0 Then
WeekNumber = WeekNumber(DateSerial(Year
End If
ExitRoutine:
Exit Function
ErrorTrap:
' Note : to trap faults within this procedure
Err.Raise vbObjectError + 1, "WeekNumber", Err.Description
Resume ExitRoutine
End Function
Public Function Days(DayNo As Date) As Integer
On Error GoTo ErrorTrap
Days = DayNo - DateSerial(Year(DayNo), 1, 0)
ExitRoutine:
Exit Function
ErrorTrap:
' Note : to trap faults within this procedure
Err.Raise vbObjectError + 2, "Days", Err.Description
Resume ExitRoutine
End Function
--------------------------
The SQL I am running is an action query :
"INSERT INTO [tblAllBandageProductionBy
SELECT '" & strUserName & "' AS User, qryAllBandageProductionByR
FROM qryAllBandageProductionByR
The problem is that the source query "qryAllBandageProductionBy
If I open the .mdb file and run the query "qryAllBandageProductionBy
Like I said I tried compiling the weeknumber function in a .dll and referencing it from the access application as well, but that didn't work either.
The more I talk it through, the more I think I am going to have to re-design a bit of the application so I don't have dependance on queries held in access. Don't think I will be able to solve it with Access/DAO/VB.
Let me know what you think.
ASKER
Been reading the microsoft article and the other way I can resolve this is to check if the return from datepart is 53 and if so change this to 1. Just wary this won't cover all circumstances (plus it seems a bit untidy).
If you agree that this is the only (easy) way then I will do this.
If you agree that this is the only (easy) way then I will do this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Like you say, is complex. I had originally not relied on any user defined functions, but the datepart bug has changed that (thanks microsoft!!). Anyway, will do the "fiddle", and have to hope it works it most circumstances.
Cheers for your time.
Cheers for your time.
AW