ExcelWork.info

Excel(エクセル)のマクロ・VBA・関数・操作などのメモ

クエリにパラメータを渡して結果を取得する(ADO)Command オブジェクト・Execute メソッド

      2017/10/23

クエリにパラメータを渡して結果を取得する手順

  1. Command オブジェクトの作成
  2. ActiveConnection プロパティを使い、開いている接続(Connection オブジェクト)と Command オブジェクトとを関連付ける。または、ActiveConnection プロパティに有効な接続文字列を設定する。
  3. CommandText プロパティにパラメータの必要なクエリを設定する。または、SQL文を設定する。
  4. Parameters コレクションを使い、クエリに渡すパラメータを配列形式で指定する。
  5. Execute メソッドを実行し、必要であれば Recordset オブジェクトを返す。

Command オブジェクト(参照設定)

【書式】

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

または

Dim cmd As New ADODB.Command


参照設定を行うライブラリファイルについて

Excel2003 以前では、Microsoft ActiveX Data Objects 2.8 Library を選択します。
Excel2007 以降では、Microsoft ActiveX Data Objects バージョン番号 Library を選択します。
※ 複数のバージョンがあります。特に必要がなければ最新バージョンを選択すると良いでしょう。

参照設定の方法については、ファイルシステムオブジェクト(FileSystemObject)の使い方 を参照してください。



Command オブジェクト(CreateObject 関数)

【書式】

Dim cmd As object
Set cmd = CreateObject(“ADODB.Command“)


CreateObject 関数

CreatObject 関数 を使用する場合は、参照設定の必要はありませんが、VBE で、自動メンバ表示等のコーディング支援機能が使用できません。また、マクロの実行速度も遅くなります。



ActiveConnection プロパティ

【書式】

object.ActiveConnection = Connection_object

または

object.ActiveConnection = ConnectionString


引数・戻り値


ActiveConnection プロパティNothing を設定すると、Command オブジェクトと現在の Connection との関連付けが解除され、プロバイダはデータソース上の関連するリソースをすべて解放します。



CommandText プロパティ

【書式】

object.CommandText = Source


引数・戻り値

  • object  ・・・  対象となる Command オブジェクトを指定します。
  • Source  ・・・  テーブル名、クエリ、SQL文などを指定します。

CommandText プロパティ には、SQL ステートメント、テーブル名、またはストアドプロシージャの呼び出しなどを表す文字列型(String)の値を設定または取得します。デフォルトは “”(長さ 0 の文字列)です。

クエリを実行したい場合は、クエリ名 を指定します。パラメータクエリの場合は、Parameters コレクション にパラメータを指定します。



CommandType プロパティ

【書式】

object.CommandType = commandtypeenum


引数・戻り値

  • object  ・・・  対象となる Command オブジェクトを指定します。
  • commandtypeenum  ・・・  CommandTypeEnum 値を設定します。また、値の取得も可能です。
    定数 内容
    adCmdText 1 SQL 文
    adCmdTable 2 テーブル名
    adCmdStoredProc 4 ストアド プロシージャ名
    adCmdUnknown 8 不明(既定値)
    adCmdFile 256 永続的に保存された Recordset のファイル名
    adCmdTableDirect 512 列がすべて返されるテーブル名
    adCmdUnspecified -1 コマンドタイプ引数を指定しません



Parameters コレクション

【書式】

object.Parameters = Array ( parameters_strings )


引数・戻り値

  • object  ・・・  対象となる Command オブジェクトを指定します。
  • parameters_string  ・・・  クエリに渡すパラメータ文字列を指定します。複数ある場合は,(カンマ)で区切ります。
  • FieldName  ・・・  クエリに渡すパラメータ文字列の列名(フィールド名)指定します。
  • parameter_string  ・・・  クエリに渡す FieldName に対応するパラメータ文字列を指定します。

Item プロパティ
Parameter オブジェクト を参照してください。


Count プロパティ

【書式】
Parameters.Count

戻り値:パラメータの数を表す長整数型(Long)の値を返します。


Append メソッド

【書式】
Parameters.Append param_object

Parameter オブジェクトParameters コレクションに追加します。この場合は、Parameter オブジェクトType プロパティを設定しておく必要があります。
【引数】
param_object・・・Parameter オブジェクト を指定します。(CreateParameter メソッドの戻り値を指定します。)


Delete メソッド

【書式】
Parameters.Delete Index

Parameters コレクションからオブジェクトを削除します。
【引数】
Index・・・Parameter オブジェクトName プロパティ、または Parameters コレクションのインデックス値(整数値: 0 ~ Parameters.Count – 1 )を指定します。


Refresh メソッド

【書式】
Parameters.Refresh

