Performance issue - SLOW - VBA & Access

[DADAZHU]

hi,everyone,

I have a performance issue while I use VBA in a spreadsheet to manipulate an Access database. Currently I need to check up to about 40K rows of records in a worksheet against ones in a table in the DB, I will delete those identified as duplicated data(or requires an update), then load the spreadsheet records to the DB. This needs to be done fortnightly I I just tested about 20K rows of record today with an empty table, which means no deleting is required for the routine, it took about 21mins to complete the routine, 1000 rows records(10 fields) per minute was processed in average. It seemed too slow to me. My codes are as following, I hope that I can speed up the process, or I am more than happy to learn another better way to meet the purpose. Your time and help will be appreciated.

Also, when I insert the 2ok rows of records in the table, the size of DB increased about 14mb. I am wondering if it is normal? if not, Do you need to have the DB compacted each time I run the routine?

Private Sub btnUpdateHoursInDB_Click()

Dim strCC As String
Dim wDate As Date
Dim rowNo As Long, i As Long

rowNo = UsedRowsInAColumn("B", Sheet6)

'Set date in 'Hours' tab.
If rowNo < 2 Then GoTo NoRecordInTab

Sheet6.Columns("B:B").NumberFormat = "dd/mm/yyyy"

With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

On Error GoTo ErrHandler
Call OpenCN
Call OpenRSTbl("tblHours")
Call OpenRS("SELECT CC FROM tblUnit")

'Check record in DB if exiting by comparing with two fields, 'Date'& 'CC'
'If records are existing, delete them.

Application.StatusBar = "Please wait while updating Database!"

For i = rowNo To 2 Step -1
    
    strCC = Sheet6.Range("G" & i).Value
    wDate = Sheet6.Range("B" & i).Value
    
    With rsTbl
        .Filter = "WDate = " & "#" & wDate & "#" & " AND CC = '" & strCC & "' "
        
        If Not .EOF Then     
            .Delete
            .Update
        End If
    End With
    
Next i

'Add new records, export new and latest records from Excel to access

For i = 2 To rowNo

    'Check CC before proceed the steps below
    strCC = Sheet6.Range("G" & i).Value
    rs.Filter = "CC = '" & strCC & "'"
    If rs.EOF Then GoTo 0 'CC was not found
    
    ''Proceed updating
    With rsTbl
        .AddNew
        .Fields("WDate").Value = Sheet6.Range("B" & i).Value
        .Fields("CC").Value = Sheet6.Range("G" & i).Value
        .Fields("ORD").Value = Sheet6.Range("H" & i).Value
        .Fields("ADD").Value = Sheet6.Range("I" & i).Value
        .Fields("CAS").Value = Sheet6.Range("J" & i).Value
        .Fields("OT").Value = Sheet6.Range("K" & i).Value
        .Fields("AGENCY").Value = Sheet6.Range("L" & i).Value
        .Fields("ADMIN").Value = Sheet6.Range("M" & i).Value
        .Fields("INDUCT").Value = Sheet6.Range("N" & i).Value
        .Fields("TRAINING").Value = Sheet6.Range("O" & i).Value
        .Update
    End With
    
0:
Next i

Call CloseRS
Call CloseRSTbl
Call CloseCN

With Application
    .StatusBar = vbNullString
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

MsgBox "Database has been updated successfully!"

Exit Sub

    
ErrHandler:
    MsgBox "Connection to DB failed!"

Err:
    Call CloseRS
    Call CloseRSTbl
    Call CloseCN
    With Application
        .StatusBar = vbNullString
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

End Sub

 

[obiron]

Hi Dadazhu.

I think you would get better performance by struturing your application as follows.

Import the data from Excel into a temporary table.

Create a query that does a JOIN between the target table and the imported table that gives you all the records in the target table and a matching record in the imported table. Prepare a DELETE statement based on this criteria. This will delete all of the records that exist in the imported table but leave ones that do not.

Now append the imported data to the target data.

You might also want to look at indexes on your target table; define a primary key that uniquely identifies each record. You may need more than one field in the key. not having indexes can be a performance killer on databases as Access has to scan every record every time for a potential match.

 

[DADAZHU]

Thanks, Obiron.

I will give it a go...