AccessVBA/ADOX

ADOXを使ったデータベース操作 †

ADOオブジェクトでは、データベースに含まれるテーブルやクエリの一覧を取得することができません。
これらの処理を行うには、ADOXオブジェクトを利用します。

  • ADOXオブジェクトを使って何ができるか？
    ADOXの各オブジェクトを利用すると、新規にデータベースやテーブル、クエリを作成したり、既存のテーブルやクエリの構造を変更したりすることができます。

オブジェクトとコレクション †

データベースに複数のテーブルやクエリを作成することができるように、Catalogオブジェクトにも複数のTableオブジェクトやViewオブジェクト、Procedureオブジェクトが含まれます。
同じ種類のオブジェクトの集まりをコレクションと呼びます。
コレクション名はオブジェクト名の複数形になります。

Catalogオブジェクト
     |
     |---Tableコレクション
     |   table(0) talble(1) table(2)・・・・---------------------Tableオブジェクト
     |                                                                    |
     |---Viewコレクション                                                 |--Columnsコレクション
     |   View(0) View(1) View(2)・・・・                                  |  Column(0) Column(1) Column(2)・・・
     |                                                                    |
     |---Proceduresコレクション                                           |--Indexesコレクション
         Procedure(0) Procedure(1) Procedure(2)・・・・                   |  Index(0) Index(1) Index(2)
                                                                          |
                                                                          |--Keysコレクション
                                                                             Key(0) Key(1) Key(2)

ADOXを利用する前に †

ADOXを利用するには、必要なライブラリを手動で追加しなければなりません。

  1. Visual Basic Editorで[ツール] - [参照設定] メニュー選択
  2. ダイアログボックスで「Microsoft ADO Ext.2.5 for DDL and Security」を選択
  3. [OK]ボタンをクリック

宣言 †

ADOXオブジェクトを提供すうコンポーネントはADOXです。

  • 例1 
    dim cat as New ADOX.Catalog

データベースに接続する。 †

CatalogオブジェクトのActiveConnectionプロパティにデータベースへの接続情報を代入する。
ADOのOpenメソッドのようなものはありません。

  • カレントデータベースに接続 
    catalog.ActiveConnection = CurrentProject.Connection
  • 他のデータベースに接続 
    catalog.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                            & "Data Source=D:\Access\Sample.mdb"
  • 例1 
    Public Sub adoxCurrentDB()
  Dim cat As New ADOX.Catalog
  cat.ActiveConnection = CurrentProject.Connection
  MsgBox "接続成功"
  Set cat = Nothing
End Sub

テーブルの一覧を取得する †

Tablesコレクションに含まれるすべてのTableオブジェクトからTypeプロパティの値が「TABLE」のものだけ抽出します。

  • 書式 
    For Each element In collection
  処理
next element

element     :コレクション内のオブジェクトを表すオブジェクト変数
collection  :コレクション名
  • 例1 
    Public Sub adoxTableList()
  Dim cat As New ADOX.Catalog
  Dim tb As New ADOX.Table
  cat.ActiveConnection = CurrentProject.Connection
  For Each tb In cat.Tables
    If tb.Type = "TABLE" Then
      Debug.Print tb.Name
    End If
  Next tb
   
  Set tb = Nothing: Set cat = Nothing
End Sub

クエリの一覧を取得する †

カレントデータベースに含まれるクエリ名を、イミディエイトウィンドウに出力するプログラム

Public Sub adoxQueryList()
  Dim cat As New ADOX.Catalog
  Dim vw As ADOX.View
  Dim proc As ADOX.Procedure
  cat.ActiveConnection = CurrentProject.Connection
  
  For Each vw In cat.Views
    Debug.Print vw.Name
  Next vw
  
  Debug.Print
  
  For Each proc In cat.Procedures
    Debug.Print proc.Name
  Next proc
  
  Set cat = Nothing
  
End Sub

テーブルを構成するオブジェクト †

Colummnsコレクションはテーブルに含まれるフィールド
Indexsコレクションはテーブルに設定されているインデックスです。

  • フィールドの名前やデータ型、サイズはColumnsオブジェクトの次のプロパティで調べることができます。
  • Nameプロパティ :フィールド名
  • Typeプロパティ :データ型
  • DefindSizeプロパティ :サイズ

フィールドの一覧を取得する †

