您無法在 Access 中變更、新增或刪除連結至 Excel 活頁簿的資料表中的資料

注意

Office 365 專業增強版 即將重新命名為 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 更新。
  • 您已安裝版本為18,2005的 Access 2002 (KB904018)更新。
  • 您已安裝一個 Access runtime 應用程式,其中包含 Microsoft Office 2003 Service Pack 2 (SP2)或更新版本的 service pack、Office 2003 SP2 後發行的任何 Access 2003 更新,或是在年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 中的連結資料表中。