クエリの作成・削除（ADOX）

2017/10/06

ADOX を使用してクエリを作成・削除

クエリを作成する手順

【書式】

＜参照設定を行う場合＞
‘データベース接続
Set cat = New ADOX.Catalog
cat.ActiveConnection = [接続文字列]

‘クエリを作成
Set cmd = New ADODB.Command
cmd.CommandText = [SQL文]
cat.Views.Append [クエリ名] ,cmd

＜CreateObject関数を使用する場合＞
‘データベース接続
Set cat = CreateObject(“ADOX.Catalog”)
cat.ActiveConnection = [接続文字列]

‘クエリを作成
Set cmd = CreateObject(“ADODB.Command”)
cmd.CommandText = [SQL文]
cat.Views.Append [クエリ名] ,cmd

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

Microsoft ActiveX Data Objects 6.1 Library、Microsoft ADO Ext.6.0 for DDL and Security を選択します。ただし、Office 等のバージョンにより、ADO、ADOX のバージョンも異なります。参照設定の方法については、ファイルシステムオブジェクト（FileSystemObject）の使い方を参照してください。

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

Command オブジェクトの詳細は、クエリにパラメータを渡して結果を取得する（ADO）Command オブジェクト・Execute メソッドを参照してください。

Catalog オブジェクトの詳細は、データベースを作成する（ADOX）を参照してください。

Views コレクション

【書式】

object.Views

＜View オブジェクトを Views コレクションに追加する＞
object.Views.Append Name, Command

＜Views コレクションから View オブジェクトを削除する＞
object.Views.Delete Name

引数・戻り値

object ・・・対象となる Catalog オブジェクトを指定します。
Name ・・・作成するビューの名前を表す文字列型（String）の値を指定します。
Command ・・・作成するビューを表す ADO の Command オブジェクトを指定します。

Views.Append メソッドで、Access のクエリを作成することができます。Command オブジェクトの CommandText プロパティに、SQL文を指定します。

Views.Delete メソッドで、Access のクエリを削除することができます。

ADOX を使用してクエリを作成・削除する例

サンプルVBAソースその１

　
Sub Sample_ADOX_CreateQuery1()

    '参照設定:Microsoft ActiveX Data Objects 6.1 Library
    '参照設定:Microsoft ADO Ext.6.0 for DDL and Security
                    
    Dim cat As ADOX.Catalog
    Dim cmd As ADODB.Command
    Dim constr As String
    Dim DBFile As String
    
    On Error GoTo ErrHandler
    
    'データベースのパスと名前
    
    'Access 2007以降（accdb ファイル）
    DBFile = ActiveWorkbook.Path & "\mydb2.accdb"
    constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile
    
    'Access 2003以前（mdb ファイル）
    'DBFile = ActiveWorkbook.Path & "\mydb2.mdb"
    'ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFile
    
    'データベース接続
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = constr
    
    'クエリを作成
    Set cmd = New ADODB.Command
    cmd.CommandText = "select * from Table1"
    cat.Views.Append "newQuery1", cmd
                
ErrHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & vbCrLf & Err.Description
    End If
    
    Set cat = Nothing
    Set cmd = Nothing

End Sub

Sub Sample_ADOX_CreateQuery1()

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

'参照設定:Microsoft ADO Ext.6.0 for DDL and Security

Dim cat As ADOX.Catalog

Dim cmd As ADODB.Command

Dim constr As String

Dim DBFile As String

On Error GoTo ErrHandler

'データベースのパスと名前

'Access 2007以降（accdb ファイル）

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

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

'Access 2003以前（mdb ファイル）

'DBFile = ActiveWorkbook.Path & "\mydb2.mdb"

'ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFile

'データベース接続

Set cat = New ADOX.Catalog

cat.ActiveConnection = constr

'クエリを作成

Set cmd = New ADODB.Command

cmd.CommandText = "select * from Table1"

cat.Views.Append "newQuery1", cmd

ErrHandler:

If Err.Number <> 0 Then

MsgBox Err.Number & vbCrLf & Err.Description

End If

Set cat = Nothing

Set cmd = Nothing

End Sub

実行結果