テーブルを構成するフィールドは、TableオブジェクトのColumnsコレクションで管理されています。
Columnsコレクションに含まれるすべてのColumnsオブジェクトのNameプロパティを調べると、フィールドの一覧が表示されます。

  • 書式 
    Set table = catalog.Tables!tablename

table     :Tableオブジェクト型のオブジェクト変数
catalog   :開いているCatalogオブジェクト
tablename :参照するテーブル名
  • 例
    [T_顧客]テーブルに含まれるフィールドの一覧を取得 
    Public Sub adoxFieldList()
  Dim cat As New ADOX.Catalog
  Dim tb As ADOX.Table
  Dim col As ADOX.Column
  
  cat.ActiveConnection = CurrentProject.Connection
  Set tb = cat.Tables!T_顧客
  For Each col In tb.Columns
    Debug.Print col.Name, col.Type, col.DefinedSize
  Next col
  
  Set cat = Nothing: Set tb = Nothing: Set col = Nothing
End Sub

インデックス一覧を取得する †

テーブルに設定されているインデックスは、Indexesコレクションで管理されます。
インデックスの名前や動作は、Indexオブジェクトの次のプロパティで調べることができます。

  • Nameプロパティ :インデックスの名前
  • PrimaryKeyプロパティ :主キー(Trueの、主キーを表す)
  • Uniqueプロパティ :重複を許可するかどうか(Trueの時、重複した値を許可しない)
  • IndexNullプロパティ :Null値を許可するかどうか 
                          adindexNullsDisallow  :Null値を許可しない
                      adindexNullsLgnore    :NULL値を許可する
  • 例 Public Sub adoxIndexList() 
     Dim cat As New ADOX.Catalog
 Dim tb As ADOX.Table
 Dim col As ADOX.Column
 Dim idx As ADOX.Index
 
 cat.ActiveConnection = CurrentProject.Connection
 Set tb = cat.Tables!T_顧客
 
 For Each idx In tb.Indexes
   Debug.Print idx.Name, idx.PrimaryKey, idx.Unique, idx.IndexNulls
   For Each col In idx.Columns
     Debug.Print vttab & col.Name
   Next col
 Next idx
 
 Set tb = Nothing: Set col = Nothing: Set idx = Nothing: Set cat = Nothing
    End Sub

テーブルを作成する。 †

  1. テーブルにフィールドを追加する
  2. 必要であればインデックスを追加する
  3. 作成したテーブルをデータベースに追加する
  • 書式 
    catalog.Tables.Append table

catalog  :開いているCatalogオブジェクト
table    :作成したTableオブジェクト

テーブルにフィールドを追加する †

テーブルには、必ず1つ以上のフィールドが必要です。
TableオブジェクトのColumnsコレクションに、Appendメソッドを使ってColumnsオブジェクトを追加してください。

  • 書式 
    table.Collections.Append column, type, definesize

