Access のExcel ブックにリンクされているテーブル内のデータを変更、追加、または削除することはできません。

元の KB 番号:   904953

現象

Microsoft Office Access 2007 または Microsoft Office Access 2003 または Microsoft Access 2002 では、Microsoft Excel ブックにリンクされているテーブルのデータを変更、追加、削除することはできません。

さらに、次のいずれかの条件に該当する場合に、この動作が発生する可能性があります。

  • Excel ブックにリンクされているテーブルからデータを取得するクエリを作成します。
  • Excel ブックにリンクされているテーブルからデータにアクセスするフォームを作成します。
  • DAO または ADO を使用して、Excel ブックにリンクされているテーブルをプログラムで更新します。

リンクされたExcel ブック内のレコードを更新するクエリを実行すると、次のメッセージが表示されます。

操作では、更新可能なクエリを使用する必要があります

DAO を使用して、Excel ブックにリンクされているテーブルをプログラムで更新すると、次のメッセージが表示されます。

ランタイム エラー '3027' は更新できません。 (エラー 3027)

ADO でリンクされたデータを更新しようとすると、メッセージは同じですが、エラー番号は次のようになります。

-2147217911 (80040e09)

Excel ブックにレコードを挿入するクエリを実行すると、Excel ブックが Access データベースにリンクされていない場合でも、次のエラー メッセージが表示されます。

操作では、更新可能なクエリを使用する必要があります

原因

この予期される動作は、次のいずれかの条件に該当する場合に発生します。

  • Office Access 2007 を使用しています。
  • Microsoft Office 2003 Service Pack 2 (SP2) 以降のサービス パック、または 2003 SP2 Office後にリリースされた Access 2003 更新プログラムをインストールしました。
  • 2005 年 10 月 18 日にリリースされた Access 2002 (KB904018) の更新プログラムをインストールしました。
  • 2003 Service Pack 2 (SP2) 以降Microsoft Office含む Access ランタイム アプリケーション、2003 SP2 Office後にリリースされた Access 2003 更新プログラム、または 2005 年 10 月 18 日以降にリリースされた Access 2002 (KB904018) の更新プログラムを含む Access ランタイム アプリケーションがインストールされています。

回避策

この想定される動作を回避するには、次のいずれかの方法を使用します。

方法 1: Microsoft Excelを使用する

リンクされたExcelブックをMicrosoft Excelで開き、ブックに変更を加えます。 変更が完了したら、変更を保存してからブックを閉じます。

方法 2: Access 2007、Access 2003、または Access 2002 Officeを使用する

リンクされた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 のリンク テーブルに変更が表示されます。