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

2017/09/19 2017/10/23

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

Command オブジェクトの作成
ActiveConnection プロパティを使い、開いている接続（Connection オブジェクト）と Command オブジェクトとを関連付ける。または、ActiveConnection プロパティに有効な接続文字列を設定する。
CommandText プロパティにパラメータの必要なクエリを設定する。または、SQL文を設定する。
Parameters コレクションを使い、クエリに渡すパラメータを配列形式で指定する。
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

引数・戻り値

object ・・・対象となる Command オブジェクトを指定します。
Connection_object ・・・ Connection オブジェクトを指定します。詳細は、データベース（Access 等）に接続（ADO）を参照してください。
ConnectionString ・・・接続情報文字列（String）を設定します。詳細は、データベース（Access 等）に接続（ADO）の データベースへ接続（Open メソッド） を参照してください。

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 オブジェクトで指定されたストアドプロシージャまたはパラメータクエリに関するプロバイダ側のパラメータ情報を取得できます。プロバイダがストアドプロシージャの呼び出しまたはパラメータクエリをサポートしない場合は、コレクションは空になります。

Sub Sample_Command_Refresh()

    '参照設定:Microsoft ActiveX Data Objects 6.1 Library

    Dim cmd As ADODB.Command
    Dim rs As Object
    Dim constr As String
    Dim DBFile As String
    
    DBFile = ActiveWorkbook.Path & "\mydb1.accdb"
    constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile
                
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = constr
    
    cmd.CommandText = "[テーブル2 クエリ]"
    Set rs = cmd.Execute(Parameters:=Array(1))
    
    cmd.Parameters.Refresh
    
    cmd.CommandText = "[テーブル3 クエリ1]"
    Set rs = cmd.Execute(Parameters:=Array(1100, 2000, 2010, 3))
        
    rs.Close
    Set rs = Nothing
    Set cmd = Nothing
    
End Sub

Sub Sample_Command_Refresh()

'参照設定:Microsoft ActiveX Data Objects 6.1 Library

Dim cmd As ADODB.Command

Dim rs As Object

Dim constr As String

Dim DBFile As String

DBFile = ActiveWorkbook.Path & "\mydb1.accdb"

constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile

Set cmd = New ADODB.Command

cmd.ActiveConnection = constr

cmd.CommandText = "[テーブル2 クエリ]"

Set rs = cmd.Execute(Parameters:=Array(1))

cmd.Parameters.Refresh

cmd.CommandText = "[テーブル3 クエリ1]"

Set rs = cmd.Execute(Parameters:=Array(1100, 2000, 2010, 3))

rs.Close

Set rs = Nothing

Set cmd = Nothing

End Sub

※ 上記の場合、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

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

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

Parameter オブジェクト（CreateObject 関数）

【書式】

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

CreateObject 関数

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ソースその１

　
Sub Sample_Command()
    
    '参照設定:Microsoft ActiveX Data Objects 6.1 Library
    
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As Object
    Dim constr As String
    Dim DBFile As String
    Dim i As Long, j As Long
    Dim strBack1, strBack2, strBack3, strBack4
    
    DBFile = ActiveWorkbook.Path & "\mydb1.accdb"
    constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile
    
    Set cn = New ADODB.Connection
    cn.ConnectionString = constr
    cn.Open
    
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandText = "[テーブル3 クエリ1]"
    
    strBack1 = 1000
    strBack2 = 2000
    strBack3 = Trim(InputBox("年度を入力して下さい（例:2010）"))
    strBack4 = Trim(InputBox("月度を入力して下さい（例：3）"))
    
    Set rs = cmd.Execute(Parameters:=Array(strBack1, strBack2, strBack3, strBack4))
    
    With Worksheets("Sheet1")
        .Cells.Clear
        rs.MoveFirst
        i = 1
        .Cells.Clear
        Do Until rs.EOF
            For j = 0 To rs.Fields.Count - 1
                If i = 1 Then .Cells(i, j + 1) = rs(j).Name
                .Cells(i + 1, j + 1) = rs(j).Value
            Next j
            rs.MoveNext
            i = i + 1
        Loop
        .Columns("A:H").AutoFit
    End With
    
    rs.Close
    Set rs = Nothing
    Set cmd = Nothing
    cn.Close
    Set cn = Nothing
    
End Sub

Sub Sample_Command()

'参照設定:Microsoft ActiveX Data Objects 6.1 Library

Dim cn As ADODB.Connection

Dim cmd As ADODB.Command

Dim rs As Object

Dim constr As String

Dim DBFile As String

Dim i As Long, j As Long

Dim strBack1, strBack2, strBack3, strBack4

DBFile = ActiveWorkbook.Path & "\mydb1.accdb"

constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile

Set cn = New ADODB.Connection

cn.ConnectionString = constr

cn.Open

Set cmd = New ADODB.Command

cmd.ActiveConnection = cn

cmd.CommandText = "[テーブル3 クエリ1]"

strBack1 = 1000

strBack2 = 2000

strBack3 = Trim(InputBox("年度を入力して下さい（例:2010）"))

strBack4 = Trim(InputBox("月度を入力して下さい（例：3）"))

Set rs = cmd.Execute(Parameters:=Array(strBack1, strBack2, strBack3, strBack4))

With Worksheets("Sheet1")

.Cells.Clear

rs.MoveFirst

i = 1

.Cells.Clear

Do Until rs.EOF

For j = 0 To rs.Fields.Count - 1

If i = 1 Then .Cells(i, j + 1) = rs(j).Name

.Cells(i + 1, j + 1) = rs(j).Value

Next j

rs.MoveNext

i = i + 1

Loop

.Columns("A:H").AutoFit

End With

rs.Close

Set rs = Nothing

Set cmd = Nothing

cn.Close

Set cn = Nothing

End Sub

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

実行結果

サンプルVBAソースその２

　
Sub Sample_Command_CreateParameter()
    
    '参照設定:Microsoft ActiveX Data Objects 6.1 Library
    
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
    Dim param As ADODB.Parameter
    Dim constr As String
    Dim DBFile As String
    Dim i As Long
    Dim j As Long
    
    DBFile = ActiveWorkbook.Path & "\mydb1.accdb"
    constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile
    
    Set cn = New ADODB.Connection
    cn.ConnectionString = constr
    cn.Open
    
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandText = "select * from テーブル3 where 仕入先C= ? and 年度= ? order by 月度"
        .Prepared = True
    End With
    
    Set param = New ADODB.Parameter
    
    Set param = cmd.CreateParameter("取引先", adInteger, adParamInput)
    cmd.Parameters.Append param
    cmd.Parameters("取引先").Value = Trim(InputBox("仕入先Cを入力してください"))
     
    Set param = cmd.CreateParameter("年度", adInteger, adParamInput)
    cmd.Parameters.Append param
    cmd.Parameters("年度").Value = Trim(InputBox("年度を入力してください"))
    
    Set rs = cmd.Execute
    
    With Worksheets("Sheet1")
        .Cells.Clear
            rs.MoveFirst
        i = 1
        .Cells.Clear
        Do Until rs.EOF
            For j = 0 To rs.Fields.Count - 1
                If i = 1 Then .Cells(i, j + 1) = rs(j).Name
                .Cells(i + 1, j + 1) = rs(j).Value
            Next j
            rs.MoveNext
            i = i + 1
        Loop
        .Columns("A:H").AutoFit
    End With
    
    Set cmd = Nothing
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    
End Sub