table      :開いているTableオブジェクト
column     :フィールド名
type       :データ型
            adVarWCar         :テキスト型
            adLongVarWChar    :メモ型
            adUnsignedTinyInt :数値型(バイト型)
            adSmallInt        :数値型(整数型)
            adInteger         :数値型(長整数型)
            adSingle          :数値型(単精度浮動小数点数型
            adDouble          :数値型(倍精度浮動小数点数型
            adGUID            :数値型(レプリケーションID型)
            adNumeric         :数値型(10進型)
            adDate            :日付/時刻
            adCurrency        :通貨型
            adInteger         :オートナンバー型
            adBoolean         :Yes/No型
            adLongVarBinary   :OLEオブジェクト型
            adLongVarWChar    :ハイパーテキスト型
definesize :データサイズ(テキスト型のみ)

データにインデックスを追加する †

  1. IndexオブジェクトのColumnsコレクションに、インデックスを設定するフィールドを追加する。
  2. TableオブジェクトのIndexesコレクションに、作成したIndexオブジェクトを追加する。
  • インデックスにフィールドを追加する
  • 書式 
    index.Columns.Append column

index    :開いているIndexオブジェクト
column   :フィールド名
  • テーブルにインデックスを追加する。
  • 書式 
    table.Indexes.Append index
table    :開いているTableオブジェクト
column   :インデックス名
  • 例 
    id.Name = "PrimaryKey"   '-----------------インデックスの名前を設定
id.PrimaryKey = True     '-----------------主キーを設定
idx.Columns.Append "ID"  '-----------------[ID]フィールドにインデックスを設定
table.Indexes.Append idx '-----------------作成したインデックスをテーブルに追加

新規にテーブルを作成する。 †

ADOXを使って新たにテーブルを作成するときは、ADOXの階層の低い方から順番にオブジェクトを作成し、コレクションに追加してください。

  1. TableオブジェクトのColumnsコレクションにColumnオブジェクトを追加
  2. IndexオブジェクトのColumnsコレクションにColumnオブジェクトを追加
  3. TableオブジェクトのIndexesコレクションにIndexオブジェクトを追加
  4. CatalogオブジェクトのTablesコレクションにTableオブジェクトを追加
  • 例 
    Public Sub adoxMakeTable()
  Dim cat As New ADOX.Catalog
  Dim tb As New ADOX.Table
  Dim idx As New ADOX.Index
  
  cat.ActiveConnection = CurrentProject.Connection
  tb.Name = "T_電話帳"
  
  'テーブルにフィールドを追加
  tb.Columns.Append "ID", adInteger
  tb.Columns.Append "氏名", adVarWChar, 20
  tb.Columns.Append "生年月日", adDate
  tb.Columns.Append "電話番号", adVarWChar, 15
  
 'インデックス設定
  idx.Name = "PrimaryKey"               'インデックス名
  idx.PrimaryKey = True                 '主キー
  idx.Unique = True                     '固有
  idx.IndexNulls = adIndexNullsDisallow 'Null無視
  idx.Columns.Append "ID"
  
  'テーブルにインデックスを追加
  tb.Indexes.Append idx
  'データベースにテーブルを追加
  cat.Tables.Append tb
  
  '終了処理
  Set tb = Nothing: Set idx = Nothing: Set cat = Nothing
  Beep
  MsgBox "終了しました"
End Sub

新規にクエリを作成する †

  1. ADOのCommandオブジェクトを作成する
  2. CommandオブジェクトのCommandTextプロパティにSQLステートメントを設定する。
  3. 作成したCommandオブジェクトをADOXのViewコレクションに追加する。
  • 書式 
    catalog.Views.Append name, command

catalog     :開いているCatalogオブジェクト
name        :クエリ名
command     :作成したCommandオブジェクト
  • 例
    [性別]フィールドの値が[「1」のレコードを抽出する「Q_男性」クエリを作成する 
    Sub MyCreateQuery()

    Dim Cat As New ADOX.Catalog
    Dim Cmd As New ADODB.Command
    Dim mySQL As String

    Cat.ActiveConnection = CurrentProject.Connection
    mySQL = "SELECT * FROM T_顧客 WHERE 性別=1 "

    Cmd.CommandText = mySQL '
    Cat.Views.Append "Q_男性 ", Cmd

    Set Cat = Nothing
End Sub

パラメータクエリを作成する †

Accessデータベースのパラメータクエリやアクションクエリも、これと同じ方法で作成する事ができます。
Accessデータベースのクエリオブジェクトを作成するときは、下記のようにすると「パラメータの入力」ダイアログボックスが表示されるようになります。

com.CommandText = "SELECT * FROM T_顧客 WHERE 性別 = 性別を入力"

データベースを作成する †

CatalogオブジェクトのCreateメソッドを利用すると、新規データベースを作成することができます。

  • 書式 
    catalog.Create connectionstring

catalog          :Catalogオブジェクト型のオブジェクト変数
connectionstring :データベースの接続情報文字列
  • 例 
    Public Sub adoxMakeDB()
  Dim cat As New ADOX.Catalog
  
  cat.Create "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Sample.mdb"
  
  Set cat = Nothing
End Sub

オブジェクトを削除する方法 †

データベースをからテーブルを削除する、テーブルからフィールドを削除する、テーブルからインデックスを削除する・・・
これらは、みな、コレクションからオブジェクトを削除する処理です。コレクションのDeleteメソッドを利用します。

  • 書式 
    collection.Delete object

collection  :コレクション名
object      :オブジェクト名
  • 例1
    [T_電話帳]テーブルを削除する。 
    catalog.Tables.Delete "T_電話帳"
  • 例2
    [氏名]フィールドを削除する 
    table.Column.Delete "氏名"
  • 例3 
    Public Sub adoField()
  Dim cat As New ADOX.Catalog
  Dim tb As ADOX.Table
  
  cat.ActiveConnection = CurrentProject.Connection
  Set tb = cat.Tables!T_女性
  tb.Columns.Delete "郵便番号"
End Sub