通过 ODBC 访问 Excel 时出现错误“操作必须使用可更新查询”

症状

通过 ADO 和 ODBC 编辑 Excel 工作表时，如果使用 ADO DataControl 对象，可能会收到以下错误消息：

[Microsoft][ODBC Excel Driver] Operation must use an updateable query.

如果使用使用 ADO 代码生成的 Recordset 对象，则通过 ADO 和 ODBC 编辑 Excel 工作表时，可能会收到以下错误消息：

Run-time error '-2147467259(80004005)': [Microsoft][ODBC Excel Driver] Operation must use an updateable query.

原因

如果尝试编辑保存或打开为 ReadOnly 的工作表，则会出现此问题。

注释

ReadOnly 是与 Excel 的 ODBC 连接的默认设置，包含或不带数据源名称（DSN）。 因此，用户必须始终更改该设置才能编辑数据。

决议

若要解决此问题，请使用以下方法：

• 确保 Recordset 对象的 LockType 属性未设置为 ReadOnly。

• 确保尝试打开的文件未保存为 ReadOnly。

• 如果要通过 DSN 进行连接，请执行以下步骤：

  1. 打开“控制面板”，然后选择“ODBC 数据源管理员”。
  2. 双击 DSN。
  3. 在“ODBC Microsoft Excel 设置”对话框中，选择“选项”。
  4. 确保清除 ReadOnly 复选框。

• 如果使用的是无 DSN 连接，请确保在连接字符串中包含“ReadOnly=0”选项。 例如：

  cn.Open "Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\MyDoc.xls;ReadOnly=0;"
  

状态

此行为是设计造成的。

详细信息

重现行为的步骤

1. 在 Visual Basic 中创建新的 Standard EXE 项目。

2. 在“项目”菜单上，选择“引用”，然后添加对 Microsoft ActiveX 数据对象库的引用。

3. 向 Form1 添加命令按钮。

4. 将以下代码添加到 Form1：

   Dim rs As ADODB.Recordset
   Dim cn As ADODB.Connection
   
   Private Sub Form_Load()
     Command1.Caption = "Edit"
   End Sub
   
   Private Sub Command1_Click()
     Dim DocPath As String
   
     DocPath = App.Path & "\Test.xls"
   
     Set cn = New Connection
     Set rs = New Recordset
   
     cn.Open "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & DocPath & ";ReadOnly=1"
     rs.LockType = adLockOptimistic
     rs.Open "TB1", cn
     rs.AddNew
     rs.Fields(1).Value = "New Value"
     rs.Update
   
     rs.Close
     cn.Close
     Set rs = Nothing
     Set cn = Nothing
   End Sub  
   

5. 保存应用程序。

6. 创建新的 Excel 工作表，然后将工作表另存为 Test.xls。

7. 在“插入”菜单上，指向“名称”，然后选择“定义”。

8. 在 Excel 工作表中创建一个新表，然后将表命名为 TB1。

9. 将工作表保存在与 Visual Basic 应用程序相同的文件夹中。

10. 按 F5 运行应用程序。

参考文献

有关详细信息，请参阅 Office Space：Microsoft Office 应用程序脚本的提示和技巧。