Parameters.Refresh メソッドを使うと、Command オブジェクトで指定されたストアドプロシージャまたはパラメータクエリに関するプロバイダ側のパラメータ情報を取得できます。プロバイダがストアドプロシージャの呼び出しまたはパラメータクエリをサポートしない場合は、コレクションは空になります。



※ 上記の場合、Parameters.Refresh メソッドを実行しない場合、以下のようなエラーが発生する場合があります。
Parameters.Refresh エラー



CreateParameter メソッド

【書式】

<Parameter オブジェクトを作成>
Set parameter = object.CreateParameter ( Name[, Type, Direction, Size, Value] )


引数・戻り値

  • object  ・・・  対象となる Command オブジェクトを指定します。
  • Name  ・・・  Parameter オブジェクトの名前を表す文字列型(String)を指定します。
  • Type  ・・・  Parameter オブジェクトのデータ型を表す DataTypeEnum クラスの定数または値を指定します(省略可)。
    定数 説明
    adEmpty 0 指定された値なし
    adSmallInt 2 2 バイトの符号付き整数
    adInteger 3 4 バイトの符号付き整数
    adSingle 4 単精度浮動小数点値
    adDouble 5 倍精度浮動小数点値
    adCurrency 6 通貨値 (8 バイトの符号付き整数、桁数 10,000)
    adDate 7 日付型 (Date) 値
    adBSTR 8 終端が null の文字列 (Unicode)
    adIDispatch 9 OLE オブジェクトの IDispatch インターフェイスを指すポインタ
    adError 10 32 ビットのエラー コード
    adBoolean 11 ブール型 (Boolean) 値
    adVariant 12 OLE オートメーション バリアント型 (Variant)
    adIUnknown 13 OLE オブジェクトの IUnknown インターフェイスを指すポインタ
    adDecimal 14 固定の精度および桁数を持つ正確な数値
    adTinyInt 16 1 バイトの符号付き整数
    adUnsignedTinyInt 17 1 バイトの符号なし整数
    adUnsignedSmallInt 18 2 バイトの符号なし整数
    adUnsignedInt 19 4 バイトの符号なし整数
    adBigInt 20 8 バイトの符号付き整数
    adUnsignedBigInt 21 8 バイトの符号なし整数
    adGUID 72 グローバル一意識別子 (GUID)
    adBinary 128 バイナリ値
    adChar 129 文字列型 (String) 値
    adWChar 130 終端が null の Unicode 文字列
    adNumeric 131 固定の精度および桁数を持つ正確な数値
    adUserDefined 132 ユーザー定義変数
    adDBDate 133 日付値 (yyyymmdd)
    adDBTime 134 時刻値 (hhmmss)
    adDBTimeStamp 135 日付時刻スタンプ (yyyymmddhhmmss および 10 億分の 1 桁までの小数)
    adVarChar 200 文字列型 (String) 値 (Parameter オブジェクトのみ)
    adLongVarChar 201 長文字列型 (String) 値 (Parameter オブジェクトのみ)
    adVarWChar 202 終端が null の Unicode 文字列 (Parameter オブジェクトのみ)
    adLongVarWChar 203 終端が null の長文字列値 (Parameter オブジェクトのみ)
    adVarBinary 204 バイナリ値 (Parameter オブジェクトのみ)
    adLongVarBinary 205 ロング バイナリ値 (Parameter オブジェクトのみ)

  • Direction  ・・・  Parameter オブジェクトのオブジェクト型を表すParameterDirectionEnum クラスの定数または値を指定します(省略可)。
    定数 内容
    adParamUnknown 0 パラメーターの方向が不明
    adParamInput 1 入力パラメータ(既定値)
    adParamOutput 2 出力パラメータ
    adParamInputOutput 3 入出力パラメータ
    adParamReturnValue 4 パラメーターが戻り値を表す

  • Size  ・・・  パラメータの最大サイズを文字単位またはバイト単位で示す長整数型(Long)の値を指定します(省略可)。
  • Value  ・・・  パラメータに設定する値(クエリに渡すパラメータ値)を指定します(省略可)。
  • parameter  ・・・  作成する Parameter オブジェクト を表すオブジェクト変数


Execute メソッド

【書式】

<取得>
Set recordset = object.Execute [ ( RecordsAffected, Parameters, Options ) ]

object.Execute [ RecordsAffected, Parameters, Options ]


