MS Access Insert Into Slow for Large Recordset (VBA)

Question

I have a section of code which creates a new table and then attempts to copy the record set values into the table. The only problem is this it is quite slow and access shows the loading symbol whilst it is executing this insert section below. Currently this problem is occurring inserting 500 records, but I will need to insert around 10,000 to 20,000 when I get a final data set.

I = 1
DoCmd.SetWarnings False
RecordSet1.MoveFirst
Do While Not RecordSet1.EOF = True
    SQL = "INSERT INTO " & FullName & " ("
    For Each field In RecordSet1.fields()
        SQL = SQL & " " & Replace(field.Name, ".", "_") & ","
    Next field
    SQL = SQL & "ValidationCheck)"
    SQL = SQL & " VALUES("
    For Each field2 In RecordSet1.fields()
        SQL = SQL & "'" & field2.Value & "',"
    Next field2
    SQL = SQL & Matches(I) & ")"
    DoCmd.RunSQL (SQL)
    RecordSet1.MoveNext
    I = I + 1
Loop

What I want to know is, is there any way I can speed this up? Or are there better approaches? (What I am trying to do is create a table at run time with a unique set of fields from a RecordSet and add an extra column with a Boolean value stored in Match array for each Record). The creation works fine, but the insertion code above is very slow.

I would try to create a saved query if the performance is too slow using VBA. If you can write this in plain SQL, Access will save the query plan and you should see better performance. — Mark C., Apr 25 '16 at 12:16
Hi Mark, Thanks for the suggestion. I've actually tried that and it has increased the performance somewhat. Granted, I am losing the flexibility of the table structure however. Have you got any further performance enhancing ideas? — calh27, Apr 25 '16 at 12:43
I would do whatever you could to prepare the data and/or table and try to minimize the loops/string manipulation. I'm not sure what `Matches(I)` is either.. Are you able to set a timer and see where the performance hit is actually happening? — Mark C., Apr 25 '16 at 12:45

score 2 · Accepted Answer · answered Apr 25 '16 at 13:38

Yes, use DAO. So much faster. This example copies to the same table, but you can easily modify it so copy between two tables:

Public Sub CopyRecords()

  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
  Dim strSQL      As String
  Dim lngLoop     As Long
  Dim lngCount    As Long

  strSQL = "SELECT * FROM tblStatus WHERE Location = '" & _
                "DEFx" & "' Order by Total"

  Set rstInsert = CurrentDb.OpenRecordset(strSQL)
  Set rstSource = rstInsert.Clone
  With rstSource
    lngCount = .RecordCount
    For lngLoop = 1 To lngCount
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              ElseIf .Name = "Total" Then
                ' Insert default value.
                rstInsert.Fields(.Name).Value = 0
              ElseIf .Name = "PROCESSED_IND" Then
                rstInsert.Fields(.Name).Value = vbNullString
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
      End With
      .MoveNext
    Next
    rstInsert.Close
    .Close
  End With

  Set rstInsert = Nothing
  Set rstSource = Nothing

End Sub

score 0 · Answer 2 · edited May 23 '17 at 12:32

0

For multiple inserts in a loop, don't use SQL INSERT statements. Instead use a DAO.Recordset with .AddNew.

See this answer: https://stackoverflow.com/a/33025620/3820271

As positive side effects, your code will become better readable and you don't have to deal with the multiple formats for different data types.

For Each field In RecordSet1.Fields
     rsTarget(field.Name) = field.Value
Next field

edited May 23 '17 at 12:32

Community

1
1

answered Apr 25 '16 at 13:01

Andre

26,751
7
36
80

Thanks Andre, I'm a bit unclear on the solution. So you're suggesting copying everything in my recordset into a new one (rsTarget), so I can add my vba generated value in the Match array for each record? How do I go about writing the rsTarget record set to my new table? – calh27 Apr 25 '16 at 13:41

MS Access Insert Into Slow for Large Recordset (VBA)

2 Answers2

Linked