Function making Access crash hard.

User avatar
geedeearr
StarLounger
Posts: 52
Joined: 04 Feb 2010, 17:14
Location: Brookings, South Dakota

Function making Access crash hard.

Post by geedeearr »

Hi All,
I'm using Access 2007 on Windows XP, all updates/patches current.
I've written an application, that gathers MSSQL and server information from our production servers, and enters that data into another MSSQL database. That database is then linked to an Access frontend via ODBC. I've chosen to enter "raw" data into the database.
I've written an Access user defined function, to help me translate the MSSQL schedule information into "plain" English, for use in a couple of forms. That function, along with the following code, is attached in a text file (and is, Module modJobServerTranslator, in my database). In testing (again the following code) the code executes fine until it reaches and steps through Set rstTest = db.OpenRecordset(strSQL). Without hesitation, the "Microsoft Office Access has encountered a problem and needs to close. We're sorry.....etc." error pops up. I have tried placing the generated SQL statement into the sql window of a query and if I try to execute or save that query, the same result occurs. The translation function, in and of itself, works fine
So, I'm trying to do something that makes Access really angry and I'm blind to what it is.
Thank you.

Code: Select all

Sub TestTranslateSchedule()
On Error GoTo ErrHandler:

    Dim strFreqInterval As String
    Dim strFreqType As String
    Dim db As Database
    Dim rstTest As DAO.Recordset
    Dim strSQL As String
    
    Set db = CurrentDb()
    
    strSQL = "SELECT [JobServerJobScheduleID], " & _
                    "[JobServerJobID], " & _
                    "[JobScheduleName], " & _
                    "[JobServerJobName], " & _
                    "[JobScheduleJobCount], " & _
                    "[JobScheduleIsEnabled], " & _
                    "(TranslateSchedule([FrequencyTypes], [FrequencyRecurrenceFactor], [FrequencyInterval], [FrequencyRelativeIntervals]).FreqInterval) As calcFreqInterval, " & _
                    "(TranslateSchedule([FrequencyTypes], [FrequencyRecurrenceFactor], [FrequencyInterval], [FrequencyRelativeIntervals]).FreqType) As calcFreqType, " & _
                    "[FrequencySubDayTypes], " & _
                    "[FrequencySubDayInterval], " & _
                    "[JobScheduleActiveStartDate], " & _
                    "[JobScheduleActiveStartTimeOfDay], " & _
                    "[JobScheduleActiveEndDate], " & _
                    "[JobScheduleActiveEndTimeOfDay], " & _
                    "[JobScheduleUid], " & _
                    "[EnterDate], " & _
                    "[EndDate]"
    strSQL = strSQL & " FROM SQLInv_JobServerJobSchedules"
    
Debug.Print strSQL

    Set rstTest = db.OpenRecordset(strSQL) '****************Access chokes here***********************
    
    With rstTest
Debug.Print "Job Schedule Name: " & !JobScheduleName
Debug.Print "Frequency Interval: " & !calcFreqInterval
Debug.Print "Frequency Type: " & !calcFreqType
    End With

    'strFreqInterval = TranslateSchedule(1, 0, 0, 0).FreqInterval
    'strFreqType = TranslateSchedule(1, 0, 0, 0).FreqType
    
    'Debug.Print strFreqInterval
    'Debug.Print strFreqType
    
ExitHandler:
    On Error Resume Next
    'Close any open db or rst objects and set to nothing here
    rstTest.Close
    Set rstTest = Nothing
    db.Close
    Set db = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & _
           " in procedure TestTranslateSchedule of Module modJobServerTranslator", vbOKOnly, "TranslateSchedule"
    Resume ExitHandler
    Resume
End Sub
You do not have the required permissions to view the files attached to this post.
gary

Those who dance are considered insane by those who can't hear the music. - George Carlin                    Image

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Function making Access crash hard.

Post by HansV »

The problem is that your function returns a user-defined type. DAO can't handle that.
You'll have to create two separate functions, one that returns the FreqInterval and another that returns the FreqType.
That's less efficient, but I don't think you can avoid it.
Best wishes,
Hans

User avatar
geedeearr
StarLounger
Posts: 52
Joined: 04 Feb 2010, 17:14
Location: Brookings, South Dakota

Re: Function making Access crash hard.

Post by geedeearr »

Hi Hans,
Didn't even occur to me that DAO was incompatable that way.
And here I thought I was being so clever :groan:
Thank you.
gary

Those who dance are considered insane by those who can't hear the music. - George Carlin                    Image