VBA代码语法错误及SQL Server数据导入性能优化求助

Let's break down your two problems and get you back on track:

1. Fixing Runtime Error '-2147217900(80040e14)' #

This error almost always boils down to SQL syntax hiccups, missing objects, or permission gaps after migrating to a server. Here's what to check step by step:

• Validate your SQL statements: If you're using dynamic SQL (string concatenation), make sure field/table names with spaces or special characters are wrapped in square brackets ([]). For example, use [Customer Name] instead of Customer Name—the server will throw this error if it can't parse the name correctly.
• Confirm object existence post-migration: Double-check that any linked tables, queries, or temporary tables your code references actually exist on the server. It's easy for paths or table names to get messed up during migration.
• Match parameter types: If you're using parameterized queries, ensure the data type of your parameters matches the target field type. For dates, use proper formatting (e.g., #10/05/2024# instead of a string without delimiters); for numbers, don't wrap them in quotes.
• Check server permissions: The account your Access app uses to connect to the server needs read/write access to the database and any temporary tables you're using. If permissions are restricted, this error will pop up when trying to execute operations.

2. Speeding Up Data Imports & Fixing TransferSpreadsheet Syntax Errors #

First, let's squash that syntax error—then we'll tackle the speed issue head-on.

Fixing TransferSpreadsheet Syntax Issues #

The correct syntax for TransferSpreadsheet depends on your Excel file type, but here's a standard template for importing to a server-side temp table:

DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel12Xml, ' Use acSpreadsheetTypeExcel9 for .xls files
    TableName:="TempTable", ' Your server-side temp table name
    FileName:="C:\Path\To\Your\File.xlsx", ' Full path to Excel file
    HasFieldNames:=True, ' Set to False if your Excel sheet has no header row
    Range:="Sheet1$" ' Optional: Specify a specific range if needed

Common syntax mistakes to avoid:

• Mixing up parameter order (e.g., putting the file path before the table name)
• Forgetting to wrap file paths with spaces in quotes (use Chr(34) & "C:\My Path\File.xlsx" & Chr(34) if building paths dynamically)
• Using the wrong SpreadsheetType enum (mismatched with your Excel file version)

Speeding Up Imports (Alternative to TransferSpreadsheet) #

TransferSpreadsheet is slow for large datasets because it processes rows one by one. Instead, use ADODB connections to bulk-import directly from Excel to your server table—it's way faster. Here's a working example:

Dim excelConn As ADODB.Connection
Set excelConn = New ADODB.Connection

' Connect to the Excel file
excelConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Path\To\File.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;"""

' Bulk insert into server temp table (adjust field names to match your schema)
CurrentDb.Execute _
    "INSERT INTO TempTable (ID, CustomerID, OrderDate, Total) " & _
    "SELECT ID, CustomerID, OrderDate, Total FROM [Sheet1$] IN '" & excelConn.ConnectionString & "'"

' Clean up
excelConn.Close
Set excelConn = Nothing

Additional tips for faster imports:

• Disable Access warnings and screen updates before importing to cut down on overhead:

  DoCmd.SetWarnings False
  DoCmd.Echo False ' Turns off screen updates in Access
  ' Your import code here
  DoCmd.Echo True
  DoCmd.SetWarnings True
  

• Remove indexes from your temp table before importing (rebuild them after)—indexes slow down bulk inserts significantly.
• For extremely large datasets (100k+ rows), split the import into batches (e.g., import 10k rows at a time) to avoid memory issues.

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