turbo charge this code??? - Part 1

[kkozar]

need help. Access 2003 dB with a couple processes that run awful slow. Here's one module that takes about 3-4 hours to run for 130 unique records to create about 400 externally stored files. 2 out of every three files are PDF. the other is Excel.

' Purpose : Contains procedures used in exporting data to files and sending files in email.
'---------------------------------------------------------------------------------------

Option Compare Database
Option Explicit

Private Const SMTP_SERVER As String = "smtpgate.petc.com"

'---------------------------------------------------------------------------------------
' Procedure : RunExport
' Author : xxxxx
' Date : 7/3/2008
' Purpose : Exports Scorecard or Statement and PODetail spreadsheets to files.
' ReportName is the report to be run
'---------------------------------------------------------------------------------------
'
Public Sub RunExport(ReportName As String)
On Error GoTo RunExport_Error

Dim rs As DAO.Recordset
Dim sFilePath As String
Dim sTempPDFPath As String
Dim I As Integer
Dim bContinue As Boolean

Set rs = CurrentDb.OpenRecordset("Select * From tblVendors WHERE Active = True;")

'Get the base path from the paths table
sFilePath = GetFilePath("Export")

'Get temp path from the paths table
sTempPDFPath = GetFilePath("TempPDFPath")

If ReportName = "rptFinalInvoice" Then
sFilePath = sFilePath & Format(Date, "mmddyy") & "_statements\"
Else
sFilePath = sFilePath & Format(Date, "mmddyy") & "\"
End If

If Dir$(sFilePath) = "" Then
If Not CreateWindowsDirectory(sFilePath) Then GoTo RunExport_Error
End If

bContinue = True

'loop through vendors, outputting files
Do While Not rs.EOF And bContinue

'Ouput PO Detail spreadsheet and Scorecard report to pdf
'if either returns false bail out of the process

bContinue = PODetail2Excel(rs("VENDID"), sFilePath & Format$(rs("VENDID"), "00000") & " Detail" & ".xls") And _
PrintPdf(ReportName, "[VendID]=" & rs("VENDID"), sTempPDFPath, sFilePath, Format$(rs("VENDID"), "00000") & " " & " Scorecard" & ".pdf") And _
PrintPdf("rptCharts", "[VendID]=" & rs("VENDID"), sTempPDFPath, sFilePath, Format$(rs("VENDID"), "00000") & " " & " Charts" & ".pdf")

rs.MoveNext

Loop

If bContinue Then
MsgBox "Export Complete."
Else
MsgBox "Export aborted before it was finished, please check the files in the export folder"
End If

RunExport_Error:

If Err.Number <> 0 Then
ProcessError Err.Number, Err.Description, "RunExport", "Module: modExports"
End If

On Error Resume Next

End Sub

*****go to Part 2 Thread************

[Nightwalker83]

It would be a good idea to wrap the above code in [code][/code] tags so that it is easier for people to read. Like you have done with your other post.

[kkozar]

done

[03myersd]

I have never gotten round to doing any work with databases (although it is the next thing on my agenda), but what I suggest is that you break the code down to find out which bit is the slow bit. Then work on optimizing that. I doubt many members of the forum would want to go through all the code between this thread and the other thread you created.

[TDQWERTY]

I can't remember anything besides indexing your field tblVendors.Active and using multithreading there.
If using multithreading, make sure you don't create more then 5 or 6 simultaneous threads while creating the pdfs, it seems you're using crystal reports to export them and by my experience it's kinda slow doing that.

Multithreading example Here

Assuming you're using crystal reports...
aw, and something else, one of the params is "[VendID]=", are you by change using selectionFormula? Because if so it also might explain the slow export since Crystal will read every record and display according to the selectionFormula you sent. Many ppl seems to understand exactly what are those params for. It might be a good idea to select all data you want using a [ "Select * From tblVendors into tmp_tblVendors WHERE Active = True AND VendID=" & rs("VENDID") ] and then, export the pdf.
Plus, you might also index VendID field so the query returns a faster result. Specially if you have loads of data inside one only table.

Sorry for the bad bad text. I'm sleepy and my primary language isn't English.