Need query to run faster when called from vba

[kimpal]

Hello,
Its been a while since I did some VBA - I am running a block of code which parses a directory, checks some rules and imports a file then runs a query on the data. Then loops to the next file to check against rules, import, run the query, etc.

I ran it on a directory which contained only 3 files (2 that matched and 1 that did not) and it was taking a little over a minute per file (2K records total). Larger files are taking over 3 minutes per file to import and run query.

I am hoping someone can take a look at the code and advise if there is a more efficient way to write it to get faster performance of the import and particularly the query.

[START VBA code]
Function MULTI_IMPORT()
Set db = CurrentDb()
Path = [Forms]![frmNavigation]![NavigationSubform].[Form]![FileName]
myfile = Dir(Path & "\*.*")
Dim TBL As String
Dim Spec As String
Dim FT As String
Dim ACT As String
Dim IE As String
Dim sql As String
DoCmd.SetWarnings False
Dim FC As Integer
Dim FN5 As String
Dim ID As Date
Dim RD As Date

ID = [Forms]![frmNavigation]![NavigationSubform].[Form]![ImportDate]
RD = [Forms]![frmNavigation]![NavigationSubform].[Form]![ReportDate]

Do Until myfile = ""
i = 0
'CSV AIR
If Left(myfile, 5) Like "CSVAX" = True Then
Spec = "CSV_AIR"
TBL = "tblCSV"
FT = "CSV AIR"
ACT = "AIR"
IE = "ALL"
FN5 = "CSVAX"

'CSV Ocean
ElseIf Left(myfile, 5) Like "CSVSX" = True Then
Spec = "CSV_SEA"
TBL = "tblCSV"
FT = "CSV SEA"
ACT = "SEA"
IE = "ALL"
FN5 = "CSVSX"

'Search Panel Export
ElseIf Left(myfile, 5) Like "SSEAE" = True Then
Spec = "SearchPanelMasterOcean"
TBL = "tblSearchPanel"
FT = "SEARCH PANEL"
ACT = "SEA"
IE = "EXPORT"
FN5 = "SSEAE"

'Search Panel Import
ElseIf Left(myfile, 5) Like "SSEAI" = True Then
Spec = "SearchPanelMasterOceanI"
TBL = "tblSearchPanel"
FT = "SEARCH PANEL"
ACT = "SEA"
IE = "IMPORT"
FN5 = "SSEAI"

'Billing Panel
ElseIf Left(myfile, 5) Like "BILLX" = True Then
Spec = "BILLINGPANEL"
TBL = "tblBilling"
FT = "BILLING"
ACT = "ALL"
IE = "ALL"
FN5 = "BILLX"

'Job Status
ElseIf Left(myfile, 5) Like "JSSTA" = True Then
Spec = "JOBSTATUS"
TBL = "tblJOBSTATUS"
qry2 = "qryCLEAN_JOBSTATUS"
FT = "JOBSTATUS"
ACT = "SEA"
IE = "IMPORT"
FN5 = "JSSTA"

'Pending Upload
ElseIf Left(myfile, 5) Like "PENDU" = True Then
Spec = "PENDINGUPLOAD"
TBL = "tblPENDINGUPLOAD"
FT = "PENDING UPLOAD"
ACT = "ALL"
IE = "ALL"
FN5 = "PENDU"

'BW RDD1005 Billing Report
ElseIf Left(myfile, 5) Like "BWRDD" = True Then
Spec = "RDD1005"
TBL = "tblBWRDD1005"
FT = "BW RDD1005"
ACT = "ALL"
IE = "ALL"
FN5 = "BWRDD"

'WF KTDL Kewill To Do List
ElseIf Left(myfile, 5) Like "WFKTD" = True Then
Spec = "WFKTDL"
TBL = "tblWFKTDL"
FT = "WF KTDL"
ACT = "ALL"
IE = "ALL"
FN5 = "WFKTD"

'Pre-alert
ElseIf Left(myfile, 5) Like "PAXXX" = True Then
Spec = "Prealert"
TBL = "tblPrealert"
FT = "PREALERT"
ACT = "ALL"
IE = "ALL"
FN5 = "PAXXX"
End If


