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