Solved Recordset performance (1 Viewer)

[ProgramRasta]

Hi All

I am attempting to import certain columns from Excel into an Access table.

However, I'm getting severe delays in updating the table using the below code.

I was wondering if my code is inefficient, Access is just slow at these type of updates or if I'm missing a trick somewhere.

For reference, there are 4000 rows in my Excel spreadsheet.

r = 2

    Do While Excel.Range("A" & r).Value <> Empty
    
            field1 = Excel.Range("A" & r).Value
            field2 = Excel.Range("B" & r).Value
            field3 = Excel.Range("C" & r).Value
            
            Set ImportRS = dbs.OpenRecordset("table1", dbOpenDynaset)
            
            With ImportRS
                .AddNew
                .Fields("Field_1") = field1
                .Fields("Field_2") = field2
                .Fields("Field_3") = field3
                .Update
            End With
    
    r = r + 1
    
    Loop

Your wisdom is appreciated!

Many thanks

 

[Gasman]

Link to the worksheet and use a query

 

[Pat Hartman]

Four thousand rows shouldn't be a problem but action queries are almost always faster than VBA loops so Gasman's suggestion should solve the problem.

You are not doing any validation so there is no need to use VBA. Even with an append query, you might be able to do all the required validation.

 

[cheekybuddha]

If you wish to use the recordset method, open the recordset outside of the excel row looping.

At the moment you open the table for each row of the worksheet.

Your code should look more like:

  r = 2

  Set ImportRS = dbs.OpenRecordset("table1", dbOpenDynaset)
  With ImportRS
    Do While Excel.Range("A" & r).Value <> Empty
      field1 = Excel.Range("A" & r).Value
      field2 = Excel.Range("B" & r).Value
      field3 = Excel.Range("C" & r).Value
      .AddNew
        .Fields("Field_1") = field1
        .Fields("Field_2") = field2
        .Fields("Field_3") = field3
      .Update
      r = r + 1
    Loop 
    .Close
  End With

 

[Gasman]

You can also open a recordset append only, can you not?
Would that speed up things at all?

 

[Pat Hartman]

open the recordset outside of the excel row looping.

Missed that. That is why 4,000 records is so slow

 

[arnelgp]

looping through each Cells in Excel also make it slow.
you can, one time put the Range values to an Array and just loop through
the Array and update the recordset:

Dim last_row As Long
Dim values As Variant

'get the last non-blank row
last_row = Excel.Cells(Excel.Rows.Count, 1).End(-4162).Row
'put the range into an array
values = Excel.Range("a2:c" & last_row)

Set importrs = dbs.OpenRecordset("table1", dbOpenDynaset)

With importrs
    For r = 1 To UBound(values)
        .AddNew
        .Fields("Field_1") = values(i, 1)
        .Fields("Field_2") = values(i, 2)
        .Fields("Field_3") = values(i, 3)
        .Update
    Next
End With

 

[ProgramRasta]

looping through each Cells in Excel also make it slow.
you can, one time put the Range values to an Array and just loop through
the Array and update the recordset:

Dim last_row As Long
Dim values As Variant

'get the last non-blank row
last_row = Excel.Cells(Excel.Rows.Count, 1).End(-4162).Row
'put the range into an array
values = Excel.Range("a2:c" & last_row)

Set importrs = dbs.OpenRecordset("table1", dbOpenDynaset)

With importrs
    For r = 1 To UBound(values)
        .AddNew
        .Fields("Field_1") = values(i, 1)
        .Fields("Field_2") = values(i, 2)
        .Fields("Field_3") = values(i, 3)
        .Update
    Next
End With

Good morning.

First of all, thank you to all who contributed, your time and knowledge was appreciated.

The above solution made the import almost instantaneous.

Have a great day.

 

[arnelgp]

if ever you convert the Regular range in Excel to a Table, then you do not need
to know the "last row"

Dim values As Variant
values = Excel.ListObjects("Table1").DataBodyRange.Value