If FN5 = Left(myfile, 5) Then
DoCmd.TransferText acImportDelim, Spec, TBL, Path & "\" & myfile
sql = "UPDATE " & TBL & " SET " & TBL & ".Import_date = """ & ID & """, " & TBL & ".Report = """ & RD & """, " & TBL & ".FileType = """ & FT & """, " & TBL & ".Activity = """ & ACT & """, " & TBL & ".ImportExport = """ & IE & """, " & TBL & ".FileName = """ & myfile & """ WHERE (((" & TBL & ".Import_date) Is Null));"
CurrentDb.QueryDefs("qrytemp").sql = sql
DoCmd.OpenQuery "qryTemp", acViewNormal, acEdit

FC = FC + 1
End If

i = i + 1

FN5 = ""

myfile = Dir
Loop

DoCmd.SetWarnings True
MsgBox FC & " Files Imported", vbOKOnly, "Import Status"
End Function

 

[Andrzejek]

it is not going to make your code run faster, just a lot easier to read:

Select Case Left(myfile, 5)
  Case "CSVAX"
    Spec = "CSV_AIR"
    TBL = "tblCSV"
    FT = "CSV AIR"
    ACT = "AIR"
    IE = "ALL"
    FN5 = "CSVAX"
  Case "CSVSX"
    ....

End Select

Also, what do you get here:

...[blue]
Debug.Print sql[/blue]
CurrentDb.QueryDefs("qrytemp").sql = sql

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[kjv1611]

Some additional suggestions. First, I'd declare all the variables up front. I noticed at least a couple missing, so added them, and then moved the others I noticed up top. Next I'd indent each sub-section... so if within an IF-THEN clause, I'd indent everything one tab-space to the right. Also, adding whitespace where it makes sense helps to read the code.

Are you certain the slow-down is even from the query, and not from the number of files? I know that if you use VBA to process files, it can be a pretty slow process.

Here's my slightly cleaned up version of your code: (also using tek-tips [IGNORE]

[/IGNORE] tags - which can be accessed easily enough using the code block icon in the posting box nowadays:

Option Compare Database
Option Explicit

Function MULTI_IMPORT()
    Dim db As DAO.Database
    Dim Path As String
    Dim myfile As String
    Dim TBL As String
    Dim Spec As String
    Dim FT As String
    Dim ACT As String
    Dim IE As String
    Dim sql As String
    Dim FC As Integer
    Dim FN5 As String
    Dim ID As Date
    Dim RD As Date
    
    Set db = CurrentDb()
    Path = [Forms]![frmNavigation]![NavigationSubform].[Form]![FileName]
    myfile = Dir(Path & "\*.*")
    DoCmd.SetWarnings False
    
    ID = [Forms]![frmNavigation]![NavigationSubform].[Form]![ImportDate]
    RD = [Forms]![frmNavigation]![NavigationSubform].[Form]![ReportDate]
    
    Do Until myfile = ""
        i = 0
        'CSV AIR
        If Left(myfile, 5) Like "CSVAX" = True Then
            Spec = "CSV_AIR"
            TBL = "tblCSV"
            FT = "CSV AIR"
            ACT = "AIR"
            IE = "ALL"
            FN5 = "CSVAX"
        
        'CSV Ocean
        ElseIf Left(myfile, 5) Like "CSVSX" = True Then
            Spec = "CSV_SEA"
            TBL = "tblCSV"
            FT = "CSV SEA"
            ACT = "SEA"
            IE = "ALL"
            FN5 = "CSVSX"
        
        'Search Panel Export
        ElseIf Left(myfile, 5) Like "SSEAE" = True Then
            Spec = "SearchPanelMasterOcean"
            TBL = "tblSearchPanel"
            FT = "SEARCH PANEL"
            ACT = "SEA"
            IE = "EXPORT"
            FN5 = "SSEAE"
        
        'Search Panel Import
        ElseIf Left(myfile, 5) Like "SSEAI" = True Then
            Spec = "SearchPanelMasterOceanI"
            TBL = "tblSearchPanel"
            FT = "SEARCH PANEL"
            ACT = "SEA"
            IE = "IMPORT"
            FN5 = "SSEAI"
        
        'Billing Panel
        ElseIf Left(myfile, 5) Like "BILLX" = True Then
            Spec = "BILLINGPANEL"
            TBL = "tblBilling"
            FT = "BILLING"
            ACT = "ALL"
            IE = "ALL"
            FN5 = "BILLX"
        
        'Job Status
        ElseIf Left(myfile, 5) Like "JSSTA" = True Then
            Spec = "JOBSTATUS"
            TBL = "tblJOBSTATUS"
            qry2 = "qryCLEAN_JOBSTATUS"
            FT = "JOBSTATUS"
            ACT = "SEA"
            IE = "IMPORT"
            FN5 = "JSSTA"
        
        'Pending Upload
        ElseIf Left(myfile, 5) Like "PENDU" = True Then
            Spec = "PENDINGUPLOAD"
            TBL = "tblPENDINGUPLOAD"
            FT = "PENDING UPLOAD"
            ACT = "ALL"
            IE = "ALL"
            FN5 = "PENDU"
        
        'BW RDD1005 Billing Report
        ElseIf Left(myfile, 5) Like "BWRDD" = True Then
            Spec = "RDD1005"
            TBL = "tblBWRDD1005"
            FT = "BW RDD1005"
            ACT = "ALL"
            IE = "ALL"
            FN5 = "BWRDD"
        
        'WF KTDL Kewill To Do List
        ElseIf Left(myfile, 5) Like "WFKTD" = True Then
            Spec = "WFKTDL"
            TBL = "tblWFKTDL"
            FT = "WF KTDL"
            ACT = "ALL"
            IE = "ALL"
            FN5 = "WFKTD"
        
        'Pre-alert
        ElseIf Left(myfile, 5) Like "PAXXX" = True Then
            Spec = "Prealert"
            TBL = "tblPrealert"
            FT = "PREALERT"
            ACT = "ALL"
            IE = "ALL"
            FN5 = "PAXXX"
    End If
    
    If FN5 = Left(myfile, 5) Then
        DoCmd.TransferText acImportDelim, Spec, TBL, Path & "\" & myfile
        sql = "UPDATE " & TBL & " SET " & TBL & ".Import_date = """ & ID & """, " & TBL & ".Report = """ & RD & """, " & TBL & ".FileType = """ & FT & """, " & TBL & ".Activity = """ & ACT & """, " & TBL & ".ImportExport = """ & IE & """, " & TBL & ".FileName = """ & myfile & """ WHERE (((" & TBL & ".Import_date) Is Null));"
        CurrentDb.QueryDefs("qrytemp").sql = sql
        DoCmd.OpenQuery "qryTemp", acViewNormal, acEdit
    
    FC = FC + 1
    End If
    
    i = i + 1
    
    FN5 = ""
    
    myfile = Dir
    Loop
    
    DoCmd.SetWarnings True
    MsgBox FC & " Files Imported", vbOKOnly, "Import Status"
    
End Function

On declaring all your variables up front. You may or may not already know this, but if you do not specifically declare a variable, then it will by default be assigned to a variant variable which is one of the largest variable types there are. That also may slow thing down a little, so declaring it to string in advance may help a tad in performance, though I doubt it's the largest factor. I'd actually imagine the number of files could be a big part.

Another thought: If you are dealing with hundreds or thousands of files or more, I'd suggest setting this up to only process say 50 or 100 records at a time. That way in case Access is having a hard time churning through a large number of records of this type at a time, breaking it up may improve performance, besides making it less likely to crash.

Anyway, be sure to share what you end up doing in the end, whether you use any of these suggestions or not.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kimpal]

Thanks for the suggestions.
I have a question on the case statement. Does the code check all statements in the case before deciding which one is true or does it stop checking the cases once it returns a true? I agree it is easier to read so fine if it doesn't speed up the processing but want to ensure it won't have negative effect.

I also moved all my declarications to the top as Kjv1611 suggested.

To be honest I was running this fucntion on wifi at home, that could actually be the problem because it does run faster at work and when on the LAN.

 

[Andrzejek]

Select Case is the equivalent of If-Then-ElseIf-Elseif-EndIf statement, it’s just a lot easier to read.
And yes, Select Case ‘bails out’ as soon as it finds the True case, it does not process any other cases after that. The same as If-Then-ElseIf-Elseif-EndIf statement does.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.