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

回避策

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

方法 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> は、結果をエクスポートする Excel のワークシートのプレースホルダーです。 このコード例を実行するには、Ctrl + G キーを押して イミディエイト ウィンドウを開き、「WorkArounds」と入力し、Enter キーを押します。

詳細情報

法的な問題のため、Microsoft は Access 2003 と Access 2002 の機能を無効にして、Excel ブック内の範囲を指すリンク テーブルのデータをユーザーが変更できるようにします。 ただし、Excel ブックで直接変更を行うと、Access のリンク テーブルに変更が表示されます。