概要
作成したExcelのインスタンスに、ピボットテーブルキャッシュのデータソースとしてRecordsetオブジェクトを設定するとき、ADOで作成したRecordsetオブジェクトのCursorLocationプロパティにはadUseClientを設定しないと、「実行時エラー 1004 アプリケーション定義またはオブジェクト定義のエラーです」になります。
どうやら、ADOのサーバー側カーソルは異なるプロセス間のマーシャリングができないことが原因らしい。
詳細な検証はできないけれど、関連資料の紹介とAccess-Excelで起こる実例を提示しました。
This page shows about cross-process marshalling of ADO recordset on VBA.
English summary is end of this page.
はじめに…
VBAでレコードセットを扱っていると、CursorLocationプロパティをadUseServerにすべきかadUseClientにすべきか迷うことがあります。
recordsetのCursorLocationプロパティの規定値はadUseServer（データ プロバイダ カーソルまたはドライバによって供給されるカーソル）なので、特に指定しないとadUseServerになっています。
よくあるのが、レコードセットの受け渡しでエラーが出たときに、CursorLocationプロパティをadUseClientに変更したら、エラーが出なくなってめでたしめでたし、というパターン。
プログラミングに関して専門知識を持ち合わせている訳ではないので、オールマイティな検討はできないのですが、どうやらoleオートメーションを介してレコードセットをやり取りすると、CursorLocationプロパティをadUseClientにしなければならない場合があるようです。
資料1「ADO マージャリングの理解(http://support.microsoft.com/kb/248287)」
この資料では、題名のとおり、ADOのマーシャリングに関して説明されています。
一部を抜粋します。
《ADO の一般的なマーシャリング》
特に指定しない限り、ADO は標準の COM マーシャリングを提供します。つまり、ADO は標準の OLE オートメーション形式を使用して、特別なカスタム マーシャリング コードを記述しないで、引数およびインターフェイス ポインタをプロセス間で受け渡すことができます。たとえば、COM サーバーはクライアントに接続オブジェクトまたはレコードセット オブジェクトを返すことができ、プロセス間で ADO オブジェクトのインターフェイス ポインタを渡すことができます (つまり、オブジェクトへの参照を渡すことができます)。インターフェイスのメソッドに対するすべての呼び出しは、サーバー上に既に作成され、存在する ADO オブジェクトに対するクライアントからのコール バックを生じます。
《標準マーシャリングの機能強化》
ADO の標準マーシャリングはほとんどの例で十分な機能を持っていますが、すべての例で十分というわけではありません。たとえば、プロセスをまたがる標準のマーシャリングは、ADO オブジェクトを呼び出すたびに、アプリケーションはプロセスをまたがる呼び出しを行うので、パフォーマンスが低下します。レコードセットのすべてのデータがクライアントに返され、その後のフェッチなどの呼び出しを、レコードセットが作成された COM サーバーに対して行うのではなく、クライアントでローカルに行うとパフォーマンスは向上します。
ADO はレコードセット オブジェクトに対するこのようなメカニズムを提供します。サーバーからレコードセット オブジェクトを返し、クライアントにレコードセットのデータをマーシャリングするには、レコードセットの CursorLocation プロパティに adUseClient を指定します。adUseClient 値は、レコードセット オブジェクトがレコードセットのすべてのレコードをキャッシュするクライアント カーソル エンジンを使用することを通知します。adUseClient を設定してレコードセットがプロセスの境界を超えて送信されるときは、常にクライアント カーソル エンジンを使用するレコードセット オブジェクトが受け取り側のプロセスで構築され、送信したレコードが設定されます。この時点では、レコードセットはデータベース接続から “切断” されるか “関連付けを解除” されます。adUseClient が指定されているときは、レコードセット オブジェクトと共にデータベース接続が受け渡されることはありません。
adUseClient が設定されている場合は、プロセスをまたがってレコードセットが送信されるときは常にレコードセットのデータが渡されます。一般的にはこのことを “値渡し” と呼びます。adUseClient が設定されていない場合は、常に標準のマーシャリングが行われ、ADO レコードセット オブジェクトへの参照だけがプロセスの境界を超えて渡されます。一般的にこのことを “参照渡し” と呼びます。
専門知識に欠けるので自信はないのですが、要するに、こういうことではないのかと。
このドキュメントを読む限りでは、レコードセットのCursorLocationプロパティにadUseServerを設定すれば、oleオートメーションを介して参照渡しができそうなのですが・・・。
資料2「Add method of QueryTables with Excel automation generates run-time error message “5”」(http://support.microsoft.com/kb/263498/en-us)邦訳（機械翻訳版）：クエリテーブルのメソッドは、Excel のオートメーションを生成 run-time エラー メッセージ「5」を追加します。(http://support.microsoft.com/kb/263498/jp)
この資料で述べられていることを要約すると以下のようになります。かなり意訳していますので、出典をご確認ください。
「Add method of QueryTables with Excel automation generates run-time error message “5”」では、実行時エラー原因として、
The problem is caused by the manner in which ActiveX Data Objects (ADO) uses marshalling for recordsets. ADO server-side cursors do not marshal cross-process.
br>と明確に述べている点に注目です。
詳細は実際のドキュメントをご覧いただくとして、提示されているスクリプトを抜粋します。
ExcelとADOの適切な参照設定が必要です。
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim oQueryTable As Excel.QueryTable
'Start a new workbook in Excel
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.UserControl = True
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
'Connect to local SQL Server. You will need to replace and <strong>
'with the User ID and password of an account who has appropriate permissions.
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB.1;Data Source=YourServer;" & _
"Password=;User ID=;Initial Catalog=Northwind"
'Generate the recordset
Set rs = New ADODB.Recordset
rs.Open "Select * from Products", cn
'Create the query table on the worksheet
Set oQueryTable = xlSheet.QueryTables.Add(rs, xlSheet.Cells(1, 1))
oQueryTable.Refresh<
'Close the recordset and the connection
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
記事によると、
Set oQueryTable = xlSheet.QueryTables.Add(rs, xlSheet.Cells(1, 1))
で、run-time error メッセージ “5.”が返るそうです。
上記の資料はVBを対象にしているので、VBAで同じようなことをやってみます。（VBAとほとんど一緒なので、資料２を応用してもよいのですが。） 参考にした記事は・・・
資料3「New ways to automate corporate reporting(http://office.microsoft.com/en-us/excel-help/new-ways-to-automate-corporate-reporting-HA001054803.aspx)」
ちなみにExcel2000を対象とした記事です。邦訳は見当たりませんでした。
資料3の一番最後にある、「Create a PivotTable report by using ADO」中のサブプロシージャ「CreatePTfromADORecordset」が参考になります。
この記事に書いてあることを、ごく簡単に要約すると次のようになります。
外部データベースからExcelにデータを落としこむ方法は、ここに書いてある以外にもあるし、Worksheetに落としこむデータの容量に注意しなくてはならないから、一般的な方法というわけではありません。しかし、少量のレコードをリストアップしたいというニーズは少なからずあり、特にCopyFromRecordsetメソッドは軽快に動作するためよく使われる方法です。
余談になりますが、AccessにはTransferSpreadsheetメソッドもあります。
また、Microsoftの解説の中では、「Visual Basic から Excel にデータを転送する方法（http://support.microsoft.com/kb/247412/ja）」が比較的詳しく、
オートメーションを使用する場合
- セルごとにデータを転送する。
- 配列のデータを特定のセル範囲に転送する。
- CopyFromRecordset メソッドを使用して、ADO レコードセットのデータを特定のセル範囲に転送する。
- ODBC または OLEDB データ ソースのクエリの結果を含む QueryTable オブジェクトを Excel ワークシート上に作成する。
- クリップボードにデータを転送し、クリップボードの内容を Excel ワークシートに貼り付ける。
オートメーションを使用しない場合
- データをタブ区切りまたはコンマ区切りテキスト ファイルに変換し、Excel でテキストを区切ってワークシートのセルに格納する。
- ADO を使用してデータをワークシートに転送する。
- 動的データ交換 (DDE) を使用してデータを Excel に転送する。
について解説されています。ただ、Visual Basicについて書かれたものなので、VBA用に書きなおす必要がありますが、さすがに内容はよくまとまっています。
話をもとに戻しましょう。
「New ways to automate corporate reporting」からサブプロシージャ「CreatePTfromADORecordset」を抜粋します。
'Before you can write ADO programs, you must create a reference to the ADO library:
'In the Visual Basic Editor, click References on the Tools menu,
'and then select the Microsoft ActiveX Data Objects 2.1 Library check box.
Sub CreatePTfromADORecordset()
'Declare variables
Dim objMyConn As New ADODB.Connection
Dim objMyRecordset As New ADODB.Recordset
Dim objMyPivotCache As PivotCache
Dim objMyPivotTable As PivotTable
'Open Connection
objMyConn.Open "Provider=SQLOLEDB;Data Source=Bookstore;User ID=guest;Password=guest;"
'Open Recordset
objMyRecordset.Open "SELECT * FROM dbo.authors Authors", objMyConn, adOpenForwardOnly, adLockReadOnly, adCmdText
'Create PivotCache
Set objMyPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
Set objMyPivotCache.Recordset = objMyRecordset
'Create PivotTable
Set objMyPivotTable = ActiveWorkbook.Sheets("Sheet1").PivotTables.Add(objMyPivotCache, Cells(1, 1))
objMyPivotTable.AddFields ("author_name")
objMyPivotTable.PivotFields("contract").Orientation = xlDataField
'Close the ADO recordset/connection
objMyRecordset.Close
objMyConn.Close
End Sub
サブプロシージャ「CreatePTfromADORecordset」でやろうとしていることは、上記抜粋の4(Pivottableを作成させる方法)のとおりです。
CreatePTfromADORecordsetはExcel用に書かれたものなので、これをもとに、
「AccessのModuleに記載したProcedureから、Excelのインスタンスを生成し、そのインスタンスを操作することで、ExcelのWorksheet上にAccessのTableにADO接続で作成したrecordsetを使ってPivotTableを作成」
するスクリプトを作ります。
上のようにに書くと何のことやら分からない方がいるかもしれませんが、Accessに書いたスクリプトでやらせたいことは、大まかに以下のとおり。
- Excelのウィンドウを新しく起動し、新しいWorkbookを開く。
- Accessのクエリを元にして、ADOで必要なRecordsetを作る。
- そのRecordsetを元のデータにして、Excel上にPivottableを作成する。
と、こんなことをさせたいわけです。
作ったスクリプトは次のもの。
Sub CreatePTfromADORecordset_byAccess()
'============================================================
'参照設定
'Microsoft ActiveX Data Objects Library
'Microsoft Excel 10.0 Object Library
'============================================================
Dim objMyConn As New ADODB.Connection
Dim objMyRecordset As New ADODB.Recordset
Dim objMyRecordsetClone As New ADODB.Recordset
Dim objXls As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objworkSheet As Excel.Worksheet
Dim objMyQueryTable As Excel.QueryTable
Dim objMyPivotCache As Excel.PivotCache
Dim objMyPivotTable As Excel.PivotTable
'Set connection
Set objMyConn = CurrentProject.Connection
'Set cursorlocation
objMyConn.CursorLocation = adUseClient
'Open recordset
objMyRecordset.Open "Authors", objMyConn, adOpenForwardOnly, adLockReadOnly
'Create Excel instance
Set objXls = CreateObject("Excel.Application")
objXls.Visible = True
Set objWorkBook = objXls.Workbooks.Add
Set objworkSheet = objWorkBook.Worksheets(1)
'Create PivotCache
Set objMyPivotCache = objWorkBook.PivotCaches.Add(xlExternal)
Set objMyPivotCache.Recordset = objMyRecordset
'Create PivotTable
Set objMyPivotTable = objWorkBook.Sheets("Sheet1").PivotTables.Add(objMyPivotCache, objworkSheet.Cells(1, 1))
'Close the ADO recordset/connection
objMyRecordset.Close
objMyConn.Close
End Sub
このスクリプトではPibotTableを作成するところまでしか書いていないので、PivotTableの操作はそれぞれのニーズに合わせて追加してください。
検討
上記のスクリプトはAccessの標準moduleで正常に動きます。一方で、「objMyConn.CursorLocation = adUseClient」をコメントアウトすると、recordsetのCursorLocationプロパティは規定値のadUseServerになり、この状態でスクリプトを走らせると、
Set objMyPivotCache.Recordset = objMyRecordset
で、「実行時エラー 1004 アプリケーション定義またはオブジェクト定義のエラーです。」になります。
また、先ほどのMicrosoftの記事「New ways to automate corporate reporting」の中にある「GetDataFromADO」でも使われている、CopyFromRecordsetメソッドも、CursorLocationをadUseClientにしないと、
「実行時エラー’430″:クラスはオートメーションまたは予測したインターフェースをサポートしていません。」
になります。
やはり、今回の場合のように、oleオートメーションを使って、別のインスタンスにレコードセットをマーシャリングする際には、recordsetのCursorLocationプロパティをadUseClient（ローカル カーソル ライブラリより提供されたクライアント側カーソル）にしなければならないようです。
資料1「ADO マージャリングの理解」にはadUseServerを設定すると、レコードセットを参照渡しできるかのように書かれていますが、資料2「Add method of QueryTables with Excel automation generates run-time error message “5”」のCAUSE（原因）に記載してあるように、ADOのサーバー側カーソルは異なるプロセス間のマーシャリングができないということなのでしょう。機会があったら、他のパターンも試してみたいところです。
識者諸兄からのコメント、歓迎です。
ご意見、お寄せください。
Summary
According to “INFO: Understanding ADO Marshaling”,
With adUseClient set, you always pass the data of the recordset when sending the recordset across process. This is typically called “passing by value”. Without adUseClient, you always get standard marshaling and only a reference to the ADO Recordset object is passed across process boundaries and is typically called “passing by reference”.
But another document “Add method of QueryTables with Excel automation generates run-time error message “5”” describes.
In fact, you can create a new instance of Excel PivotTalbe using Microsoft Access VBA and ActiveX Data Objects (ADO). But it’s necessary to check CursorLocation property of Recordset is adUseClient.
Default value of CuresorLocation is adUseServer. So, in case of creating Excel PivotTable from Access, perhaps, you receive the following error message.
Run-time error ‘1004’: Application-defined or object-defined errorYou can get the other document “New ways to automate corporate reporting“. The example program named CreatePTfromADORecordset creates an Excel PivotTableReport using Excel-VBA and ADO. That is a complete program for only Excel. But for Access or VB, it’s necessary to insert one method.
objMyConn.CursorLocation = adUseClient
Your comments, please.
最近のコメント