Increase Ms Access Insert Performance

Increase Ms Access Insert Performance

If you have now

S = "SELECT SomeId, SomeBoolean, SomeOtherBoolean " & _
    "FROM recTable WHERE someCriteria"
Set rec = DB.OpenRecordset(S)

change your statements into

"INSERT INTO TransportationDetails (TransportationId, SomeId) " & _
"SELECT " & vTransportationId & ", SomeId " & _
"FROM recTable WHERE someCriteria"

and

"UPDATE recTable SET SomeBoolean = SomeOtherBoolean WHERE someCriteria"

For performance, avoid looping over Recordsets where possible. Use SQL statements that operate on whole sets instead.

MS Access Insert Into Slow for Large Recordset (VBA)

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

How to increase performance for bulk INSERTs to ODBC linked tables in Access?

This situation is not uncommon when dealing with bulk INSERTs to ODBC linked tables in Access. In the case of the following Access query

INSERT INTO METER_DATA (MPO_REFERENCE) 
SELECT MPO_REFERENCE FROM tblTempSmartSSP

where [METER_DATA] is an ODBC linked table and [tblTempSmartSSP] is a local (native) Access table, the Access Database Engine is somewhat limited in how clever it can be with ODBC linked tables because it has to be able to accommodate a wide range of target databases whose capabilities may vary greatly. Unfortunately, it often means that despite the single Access SQL statement what actually gets sent to the remote (linked) database is a separate INSERT (or equivalent) for each row in the local table. Understandably, that can prove to be very slow if the local table contains a large number of rows.

Option 1: Native bulk inserts to the remote database

All databases have one or more native mechanisms for the bulk loading of data: Microsoft SQL Server has "bcp" and BULK INSERT, and Oracle has "SQL*Loader". These mechanisms are optimized for bulk operations and will usually offer significant speed advantages. In fact, if the data needs to be imported into Access and "massaged" before being transferred to the remote database it can still be faster to dump the modified data back out to a text file and then bulk import it into the remote database.

Option 2(a): Using Python and pandas

pyodbc with fast_executemany=True can upload rows much faster than INSERT INTO … SELECT … on a linked table. See this answer for details.

Option 2(b): Using a pass-through query in Access

If the bulk import mechanisms are not a feasible option, then another possibility is to build one or more pass-through queries in Access to upload the data using INSERT statements that can insert more than one row at a time.

For example, if the remote database was SQL Server (2008 or later) then we could run an Access pass-through (T-SQL) query like this

INSERT INTO METER_DATA (MPO_REFERENCE) VALUES (1), (2), (3)

to insert three rows with one INSERT statement.

According to an answer to another earlier question here the corresponding syntax for Oracle would be

INSERT ALL
    INTO METER_DATA (MPO_REFERENCE) VALUES (1)
    INTO METER_DATA (MPO_REFERENCE) VALUES (2)
    INTO METER_DATA (MPO_REFERENCE) VALUES (3)
SELECT * FROM DUAL;

