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

  • 项目
  • 适用于:
    Microsoft Office Access 2007, Microsoft Office Access 2003, Microsoft Office Access 2002

原始 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 更新。
  • 你已安装了 2005 年 10 月 18 日的 Access 2002 (KB904018) 更新。
  • 已安装 Access 运行时应用程序，其中包括 Microsoft Office 2003 Service Pack 2 (SP2) 或更高版本的 Service Pack、Office 2003 SP2 之后发布的任何 Access 2003 更新，或 2005 年 10 月 18 日或更高版本的 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 工作簿。

  • <WorkSheets>

    <WorkSheets> 是 Excel 中要导出结果的工作表的占位符。 若要运行此代码示例，请按 Ctrl+G 打开 “即时 ”窗口，键入“WorkArounds”，然后按 Enter。

更多信息

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