Access crashes while exporting -- stumped

[eng69]

I am working in Access VBA and have a procedure that loops 3 times doing a total of 6 queries. I am running office 2000. The problem is when it runs, access crashes, gives an exception in msaccess.exe in mswstr10.dll. The weird thing is that if i step through it, it completes with no errors.... I repeat, if i manually step through it, it works fine. I have searched and come up with nothing. the microsoft error reporting told me to update office, i did that, with no luck. I took it home and tried it on office 2003 with the same results.

This piece of code runs through 3 times, each time doing two queries. After one query, I do some things with excel to process the data (i have deleted that code for now to troubleshoot), delete the qrytemp sheet, save, close excel, do another query and transfer to excel, process it, delete qrytemp sheet, save, close, and loop all over again. When i open the excel file that it started with, there is only one sheet in it, indicating it made it to the second query but bomb before transfering the data.

Anybody have any suggestions??.. i am really at wits end here.

Thanks in advance for any input

Private Sub violationreport(path As String)
'procedure to create the fhwahp reports
Dim strSQL As String
Dim strQuery As String
Dim strTable As String
Dim strDate As String
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim sdate As Date
Dim endate As Date
Dim snumber As Integer
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Set objExcel = New Excel.Application
Dim progress_amount As Integer
Dim charts(3) As String
Dim chartcounter As Integer
Dim cellcounter As Integer
Dim rowcounter As Integer
Dim stationid As Integer
Dim Retval As Integer
Dim class As Integer
Dim dailycount As Integer
Dim shour As Integer
Dim sdate2 As Date
Dim datecounter As Single
Dim sitename As String
Dim row(32) As String
Dim pastecounter As Integer
Dim column As Integer
Dim rowcounter2 As Integer
Dim rowcounter3 As Integer
Dim columnt As Integer
Dim sdate3 As Date
Dim sdate1 As Date
Dim column2 As Integer
Dim column3 As Integer
Dim classcounter(3) As Integer
Dim xc As Integer
Dim weight(6) As String
Dim sname As String
class = 9
Retval = SysCmd(1, "Processing...", 1000)
weight(1) = "> 80"
weight(2) = ">= 88"
weight(3) = ">= 105.5"
weight(4) = "> 88"
weight(5) = ">= 96.8"
weight(6) = ">= 116.05"
classcounter(1) = 9
classcounter(2) = 10
classcounter(3) = 13

For xc = 1 To 3
class = classcounter(xc)
charts(0) = "Total Trucks 80 - 83.9 Kips"
charts(1) = "Total Trucks 84 - 87.9 Kips"
charts(2) = "Total Trucks > 88 Kips"
strQuery = "qryWIMPermAxle"
strTable = "WIMPermAxle"
strDate = "HHMMSS"
endate = DateValue(Me.dend.Value)
sdate = DateValue(Me.dstart.Value)
Set db = CurrentDb
snumber = getstationnumber()
sname = getstationname()
objExcel.Application.DisplayAlerts = False
strQuery = "violationreport"

'sql string for total for each month by year
strSQL = Left(db.QueryDefs(strQuery).SQL, Len(Trim(db.QueryDefs(strQuery).SQL)) - 3) & _
" WHERE WIMPermAxle.COLLECTION_DATE >= #" & sdate & "# AND " & _
"WIMPermAxle.Gross " & weight(xc) & " AND " & _
"WIMPermAxle.CL = " & classcounter(xc) & " AND " & _
"WIMPermAxle.STATION = " & snumber & " AND " & _
"WIMPermAxle.COLLECTION_DATE <= #" & endate & "# " & _
"GROUP BY WIMPermAxle.STATION, WIMPermAxle.COLLECTION_DATE, Hour(WIMPermAxle.HHMMSS) " & _
"ORDER BY WIMPermAxle.STATION, WIMPermAxle.COLLECTION_DATE, Hour(WIMPermAxle.HHMMSS);"

db.QueryDefs("qryTemp").SQL = strSQL
Set rs = db.OpenRecordset(strSQL)
DoCmd.TransferSpreadsheet acExport, , "qryTemp", path, True
objExcel.Workbooks.Open (path)
objExcel.Worksheets.Add

objExcel.Sheets("qrytemp").Delete
objExcel.ActiveWorkbook.Save
objExcel.Application.Quit
'sql string for total for each month by year
strSQL = Left(db.QueryDefs(strQuery).SQL, Len(Trim(db.QueryDefs(strQuery).SQL)) - 3) & _
" WHERE WIMPermAxle.COLLECTION_DATE >= #" & sdate & "# AND " & _
"WIMPermAxle.Gross " & weight(xc + 3) & " AND " & _
"WIMPermAxle.CL = " & classcounter(xc) & " AND " & _
"WIMPermAxle.STATION = " & snumber & " AND " & _
"WIMPermAxle.COLLECTION_DATE <= #" & endate & "# " & _
"GROUP BY WIMPermAxle.STATION, WIMPermAxle.COLLECTION_DATE, Hour(WIMPermAxle.HHMMSS) " & _
"ORDER BY WIMPermAxle.STATION, WIMPermAxle.COLLECTION_DATE, Hour(WIMPermAxle.HHMMSS);"


objExcel.Sheets("qrytemp").Delete
objExcel.ActiveWorkbook.Save
objExcel.Application.Quit

Next

Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

End Sub