引数・戻り値

  • object  ・・・  対象となる Command オブジェクトを指定します。
  • recordset  ・・・  Recordset オブジェクトを表すオブジェクト変数です。Execute メソッドの実行結果、または、Nothing を取得します。
  • RecordsAffected  ・・・  長整数型(Long)の変数を指定します。Execute メソッド実行操作によって影響を受けたレコードの数をこの変数に返します(省略可)。
  • Parameters  ・・・  クエリに渡すパラメータをバリアント型(Variant)の配列で指定します。⇒ Parameters コレクション(省略可)
  • Options  ・・・  CommandText プロパティの評価を最適化するために使います(省略可)。
    CommandTypeEnum クラス の定数(またはその値)を指定します。&

    定数 内容
    adCmdText 1 SQL 文
    adCmdTable 2 テーブル名
    adCmdStoredProc 4 ストアド プロシージャ名
    adCmdUnknown 8 不明(既定値)
    adCmdFile 256 永続的に保存された Recordset のファイル名
    adCmdTableDirect 512 列がすべて返されるテーブル名
    adCmdUnspecified -1 コマンドタイプ引数を指定しません



Parameter オブジェクト(参照設定)

【書式】

Dim parameter As ADODB.Parameter
Set parameter = New ADODB.Parameter

または

Dim parameter As New ADODB.Parameter


参照設定を行うライブラリファイルについて

Excel2003 以前では、Microsoft ActiveX Data Objects 2.8 Library を選択します。
Excel2007 以降では、Microsoft ActiveX Data Objects バージョン番号 Library を選択します。
※ 複数のバージョンがあります。特に必要がなければ最新バージョンを選択すると良いでしょう。

参照設定の方法については、ファイルシステムオブジェクト(FileSystemObject)の使い方 を参照してください。



Parameter オブジェクト(CreateObject 関数)

【書式】

Dim parameter As object
Set parameter = CreateObject(“ADODB.Parameter“)


CreateObject 関数

CreatObject 関数 を使用する場合は、参照設定の必要はありませんが、VBE で、自動メンバ表示等のコーディング支援機能が使用できません。また、マクロの実行速度も遅くなります。



Parameter オブジェクトの取得

【書式】

<取得>
object.Parameters[.Item](index)
object.Parameters[.Item](name)
object(index)
object(name)
object![name]


引数・戻り値

  • object  ・・・  対象となる Command オブジェクトを指定します。
  • index  ・・・  インデックス番号(0 から始まる整数値)を指定します。
  • name  ・・・  パラメータ名を指定します。

Name プロパティ

【書式】
parameter_object.Name

パラメータ名の取得および設定をします。
parameter_object・・・Parameter オブジェクト


Value プロパティ

【書式】
parameter_object.Value

Parameter オブジェクトのパラメータ値の取得および設定をします。
parameter_object・・・Parameter オブジェクト



Command オブジェクト 使用例

サンプルVBAソース その1


mydb1「テーブル3 クエリ1」の内容

mydb1「テーブル3 クエリ1」デザインビュー


実行結果

(ADO)Command オブジェクト 実行結果1

(ADO)Command オブジェクト 実行結果2

(ADO)Command オブジェクト 実行結果3


サンプルVBAソース その2


実行結果

(ADO)Command オブジェクト 実行結果1

(ADO)Command オブジェクト 実行結果2

(ADO)Command オブジェクト 実行結果3



 - データベース , , , , , , , , , , , , , ,

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

  関連記事

ADOX クエリを作成
クエリの作成・削除(ADOX)

ADOX を使用してクエリを作成・削除 クエリを作成する手順 【書式】 <参照設 …

データベース(SQLServer)接続
データベース(SQL Server)に接続する(ADO)

データベース(SQL Server)に接続 ODBC または OLE DB で接 …

Filter プロパティ(ADO)
レコードを指定した条件で抽出する(ADO)Filter プロパティ

データベースのレコードを指定した条件で抽出する Filter プロパティ 【書式 …

Delete メソッド(ADO)
レコードの削除(ADO)Delete メソッド

データベースのレコードを削除する Delete メソッド カレントレコードを削除 …

SQL実行結果
データベース(Oracle)に接続する(ODBC を使用)

ODBC接続(ADO) ADOで、ODBC(Open Database Conn …

Excelに接続する(ADO)
Excelファイルに接続(ADO)

ADOでExcelファイルに接続 ADO を使用してExcelファイルを読み込み …

RecordCount プロパティ(ADO)
Recordset オブジェクト内のレコード数(ADO)RecordCount プロパティ

RecordCount プロパティ Recordset オブジェクトのレコード数 …

Sort プロパティ(ADO)
レコードを並べ替える(ADO)Sort プロパティ

データベースのレコードを並べ替える Sort プロパティ 【書式】 object …

ADOX テーブルの作成
テーブルの作成・削除/フィールドの作成・削除(ADOX)

ADOX を使用してテーブル・フィールド(列)を作成・削除 Table オブジェ …

ado Field オブジェクト
フィールド名(ADO)Field オブジェクト

Field オブジェクト Recordset オブジェクト内の列(フィールド)を …