无法更改、添加或删除链接到 Access 中的 Excel 工作簿的表中的数据

备注

Office 365 ProPlus 正在重命名为适用于企业的 Microsoft 365 应用。 有关此更改的详细信息, 请阅读此博客文章

原始 KB 数:  904953

症状

在 Microsoft Office Access 2007 或 Microsoft Office Access 2003 或 Microsoft Access 2002 中,无法更改、添加或删除链接到 Microsoft Excel 工作簿的表中的数据。

此外,当满足以下任一条件时,您可能会遇到此行为:

  • 生成查询以检索链接到 Excel 工作簿的表中的数据。
  • 构建一个从链接到 Excel 工作簿的表访问数据的窗体。
  • 您可以使用 DAO 或 ADO 以编程方式更新链接到 Excel 工作簿的表。

当您执行查询以更新链接的 Excel 工作簿中的记录时,将收到以下消息:

操作必须使用可更新的查询

当您使用 DAO 以编程方式更新链接到 Excel 工作簿的表时,将收到以下消息:

运行时错误 "3027" 无法更新。 数据库或对象为只读。

当您尝试更新 ADO 中的链接数据时,邮件是相同的,但错误号可能类似于以下内容:

-2147217911 (80040e09)

当您运行查询以将记录插入到 Excel 工作簿中时,即使 Excel 工作簿未链接到 Access 数据库,也会收到以下错误消息:

操作必须使用可更新的查询

原因

如果满足以下任一条件,则会发生这种预期行为:

  • 您使用的是 Office Access 2007。
  • 您已安装 Microsoft Office 2003 Service Pack 2 (SP2)或更高版本的 service pack 或在 Office 2003 SP2 之后发布的任何 Access 2003 更新。
  • 您已安装了2002年10月 18 2005 日发布的 Access (KB904018)更新。
  • 您已安装包含 Microsoft Office 2003 Service Pack 2 (SP2)或更高版本的 service pack 的 Access 运行时应用程序、在 Office 2003 SP2 之后发布的任何访问2003更新,或者 microsoft 2006 年10月 18 2005 日或更高版本的 Access 2002 (KB904018)更新。

解决方法

若要解决此预期行为,请使用下列方法之一。

方法1:使用 Microsoft Excel

在 Microsoft Excel 中打开链接的 Excel 工作簿,然后对工作簿进行更改。 完成更改后,保存所做的更改,然后关闭工作簿。

方法2:使用 Office Access 2007、Access 2003 或 Access 2002

将链接的 Excel 工作簿导入 Access,然后对数据进行更改。 完成更改后,将数据导出为 Excel .xls 文件。

若要将表从 Access 导出到 Excel,请在 Access 中运行以下代码。

Public Sub WorkArounds()
On Error GoTo Leave

Dim strSQL, SQL As String
    Dim Db As ADODB.Connection
    Set Db = New ADODB.Connection
    Db.CursorLocation = adUseClient
    Db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=<AccessPath>"
    'Note: In Office Access 2007, use the following line of code:
    'Db.Open "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=<AccessPath>"
    SQL = "<MyQuery>"
    CopyRecordSetToXL SQL, Db
    Db.Close
    MsgBox "Access has successfully exported the data to excel file.", vbInformation, "Export Successful."
    Exit Sub
Leave:
        MsgBox Err.Description, vbCritical, "Error"
        Exit Sub
End Sub

Private Sub CopyRecordSetToXL(SQL As String, con As ADODB.Connection)
    Dim rs As New ADODB.Recordset
    Dim x
    Dim i As Integer, y As Integer
    Dim xlApp As Excel.Application
    Dim xlwbBook As Excel.Workbook, xlwbAddin As Excel.Workbook
    Dim xlwsSheet As Excel.Worksheet
    Dim rnData As Excel.Range
    Dim stFile As String, stAddin As String
    Dim rng As Range
    stFile = "<ExcelPath>"
    'Instantiate a new session with the COM-Object Excel.exe.
    Set xlApp = New Excel.Application
    Set xlwbBook = xlApp.Workbooks.Open(stFile)
    Set xlwsSheet = xlwbBook.Worksheets("<WorkSheets>")
    xlwsSheet.Activate
    'Getting the first cell to input the data.
    xlwsSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
    y = xlApp.ActiveCell.Column - 1
    xlApp.ActiveCell.Offset(1, -y).Select
    x = xlwsSheet.Application.ActiveCell.Cells.Address
    'Opening the recordset based on the SQL query and saving the data in the Excel worksheet.
    rs.CursorLocation = adUseClient
    If rs.State = adStateOpen Then
        rs.Close
    End If
    rs.Open SQL, con
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        x = Replace(x, "$", "")
        y = Mid(x, 2)
        Set rng = xlwsSheet.Range(x)
        xlwsSheet.Range(x).CopyFromRecordset rs
    End If
    xlwbBook.Close True
    xlApp.Quit
    Set xlwsSheet = Nothing
    Set xlwbBook = Nothing
    Set xlApp = Nothing

End Sub

备注

在此代码示例中,请替换以下占位符:

  • <AccessPath>

  • <ExcelPath>

  • <MyQuery>

    <MyQuery> 是对 Access 数据库中的表运行的查询的占位符。 将查询结果导出到 Excel 工作簿。

  • <工作表>

    <工作表> 是 Excel 中要将结果导出到的工作表的占位符。 若要运行此代码示例,请按 CTRL + G 以打开 "即时" 窗口,键入 "解决方法",然后按 enter。

更多信息

由于存在法律问题,Microsoft 禁用了 Access 2003 和 Access 2002 中的功能,允许用户更改指向 Excel 工作簿中某一区域的链接表中的数据。 但是,当您直接在 Excel 工作簿中进行更改时,所做的更改会显示在 Access 的链接表中。