AccessVBA/ADOX の変更点
*ADOXを使ったデータベース操作 [#q09f2e30]
ADOオブジェクトでは、データベースに含まれるテーブルやクエリの一覧を取得することができません。~
これらの処理を行うには、ADOXオブジェクトを利用します。
-ADOXオブジェクトを使って何ができるか？~
ADOXの各オブジェクトを利用すると、新規にデータベースやテーブル、クエリを作成したり、既存のテーブルやクエリの構造を変更したりすることができます。
#contents
**オブジェクトとコレクション [#ad50b3e3]
データベースに複数のテーブルやクエリを作成することができるように、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)
**&color(red){ADOXを利用する前に}; [#z63b9d82]
ADOXを利用するには、必要なライブラリを手動で追加しなければなりません。~
+Visual Basic Editorで[ツール] - [参照設定] メニュー選択
+ダイアログボックスで「Microsoft ADO Ext.2.5 for DDL and Security」を選択
+[OK]ボタンをクリック
**宣言 [#qcd12d2f]
ADOXオブジェクトを提供すうコンポーネントはADOXです。
-例1
dim cat as New ADOX.Catalog
**データベースに接続する。 [#p0d835a5]
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
**テーブルの一覧を取得する [#hb274bb2]
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
**クエリの一覧を取得する [#a01ac820]
カレントデータベースに含まれるクエリ名を、イミディエイトウィンドウに出力するプログラム
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
**テーブルを構成するオブジェクト [#mc865261]
Colummnsコレクションはテーブルに含まれるフィールド~
Indexsコレクションはテーブルに設定されているインデックスです。
-フィールドの名前やデータ型、サイズはColumnsオブジェクトの次のプロパティで調べることができます。
--Nameプロパティ :フィールド名
--Typeプロパティ :データ型
--DefindSizeプロパティ :サイズ
***フィールドの一覧を取得する [#z7cb2682]
テーブルを構成するフィールドは、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
***インデックス一覧を取得する [#m636e8b2]
テーブルに設定されているインデックスは、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
**テーブルを作成する。 [#jeabf092]
+テーブルにフィールドを追加する
+必要であればインデックスを追加する
+作成したテーブルをデータベースに追加する
-書式
catalog.Tables.Append table
catalog :開いているCatalogオブジェクト
table :作成したTableオブジェクト
***テーブルにフィールドを追加する [#q044c5c9]
テーブルには、必ず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 :データサイズ(テキスト型のみ)
***データにインデックスを追加する [#yd1ec647]
+IndexオブジェクトのColumnsコレクションに、インデックスを設定するフィールドを追加する。
+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 '-----------------作成したインデックスをテーブルに追加
**新規にテーブルを作成する。 [#y5866b99]
ADOXを使って新たにテーブルを作成するときは、ADOXの階層の低い方から順番にオブジェクトを作成し、コレクションに追加してください。
+TableオブジェクトのColumnsコレクションにColumnオブジェクトを追加
+IndexオブジェクトのColumnsコレクションにColumnオブジェクトを追加
+TableオブジェクトのIndexesコレクションにIndexオブジェクトを追加
+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
**新規にクエリを作成する [#o14b85a1]
+ADOのCommandオブジェクトを作成する
+CommandオブジェクトのCommandTextプロパティにSQLステートメントを設定する。
+作成した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
***パラメータクエリを作成する [#fadc1966]
Accessデータベースのパラメータクエリやアクションクエリも、これと同じ方法で作成する事ができます。~
Accessデータベースのクエリオブジェクトを作成するときは、下記のようにすると「パラメータの入力」ダイアログボックスが表示されるようになります。
com.CommandText = "SELECT * FROM T_顧客 WHERE 性別 = 性別を入力"
**データベースを作成する [#a75004f4]
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
**オブジェクトを削除する方法 [#tc08bf60]
データベースをからテーブルを削除する、テーブルからフィールドを削除する、テーブルからインデックスを削除する・・・~
これらは、みな、コレクションからオブジェクトを削除する処理です。コレクションの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
