Access连接SQL Azure添加记录时错误异常及正确报错获取方法咨询

Great question—this is such a frustrating quirk when pairing Access with SQL Azure. That vague "connection is busy" error hides the real root cause (like your key conflict) because Access wraps the native SQL Server error messages. Here are practical ways to get the actual error details without pre-querying table data every time:

• Use Pass-Through Queries for Direct Execution
  Access linked tables often mask raw SQL Azure errors. Instead, create a pass-through query to send your insert statement directly to the database:

  1. Create a new query in Access, switch to SQL View.
  2. Go to the Design tab, click Pass-Through (under the Query Type group).
  3. Paste your insert SQL (e.g., INSERT INTO YourTable (PrimaryKeyCol, OtherCol) VALUES (123, 'Sample Data')).
  4. Run the query—you’ll get the native SQL Azure error, like the explicit "Violation of PRIMARY KEY constraint..." message, instead of the misleading busy connection error.

• Enable ODBC Trace Logging
  The ODBC driver logs all communication between Access and SQL Azure, including raw error responses:

  1. Open the 32-bit ODBC Data Source Administrator (critical, since most Access installations are 32-bit).
  2. Find your SQL Azure data source, go to the Trace tab, check "Start Tracing Now".
  3. Perform your insert operation in Access, then stop tracing.
  4. Open the generated log file (usually saved to your temp directory) — you’ll find the full SQL Azure error details buried in the trace output, including error codes and descriptions.

• Capture Errors Directly in SQL Azure with Extended Events
  Set up a server-side trace on your SQL Azure database to catch error events as they happen:
  Create an Extended Events session using T-SQL (or via the Azure Portal’s SQL Database > Extended Events menu):

  CREATE EVENT SESSION [CaptureInsertErrors] ON DATABASE 
  ADD EVENT sqlserver.error_reported(
      WHERE error_number = 2627) -- Target primary key conflict error code
  ADD TARGET package0.event_file(SET filename=N'InsertErrorLog.xel')
  WITH (STARTUP_STATE=OFF);
  

  Start the session, run your Access insert, then query the event file to pull the exact error details:

  SELECT 
      event_data.value('(event/@name)[1]', 'varchar(50)') AS EventName,
      event_data.value('(event/data[@name="error_number"]/value)[1]', 'int') AS ErrorNumber,
      event_data.value('(event/data[@name="message"]/value)[1]', 'varchar(MAX)') AS ErrorMessage
  FROM 
      sys.fn_xe_file_target_read_file('InsertErrorLog*.xel', NULL, NULL, NULL);
  

• Use VBA to Catch ODBC’s Native Errors
  Write a simple VBA routine to execute your insert and capture the raw ODBC error instead of Access’s wrapped message:

  Sub InsertWithRawErrorHandling()
      Dim azureConn As ADODB.Connection
      Set azureConn = New ADODB.Connection
      
      ' Replace with your SQL Azure ODBC connection string
      azureConn.Open "DRIVER={ODBC Driver 17 for SQL Server};SERVER=tcp:yourserver.database.windows.net,1433;DATABASE=yourdb;UID=youruser;PWD=yourpassword;Encrypt=Yes;TrustServerCertificate=No;Connection Timeout=30;"
      
      On Error GoTo ErrorHandler
      azureConn.Execute "INSERT INTO YourTable (PrimaryKeyCol, OtherCol) VALUES (123, 'Sample Data')"
      MsgBox "Insert completed successfully!"
      Exit Sub
      
  ErrorHandler:
      If Err.Number = -2147467259 Then ' Generic ODBC error code
          ' Grab the first native SQL Azure error from the connection
          MsgBox "SQL Azure Error: " & azureConn.Errors(0).Description & vbCrLf & _
                 "Error Code: " & azureConn.Errors(0).Number
      Else
          MsgBox "Access Error: " & Err.Description
      End If
      
      azureConn.Close
      Set azureConn = Nothing
  End Sub
  

  This bypasses Access’s error wrapping and gives you the exact message from SQL Azure.

All these methods let you get to the real error without pre-checking table data, saving you the hassle of debugging that misleading "connection is busy" message.

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