I tested this approach with SQL Server (as I don't have access to an Oracle database) using a native [tblTempSmartSSP] table with 10,000 rows. The code ...

Sub LinkedTableTest()
    Dim cdb As DAO.Database
    Dim t0 As Single
    
    t0 = Timer
    Set cdb = CurrentDb
    cdb.Execute _
            "INSERT INTO METER_DATA (MPO_REFERENCE) " & _
            "SELECT MPO_REFERENCE FROM tblTempSmartSSP", _
            dbFailOnError
    Set cdb = Nothing
    Debug.Print "Elapsed time " & Format(Timer - t0, "0.0") & " seconds."
End Sub

... took approximately 100 seconds to execute in my test environment.

By contrast the following code, which builds multi-row INSERTs as described above (using what Microsoft calls a Table Value Constructor) ...

Sub PtqTest()
    Dim cdb As DAO.Database, rst As DAO.Recordset
    Dim t0 As Single, i As Long, valueList As String, separator As String

    t0 = Timer
    Set cdb = CurrentDb
    Set rst = cdb.OpenRecordset("SELECT MPO_REFERENCE FROM tblTempSmartSSP", dbOpenSnapshot)
    i = 0
    valueList = ""
    separator = ""
    Do Until rst.EOF
        i = i + 1
        valueList = valueList & separator & "(" & rst!MPO_REFERENCE & ")"
        If i = 1 Then
            separator = ","
        End If
        If i = 1000 Then
            SendInsert valueList
            i = 0
            valueList = ""
            separator = ""
        End If
        rst.MoveNext
    Loop
    If i > 0 Then
        SendInsert valueList
    End If
    rst.Close
    Set rst = Nothing
    Set cdb = Nothing
    Debug.Print "Elapsed time " & Format(Timer - t0, "0.0") & " seconds."
End Sub

Sub SendInsert(valueList As String)
    Dim cdb As DAO.Database, qdf As DAO.QueryDef
    
    Set cdb = CurrentDb
    Set qdf = cdb.CreateQueryDef("")
    qdf.Connect = cdb.TableDefs("METER_DATA").Connect
    qdf.ReturnsRecords = False
    qdf.sql = "INSERT INTO METER_DATA (MPO_REFERENCE) VALUES " & valueList
    qdf.Execute dbFailOnError
    Set qdf = Nothing
    Set cdb = Nothing
End Sub

... took between 1 and 2 seconds to produce the same results.

(T-SQL Table Value Constructors are limited to inserting 1000 rows at a time, so the above code is a bit more complicated than it would be otherwise.)

How to increase performance for bulk INSERTs to ODBC linked tables in Access for multiple columns?

Basically, you got the idea and the code is correct.

But there are some other things you must consider:

1. If rst!NEW_COLUMN is empty, you pass an empty string to the SQL command and it will cause an error.

   e.g. error causing SQL-string:

   INSERT INTO METER_DATA (MPO_REFERENCE,NEW_COLUMN) VALUES (3, )

   Check if NEW_COLUMN is empty before building the SQL string

2. If NEW_COLUMN is a string value, it must be set between single quotation marks. This would look like that:

   valueList = valueList & separator & "(" & rst!MPO_REFERENCE _
& ",'" & rst!NEW_COLUMN & "')"

The second consideration will probably also solves the first consideration, because it changes ...VALUES (3, )... to ...VALUES (3, '')... which provides an empty string insert.

I assume, MPO_REFERENCE works without surrounded single quotation marks because it is a number.

mysql linked tables to ms access via ODBC performs poorly when insert update delete

Under some circumstances Access will actually send one native SQL statement for each row when executing a single Access SQL statement against an ODBC linked table. If you were to enable the general_log on the MySQL server you would see that, unfortunately, the Access SQL statements

INSERT INTO LinkedTable ... SELECT ... FROM LocalTable

and

DELETE * FROM LinkedTable

are two such cases.

When dealing with large numbers of rows and/or slow connections it may be necessary to use workarounds involving pass-through queries. For example, say you had an ODBC linked table named LinkedTable in Access that pointed to a MySQL table named RemoteTable. If

Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute "DELETE * FROM LinkedTable"
Set cdb = Nothing

proves to be too slow then this might be considerably faster

Dim cdb As DAO.Database, qdf As DAO.QueryDef
Set cdb = CurrentDb
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = cdb.TableDefs("LinkedTable").Connect
qdf.SQL = "TRUNCATE TABLE RemoteTable"
qdf.ReturnsRecords = False
qdf.Execute
Set qdf = Nothing
Set cdb = Nothing

A sort-of similar workaround for INSERT ... SELECT can be found in a related answer here.

How to speed up tables transfer between Access and SQL Server using VBA?

Why are functions like OPENDATASOURCE or OPENROWSET are blocked? Do you work in a bank?

I can't say for sure which solution is the absoute fastest, but you may want to consider exporting all Access tables as separate CSV files (or Excel files), and then run a small script to load each of those files into SQL Server.

Here is some VBA code that saves separate tables as separate files.

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
For Each obj In dbs.AllTables
If Left(obj.Name, 4) <> "MSys" Then
DoCmd.TransferText acExportDelim, , obj.Name, obj.Name & ".csv", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, obj.Name, obj.Name & ".xls", True
End If
Next obj

Now, you can very easily, and very quickly, load CSV files into SQL Server using Bulk Insert.

Create TestTable

USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
DROP TABLE CSVTest
GO

https://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

Also, you may want to consider one of these options.

https://www.online-tech-tips.com/ms-office-tips/ms-access-to-sql-database/

https://support.office.com/en-us/article/move-access-data-to-a-sql-server-database-by-using-the-upsizing-wizard-5d74c0df-c8cd-4867-8d07-e6e759d72924

---

Related Topics