Access VBA导入超10万条记录TXT文件速度变慢求优化方案

Absolutely—query and import optimization will drastically cut down your import time. The root issue here is almost certainly your current row-by-row SQL insert approach, which creates massive overhead for Access. Let's break down why it's slow and the best fixes:

Why Your Current Method Is Slow #

Inserting each row individually with a separate SQL statement forces Access to:

• Process a transaction for every single row (by default, Access auto-commits each action query)
• Update all indexes, constraints, and validation rules on the target table 100,000+ times
• Handle lock contention and disk I/O for each tiny operation

Even if this worked fast before, as your database grows (more data, larger indexes) or system resources shift, this overhead balloons into the hour-long wait you're seeing now.

The Best Optimization Strategies (Ordered by Effectiveness) #

1. Use Access's Built-in Bulk Import Tools #

Access has optimized bulk import functions that are orders of magnitude faster than row-by-row inserts. The DoCmd.TransferText method is perfect for tab-delimited files:

Dim Sep As String
Sep = vbTab

' Import directly to your target table
DoCmd.TransferText _
    TransferType:=acImportDelim, _
    TableName:="YourTargetTable", _
    FileName:="C:\Path\To\Your\File.txt", _
    HasFieldNames:=True, ' Set to False if your TXT has no header row
    Delimiter:=Sep

This method batches operations, minimizes transaction overhead, and leverages Access's native bulk processing—you'll likely see import times drop back to minutes (or even less).

2. Bulk Import to a Temp Table First (If You Need Custom Logic) #

If you have to clean or transform data before inserting into the main table, import the raw data into a temporary table first, then use a single append query to move the cleaned data to your target table:

' Step 1: Import to temp table
DoCmd.TransferText acImportDelim, , "TempImportTable", "C:\YourFile.txt", True, Sep

' Step 2: Append cleaned data to target table
CurrentDb.Execute "INSERT INTO YourTargetTable (Col1, Col2, Col3, Col4) " & _
    "SELECT Trim(Col1), CDate(Col2), Val(Col3), Col4 FROM TempImportTable " & _
    "WHERE Col1 IS NOT NULL", dbFailOnError

' Step 3: Clean up temp table
DoCmd.DeleteObject acTable, "TempImportTable"

This way, the bulk of the work is done with optimized import, and the append is a single set-based operation instead of 100k individual inserts.

3. Disable Indexes/Constraints Temporarily #

If you must stick with row-by-row inserts (e.g., complex per-row logic), disable all non-essential indexes, primary keys, and constraints on the target table before starting the import, then re-enable them afterward:

' Disable indexes
CurrentDb.Execute "ALTER TABLE YourTargetTable DROP CONSTRAINT PrimaryKeyName", dbFailOnError
CurrentDb.Execute "DROP INDEX IndexName ON YourTargetTable", dbFailOnError

' ... Run your row-by-row inserts here ...

' Re-enable indexes/constraints
CurrentDb.Execute "ALTER TABLE YourTargetTable ADD CONSTRAINT PrimaryKeyName PRIMARY KEY (Col1)", dbFailOnError
CurrentDb.Execute "CREATE INDEX IndexName ON YourTargetTable (Col2)", dbFailOnError

Updating indexes for every row is a huge slowdown—disabling them cuts that overhead entirely, and rebuilding them once after import is much faster.

4. Wrap Row-by-Row Inserts in a Single Transaction #

If you can't avoid row-by-row, wrap all inserts in a single transaction to eliminate per-row commit overhead:

Dim db As DAO.Database
Set db = CurrentDb

db.BeginTrans ' Start a single transaction

' ... Loop through your TXT file, run INSERT statements here ...

db.CommitTrans ' Commit all changes at once
Set db = Nothing

This reduces disk I/O and transaction processing from 100k+ operations to just two.

Bonus Checks #

• Compact & Repair Your Database: Fragmented databases can slow down operations—run DoCmd.CompactRepair regularly.
• Local File Storage: Ensure both the TXT file and Access database are on a local drive, not a slow network share.
• System Resources: Close other applications to free up memory and CPU for the import.

内容的提问来源于stack exchange，提问作者Shubham Swarup