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