サンプルVBAソースその２

　
Sub Sample_ADOX_CreateQuery2()
                    
    Dim cat As Object
    Dim cmd As Object
    Dim constr As String
    Dim DBFile As String
    
    On Error GoTo ErrHandler
    
    'データベースのパスと名前
    
    'Access 2007以降（accdb ファイル）
    DBFile = ActiveWorkbook.Path & "\mydb2.accdb"
    constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile
    
    'Access 2003以前（mdb ファイル）
    'DBFile = ActiveWorkbook.Path & "\mydb2.mdb"
    'ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFile
    
    'テータベース接続
    Set cat = CreateObject("ADOX.Catalog")
    cat.ActiveConnection = constr
        
    'クエリを作成
    Set cmd = CreateObject("ADODB.Command")
    cmd.CommandText = "select * from Table1"
    cat.Views.Append "newQuery1", cmd
            
ErrHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & vbCrLf & Err.Description
    End If
    
    Set cat = Nothing
    Set cmd = Nothing

End Sub

Sub Sample_ADOX_CreateQuery2()

Dim cat As Object

Dim cmd As Object

Dim constr As String

Dim DBFile As String

On Error GoTo ErrHandler

'データベースのパスと名前

'Access 2007以降（accdb ファイル）

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

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

'Access 2003以前（mdb ファイル）

'DBFile = ActiveWorkbook.Path & "\mydb2.mdb"

'ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFile

'テータベース接続

Set cat = CreateObject("ADOX.Catalog")

cat.ActiveConnection = constr

'クエリを作成

Set cmd = CreateObject("ADODB.Command")

cmd.CommandText = "select * from Table1"

cat.Views.Append "newQuery1", cmd

ErrHandler:

If Err.Number <> 0 Then

MsgBox Err.Number & vbCrLf & Err.Description

End If

Set cat = Nothing

Set cmd = Nothing

End Sub

サンプルVBAソースその３

　
Sub Sample_ADOX_DeleteQuery()

    '参照設定:Microsoft ActiveX Data Objects 6.1 Library
    '参照設定:Microsoft ADO Ext.6.0 for DDL and Security
                    
    Dim cat As ADOX.Catalog
    Dim cmd As ADODB.Command
    Dim constr As String
    Dim DBFile As String
    
    On Error GoTo ErrHandler
    
    'データベースのパスと名前
    
    'Access 2007以降（accdb ファイル）
    DBFile = ActiveWorkbook.Path & "\mydb2.accdb"
    constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile
    
    'Access 2003以前（mdb ファイル）
    'DBFile = ActiveWorkbook.Path & "\mydb2.mdb"
    'ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFile
    
    'データベース接続
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = constr
    
    'クエリを削除
    cat.Views.Delete "newQuery1"

ErrHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & vbCrLf & Err.Description
    End If
    
    Set cat = Nothing
    Set cmd = Nothing

End Sub

Sub Sample_ADOX_DeleteQuery()

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

'参照設定:Microsoft ADO Ext.6.0 for DDL and Security

Dim cat As ADOX.Catalog

Dim cmd As ADODB.Command

Dim constr As String

Dim DBFile As String

On Error GoTo ErrHandler

'データベースのパスと名前

'Access 2007以降（accdb ファイル）

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

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

'Access 2003以前（mdb ファイル）

'DBFile = ActiveWorkbook.Path & "\mydb2.mdb"

'ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFile

'データベース接続

Set cat = New ADOX.Catalog

cat.ActiveConnection = constr

'クエリを削除

cat.Views.Delete "newQuery1"

ErrHandler:

If Err.Number <> 0 Then

MsgBox Err.Number & vbCrLf & Err.Description

End If

Set cat = Nothing

Set cmd = Nothing

End Sub

実行前

実行後

サンプルVBAソースその４

　
Sub Sample_ADOX_CreateParamQuery1()

    '参照設定:Microsoft ActiveX Data Objects 6.1 Library
    '参照設定:Microsoft ADO Ext.6.0 for DDL and Security
                    
    Dim cat As ADOX.Catalog
    Dim cmd As ADODB.Command
    Dim constr As String
    Dim DBFile As String
    Dim strIDnum As String
    
    On Error GoTo ErrHandler
    
    'データベースのパスと名前
    
    'Access 2007以降（accdb ファイル）
    DBFile = ActiveWorkbook.Path & "\mydb2.accdb"
    constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile
    
    'Access 2003以前（mdb ファイル）
    'DBFile = ActiveWorkbook.Path & "\mydb2.mdb"
    'ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFile
            
    'データベース接続
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = constr
    
    'クエリを作成
    Set cmd = New ADODB.Command
    cmd.CommandText = "delete from Table1 where 登録ID > 10"
    cat.Views.Append "newQuery_Delete", cmd
    
ErrHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & vbCrLf & Err.Description
    End If
    
    Set cat = Nothing
    Set cmd = Nothing

End Sub