Solved

"Undefined function" error when accessing query with custom function using VB/DAO

Posted on 2003-11-12
7
620 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:Chris_Forsyth
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9733906
from a VB6 program, you CANNOT use any functions that are defined in the Access modules.  You will need to copy the Access Module code into your VB 6 application.


AW
0
 

Author Comment

by:Chris_Forsyth
ID: 9738270
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??
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9738930
post the SQL of the QUERY, from Access.  What FUNCTION are you trying to execute?

POST the VB code as well.

AW
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:Chris_Forsyth
ID: 9739203

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(InDate), 1, 1)) - 1
  StopDay = WeekDay(DateSerial(Year(InDate), 12, 31)) - 1
  ' 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(InDate) - 1, 12, 31))
  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 [tblAllBandageProductionByRendement(Calc)] ( UserName, Bandage, Year, PeriodType, Period, Production )
SELECT '" & strUserName & "' AS User, qryAllBandageProductionByRendement.Bandage, qryAllBandageProductionByRendement.Year, qryAllBandageProductionByRendement.[Period Type], qryAllBandageProductionByRendement.Period, qryAllBandageProductionByRendement.Prod
FROM qryAllBandageProductionByRendement;"

The problem is that the source query "qryAllBandageProductionByRendement" uses the WeekNumber function (in one of its source queries).  This is saved in a module in the .mdb file (plus I have a copy in the VB6 application).
If I open the .mdb file and run the query "qryAllBandageProductionByRendement" (which is a select query) it runs okay within the access environment.  Also the insert SQL runs fine within the access environment.  It is only when I use DAO/VB6 that I cannot run the insert SQL.  I believe DAO cannot resolve "qryAllBandageProductionByRendement" when one of its sub queries contains the function (it cannot see the access modules).

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.
0
 

Author Comment

by:Chris_Forsyth
ID: 9739220
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.
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 125 total points
ID: 9739914
the problem is NOT that the query is held in Access, as that is GENERALLY the best approach.  The query, when stored in Access, has been 'pre-processed' and thus will always execute FASTER than the samea query passed as the direct SQL text from a VB program.  However, this assumes that the query DOES NOT rely on the use of USER-DEFINED functions.  If you MUST use a USER-DEFINED function, then the problem becomes very complex.

If your last comment will in fact solve the problem, then go for it.

AW
0
 

Author Comment

by:Chris_Forsyth
ID: 9740942
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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question