復刻 ADOX入門講座 全９回(完了)

01.ADOXの概要 ： ADOX入門講座

http://www.accessclub.jp/ado/adox/01.html
Access2000以降、テーブルやクエリの新規作成、構造や定義の変更、一覧の参照するといつた操作を行う際には、ADOXオブジェクトモデルを利用するようになりました。

ADOXは、ADOの拡張機能です。

留意点

ADOXを利用する場合、参照設定ダイアログで事前に利用できるように設定を行う必要があります。
参照設定ダイアログを見ると、Access2003バージョンでは、Microsoft ADO Ext 2.7 DLL And Security のバージョン（2005/07現在）がインストールされていますが、他のバージョンも存在します。 これらバージョン間で連携は取れていますが、できるだけ最新版にチェックを入れるようにして下さい。

ADOX（ActiveX Data Objects Extensions for Data Definition Language and Security）の概要

ADOXを用いると、以下の操作が思いのまま実現できます。

1. 新規データベースの作成
2. 新規テーブルの作成
3. 新規クエリの作成
4. 既存テーブルの定義変更
5. 既存テーブルへのフィールドの追加、削除
6. 既存クエリへのフィールドの追加、削除
7. テーブル一覧の参照
8. クエリ一覧の参照

留意点

テーブルやクエリのレコード操作は、ADOを用いて行います。
ADOXオブジェクト モデル

下図は、ADOXオブジェクトモデルの階層構造です。各コレクションとオブジェクトが組み合わされています。この中では、Catalog オブジェクト、Table オブジェクト、Column オブジェクトが主力です。

1. Catalog オブジェクト
   最上層にあるCatalog オブジェクトは、データベースへの接続を表わします。ADOXを利用する場合、必ず作成する必要があります。
2. Table オブジェクト
   データベースに存在するテーブル名、クエリ名の一覧を参照する際に利用します。
3. Column オブジェクト
   テーブルフィールドの作成、フィールド一覧の参照、インデックスの作成を行なう際に利用します。
4. Index オブジェクト
   データベース テーブルのインデックスの作成、主キーの作成を行います。
5. Key オブジェクト
   データベース テーブルの主、外部、または一意のキー フィールドを表します。
6. Group オブジェクト
   保護されているデータベースへのアクセス権を持つグループ アカウントを表します。
7. User オブジェクト
   保護されているデータベースへのアクセス権を持つユーザー アカウントを表します。
8. Procedure オブジェクト
   アクションクエリ、パラメータクエリの一覧を参照する際に利用します。

Catalogオブジェクトは CurrentDBより上の集合であるが、レポート、フォームは含まない。データベースの一部だけである。

Dim cnn As New ADODB.Connection 
Dim cat As New ADOX.Catalog
    cnn.Open "Provider='Microsoft.ACE.OLEDB.12.0';" & _
    "Data Source= '" & CreateObject("WScript.Shell").expandenvironmentstrings("%USERPROFILE%") & "\Documents\" & _
    "Database1.accdb';"
    Set cat.ActiveConnection = cnn 

    'Clean up 
    cnn.Close 
    Set cat = Nothing 
    Set cnn = Nothing 

まずこのように開始、終了は記述されるのが定石である。ただし、CurrentDBにはこの方法では接続できず、エラーになってしまう。閉じるときにはcatalogではなく接続を閉じていることにも注意したほうが良い。
catはSetの後、既存のデータベースか、新規データベースを作成して接続するすることになるなので、DBオブジェクトよりは上位であることがわかる。現在ではJet.OleDB4.0は64bitだと使えない。
しかし、VBSを32bitのSysWowのCScripかWScriptを使うと使えないことはない。

もちろん、CurrentDBのように直接現在のファイルに接続するには

Dim cat As New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
'Clean Up
Set cat = Nothing

となるが、こちらはCloseは必要がない。Sampleを丸写しすると時々間違う。
また、Ace.OLEDB.12.0はOffice 2010-2019まで使える。
ACE.OLEDB15.0は2013のみ、ACE.OLEDB16.0は2016-2019で使える。

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:*Database1.accdb;Mode=Share Deny None;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False;

Provider=Microsoft.ACE.OLEDB.16.0;User ID=Admin;Data Source=C:*Database1.accdb;Mode=Share Deny None;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False;

Providerのバージョンを変えると、cn.ConnectionStringで表示されるACEのバージョンも変わる。
また、mdbだと接続ができない。この場合は

Dim Cdb As DAO.Database
Set Cdb = DAO.OpenDatabase("C:\hoge\hoge.MDB")

のようにDAOを使うか、accdbに変換する。Access97のmdbの場合はPowershell等を使って変換する。
何れにせよバックアップをとってから接続すること。

ADOXの参照設定

Access2000以上でADOXを利用するには、下記の参照設定が必要になります。

VBE画面に切り替えます。
メニューバーの ツール から 参照設定 をクリックします。
下図のような参照設定ダイアログが表示されます。

Microsoft ADO Ext 2.7 DLL And Securityを有効にします。（編注参照）
なお、OS、Office等のバージョンの違いよって、ADOXのバージョンが異なります。

2005/07/29

編注

2021/05/15現在では、

Microsoft ADO Ext 6.0 DLL And Securityを有効にします。

また、古いバージョンは
Microsoft ADO Ext 2.8 DLL And Security
しか残っていない。

ADOXオブジェクト機能を利用する ： ADOX入門講座

http://accessclub.jp/ado/adox/02.html
VBAでADOXオブジェクトを利用する場合、定められた手順に従ってプロシージャ内で記述していきます。大別すると以下のようになります。

1. オブジェクト変数を宣言する。
2. 宣言済みのオブジェクト変数にオブジェクトの参照を割り当てる。
3. データベースに接続する。
4. オブジェクトを削除する。

この段階でVBAからADOXオブジェクトに接続が完了し、利用できるようになります

1.オブジェクト変数の宣言

プロシージャ内で、以下のステートメントを用いてオブジェクト変数を宣言します。

1. Dimステートメント
2. Staticステートメント
3. Privateステートメント
4. Publicステートメント

基本構文

基本構文はそれぞれ、以下のようになります。Newキーワードは省略可能です。

Dim オブジェクト変数 As （New） クラス名
Static オブジェクト変数 As （New） クラス名
Private オブジェクト変数 As （New） クラス名
Public オブジェクト変数 As （New） クラス名

全てのオブジェクトはCatalog オブジェクトに属しますから、ADOXオブジェクトを利用する際は、以下の構文を最初に記述します。

Dim cat As ADOX.Catalog

2.宣言済みのオブジェクト変数にオブジェクトの参照を割り当て

オブジェクト変数を宣言した後、宣言済みのオブジェクト変数にオブジェクトの参照をSetステートメントを用いて割り当てます。基本構文は以下のようになります。

Set オブジェクト変数 = New.ADOX.クラス名

一例

Dim cat As ADOX.Catalog
Set cat = New.ADOX.Catalog

Newキーワード

オブジェクトの参照を割り当てる際に、Newキーワードを用いると直接オブジェクトへの参照を代入することができます。

CドライブにあるSample.mdbファイルに接続する場合

CatalogオブジェクトのActiveConnectionプロパティを用いてデータベースに接続します。ActiveConnectionプロパティの基本構文は、以下のようになります。

CドライブにあるSample.mdbファイルに接続する場合

Accessに接続する場合のProviderは、Microsoft.Jet.OLEDB.4.0になります。ADOのConnectionStringプロパティと利用方法がよく似ています。

Dim Cat As ADOX.Catalog
Set Cat = New ADOX.Catalog
Cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                  "Data Source=C:\Sample.mdb"

カレントデータベースに接続する場合

カレントデータベースの場合は、CurrentProjectプロパティを用いて簡潔に接続することができます。

Dim Cat As ADOX.Catalog
Set Cat = New ADOX.Catalog
Cat.ActiveConnection = CurrentProject.Connection

オブジェクトを削除する

ADOXオブジェクトを用いてデータベースに接続し全ての処理を終えた後は、オブジェクト変数を削除しメモリーを開放します。Setステートメントを用いてオブジェクト変数を代入したのでも削除する場合もSetステートメントを用います。以下、基本構文です。

Set Cat = Nothing

03.ADOXを用いてデータベースファイルを新規に作成する ： ADOX入門講座

http://www.accessclub.jp/ado/adox/03.html
CatalogオブジェクトのCreateメソッドを用いると、新規データベースを作成することができます。

留意点

事前にデータベース名やパス（フォルダーの作成等）を決めておく必要があります。

Createメソッドの基本構文

Createメソッドの基本構文は以下のようになります。

Catalog.Create ConnectionString

項目	意味
Catalog	開いているCatalogオブジェクトです。
ConnectionString	データベースの接続情報文字列です。

ConnectionStringの記述内容

ConnectionStringに設定する内容は、作成予定のデータベースの種類と格納場所（パス）になります。

1. Providerキーワードは、データベースの種類を記述します。
2. DataSourceキーワードは、データベースの格納場所を記述します。

CドライブにAccess（Sample.mdb）を作成する場合

ConnectionStringの記述内容は、以下のようになります。

ConnectionString = "Provider=Microsoft.Jet.OLE.4.0;" & _
         "DataSource=C:\Sample.mdb"

ADOXを用いてAccess2000形式のデータベースを作成する

ADOXを用いてAccess2000形式のデータベースを作成するプロシージャは以下になります。 このプロシージャを実行すると、CドライブにSampleDataBase.mdbファイルをが自動的に作成されます。

Sub MyNewDatabase()
On Error GoTo エラー
Dim Cat As ADOX.Catalog
Dim ConnectionString As String

Set Cat = New ADOX.Catalog
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
Cat.Create ConnectionString & "C:\SampleDataBase.mdb" ' --- A

Set Cat = Nothing

Exit Sub

エラー:

If Err.Number = -2147217897 Then ' --- B
MsgBox "すでに同名のデータベースが存在します。", vbCritical
Else
MsgBox Err.Number & vbNewLine & Err.Description, vbCritical
End If

End Sub

解説

Createメソッドを用いてデータベースを新規作成します。
同名のファイルが存在している場合エラーが発生しますので、注意を喚起します。

2005/08/12

04.ADOXを用いてテーブル名を取得する ： ADOX入門講座

CatalogオブジェクトのTablesコレクションには、各種のTableオブジェクトが含まれています。Tableオブジェクトの種類を調べるには、付属するTypeプロパティを用います。Accessの標準テーブルを求めるには、TypeプロパティにTABLEを指定します。なお、テーフル名の取得は、Tableオブジェクトに付属するNameプロパティを利用します。

留意点

テーブル名の取得は参照のみ可能です。テーブル名の変更等はできません。

Typeプロパティの一覧

Typeプロパティに設定するTablesコレクションのオブジェクト一覧は以下のようになります。

種類	意味
TABLE	Accessの標準テーブル
VIEW	Accessのパラメータを含まない選択クエリ
LINK	ODBCデータリソース以外からリンクされたテーブル
ACCESS TABLE	Accessのシステムテーブル
SYSTEM TABLE	Microsoft Jet システムテーブル
PASS-THROUGH	ODBCデータリソースからリンクされたテーブル

編注補足

このViewを使うことでパラメータークエリを除外して処理（一括してExcelに出力するなど）をすることができる。パラメータークエリは利点はあるが、通常のクエリと混合するとパラメータークエリだけ値を要求されてクエリを一括して処理することができない場合がある。このため、VBAで処理する場合にはパラメータークエリではなく、一時クエリを使うほうが良いかもしれない。

ADOXを用いてテーブル名を取得する

このプロシージャを実行すると、メッセージボックスにテーブル名の一覧が表示されます。テーブル毎のデータバックアップや削除などでテーブル名を指定する際に利用されるといいでしょう。

Sub MyTableName()

On Error GoTo エラー

Dim Cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim strmsg As String

Set Cat = New ADOX.Catalog
Cat.ActiveConnection = CurrentProject.Connection 'カレントデータベースに接続

For Each tbl In Cat.Tables
    If tbl.Type = "TABLE" Then ' --- A
        strmsg = strmsg & vbNewLine & "・" & tbl.Name
    End If
Next tbl


MsgBox strmsg
Set Cat = Nothing
Exit Sub
エラー:
MsgBox Err.Number & vbNewLine & Err.Description, vbCritical
Exit Sub
End Sub

解説

Tablesコレクションのオブジェクトタイプに、TABLEを指定しているので標準テーブル名のみ取得することができます。

2005/08/30

05.ADOXを用いてクエリ名を取得する ： ADOX入門講座

http://www.accessclub.jp/ado/adox/05.html
CatalogオブジェクトのViewコレクションには、 パラメータを含まない選択クエリが管理されています。また。Procedureコレクションには、パラメータを含む選択クエリやアクションクエリが管理されています。これらは。それぞれのオブジェクトにあるNameプロパティの値によってクエリ名を参照することができます。

留意点

クエリ名の取得は参照のみ可能です。クエリ名の変更等はできません。

ADOXを用いてクエリ名を取得する

このプロシージャを実行すると、メッセージボックスにクエリ名の一覧が表示されます。クエリ毎のデータバックアップや削除などでクエリ名を指定する際に利用されるといいでしょう。

Sub MyQueryName()
On Error GoTo エラー

Dim Cat As ADOX.Catalog
Dim viw As ADOX.View
Dim pcd As ADOX.Procedure
Dim strmsg As String

Set Cat = New ADOX.Catalog
Cat.ActiveConnection = CurrentProject.Connection 'カレントデータベースに接続

For Each viw In Cat.Views ' --- A
strmsg = strmsg & vbNewLine & "・" & viw.Name
Next viw

For Each pcd In Cat.Procedures ' --- B
If Left(pcd.Name, 1) <> "~" Then ' --- C
strmsg = strmsg & vbNewLine & "・" & pcd.Name
End If
Next pcd

MsgBox strmsg
Set Cat = Nothing

Exit Sub

エラー:

MsgBox Err.Number & vbNewLine & Err.Description, vbCritical
Exit Sub

End Sub

解説

A.ViewsオブジェクトのNameプロパティを用いて、パラメータを含まない選択クエリー名を取得します。
B.ProceduresオブジェクトのNameプロパティを用いて、パラメータを含む選択クエリーとアクションクエリ名を取得します。
C.チルダ（~）から始まるクエリ名を除外しています。

チルダ（~）を含むクエリを含めた場合。

2005/08/31

06.ADOXを用いてテーブルを新規に作成する ： ADOX入門講座

http://accessclub.jp/ado/adox/06.html
ADOXを用いてテーブルを新規に作成する ： ADOX入門講座
ADOX機能を用いてテーブルを新規作成することができます。方法は、Catalogオブジェクト （フィールドに該当）を1つ以上作成しTableオブジェクト（テーブルに該当）に追加します。次に、CatalogオブジェクトのTablesコレクションに追加 （Appendメソッド）します。

留意点

フィールドを持たないテーブルを作成することはできません。
テーブル作成の基本構文

テーブルを作成するADOXの構文は、以下のようになります。

Catalog.Tables.Append.Table

解説

項目	意味
Catalog	開いているCatalogオブジェクトです。
Tables	開いているTablesコレクションです。

$\style{background-color:DodgerBlue;}{フィールド作成の基本構文}$

フィールドを作成するADOXの構文は、以下のようになります。

Table.Columns.Append Column,Type,DefineSize

解説

項目	意味	備考
Table	開いているTableオブジェクトです。	省略不可
Column	フィールド名を文字列で指定します。	省略不可
Type	フィールドのデータ型を定数で指定します。	省略不可
DefineSize	フィールドサイズのデータ型を定数で指定します。	省略可

フィールドのデータ型定数

フィールドのデータ型定数一覧は以下のとおりです。

定数	データ型名称
adGUID	オートナンバー型
adVarWChar	テキスト型
adLongVarWChar	メモ型
adCurrency	通貨型
adDate	日付時刻型
adInteger	長整数型
adSmallInt	整数型
adBoolean	ブール型
adDouble	倍精度浮動少数点型
adSingle	単精度浮動少数点型

編注

実際のテーブルを分析すると次のようになっている

定数	データ型名称	値
adVarWChar	テキスト型	202
adLongVarWChar	メモ型	203
adLongVarWChar	ハイパーリンク型	203
adCurrency	通貨型	6
adDate	日付時刻型	7
adUnsignedTinyInt	バイト型	17
adSmallInt	整数型(Integer)	2
adInteger	長整数型(Long)	3
adSingle	単精度浮動少数点型	4
adDouble	倍精度浮動少数点型	5
adBoolean	ブール型	11
adGUID	GUID型	72
adLongVarBinary	OLEオブジェクト型	205
adLongVarChar	Indicates a long string value.	201

ハイパーリンクとメモ型は同じ203、Longは分かりづらいが定義からするとadInteger、Byte型は0から255までなので、adUnsignedTinyIntになるようだ。ユニコードを使っているからか、もっとも使うテキスト型はadVarWCharになる。201と203の違いはヌル値で終わるUnicode型かどうか。おそらく、OracleやMySQLとかを意識して決めているようだ。

フィールド名（ADO）Field オブジェクト ExcelWork.info 2017/09/05
DataTypeEnum

編注その2 Column Propertis Table

Adox.Columns Dao.FIelds
Adox.Column Dao.Field
という関係がある。
Tab
たとえば、
.Columns.Item("ID").Properties("AutoIncrement") = True ' --- C
は以下の表を知っていると次のようにかける
.Columns.Item("ID").Properties(0) = True ' --- C
また設定を読み取ることもできる

Columns.Item("ID").Properties(“Jet OLEDB:Allow Zero Length").Value

INDEX	Name	Additional
0	AutoIncrement	インデックス列にオートナンバー型を設定するとき使用する
1	Default	その列（Dao.Field,Adox.Column）の値を設定できる
2	Description	詳細を設定できる
3	Nullable	Trueだと空欄を許可。Falseだと空欄は不可でなにか入力しなければならない。
4	Fixed Length	Trueだと固定長。固定長は普通は使わないのでせってしないこと
5	Seed	インデックスの初期値と考えられる
6	Increment	インデックス（AutoNumber型）の列の増分を設定す。しないと1になる。ここは通常は設定しない。
7	Jet OLEDB:Column Validation Text
8	Jet OLEDB:Column Validation Rule	入力規則
9	Jet OLEDB:IISAM Not Last Column
10	Jet OLEDB:AutoGenerate
11	Jet OLEDB:One BLOB per Page
12	Jet OLEDB:Compressed UNICODE Strings	テキスト型のフィールドのユニコード圧縮をTrue
13	Jet OLEDB:Allow Zero Length	True 空文字の許可
14	Jet OLEDB:Hyperlink	メモ型の列をハイパーリンク型に設定する

インデックスのようなオートナンバー型で重要になるのが0,(5,6)
空欄を許可、空文字を許可の組み合わせが一番厳密ではない
なお、キャプションについてはDaoを使うらしい。

ADOXでのTable標題プロパティの設定 microsoft.public.jp.access.narkive
ここによるとCaptionはDaoの固有のプロパティのためADOXではなくDAOでしか設定できない。

Dim DB As Database
Dim fld As DAO.Field
Dim prop As DAO.Property
Set DB = CurrentDb
Set fld = DB.TableDefs("テーブル1").Fields("コード")
Set prop = fld.CreateProperty("Caption", dbMemo, "テスト")
fld.Properties.Append prop

この書き方も変則的だが、一度設定すると、後からは値だけ指定すれば良いらしい。

Excel VBAメモ Accessデータベースの空文字列を許可のプロパティを取得してみる

Microsoft Access データベース (.mdb または .accdb) で次の関数を使用すると、オートナンバー型フィールドのシード値をプログラムからリセットできます。この関数をモジュールに追加して、デバッグ ウィンドウで実行することも、コマンド ボタンやマクロから呼び出すこともできます。

Access データベースの最適化後オートナンバー型のフィールドがリセットされない

注 : このコードを正しく実行するには、Microsoft ActiveX Data Objects 2.x Library および Microsoft ADO Ext 2.x for DDL and Security (2.x は 2.1 またはそれ以降) の両方を参照する必要があります。これを行うには、Visual Basic Editor の [ツール] メニューで [参照設定] をクリックします。[Microsoft ActiveX Data Objects 2.x Library] および [Microsoft ADO Ext 2.x for DDL and Security] の各チェック ボックスがオンになっていることを確認します。

Function ChangeSeed(strTbl As String, strCol As String, lngSeed As Long) As Boolean
'You must pass the following variables to this function.
'strTbl = Table containing autonumber field
'strCol = Name of the autonumber field
'lngSeed = Long integer value you want to use for next AutoNumber.
Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column
'Set connection and catalog to current database.
Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn
Set col = cat.Tables(strTbl).Columns(strCol)
col.Properties("Seed") = lngSeed
cat.Tables(strTbl).Columns.Refresh
If col.Properties("seed") = lngSeed Then
    ChangeSeed = True
Else
    ChangeSeed = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing
End Function

Tableのプロパティ

ADOX in Detail CODEGURU

Index	Property name	Property type	Read/Write	Additinal
0	Temporary Table	adBoolean	Read only
1	Jet OLEDB:Table Validation Text	adBSTR	R/w
2	Jet OLEDB:Table Validation Rule	adBSTR	R/w
3	Jet OLEDB:Cache Link Name/Password	adBoolean	R/w
4	Jet OLEDB:Remote Table Name	adBSTR	R/w
5	Jet OLEDB:Link Provider String	adBSTR	R/w
6	Jet OLEDB:Link Datasource	adBSTR	R/w
7	Jet OLEDB:Exclusive Link	adBoolean	R/w
8	Jet OLEDB:Create Link	adBoolean	R/w
9	Jet OLEDB:Table Hidden In Access	adBoolean	R/w

Viewのプロパティ

Viewつまりクエリのプロパティは通常は取得できない。
しかし、Commandの後に、強引に設定する

Sub adoxtest()
Dim cat As New ADOX.Catalog
Dim cTbl As ADOX.Table
Dim cn As New ADODB.Connection
Dim aPrs As ADODB.Properties, aPrn As ADODB.Property
Dim cPrs As ADOX.Properties, cPr As ADOX.Property
Dim xViews As ADOX.Views, xVw As ADOX.View
Dim i As Long, buf As String
    'cn.Open "Provider='Microsoft.ACE.OLEDB.12.0';" & _
    "Data Source= '" & CreateObject("WScript.Shell").expandenvironmentstrings("%USERPROFILE%") & "\Documents\" & _
    "Database1.accdb';"
     Set cat.ActiveConnection = CurrentProject.Connection
    Set xViews = cat.Views
    Set xVw = xViews.Item(0)
    Set cPrs = xVw.Command.Properties ' <----ここが重要なポイント
    For i = 0 To cPrs.Count - 1
    buf = ""
    Set cPr = cPrs(i)
    buf = "|" & i & "|" & cPr.Name & "|" & cPr.Type & "|" & cPr.Value & "|" & cPr.Attributes & "| |"
    Debug.Print buf
    Next

' cn.Close
End Sub

Index	Property name	Property type	Value	Attributes	Additinal
0	Preserve on Abort	11	False	513
1	Blocking Storage Objects	11	True	513
2	Use Bookmarks	11	False	1537
3	Skip Deleted Bookmarks	11	False	513
4	Bookmark Type	3	1	513
5	Cache Deferred Columns	11	False	513
6	Fetch Backwards	11	False	1537
7	Hold Rows	11	False	1537
8	Scroll Backwards	11	False	1537
9	Column Privileges	11	True	513
10	Preserve on Commit	11	True	513
11	Defer Column	11	True	513
12	Delay Storage Object Updates	11	True	513
13	Immobile Rows	11	False	513
14	Literal Bookmarks	11	False	513
15	Literal Row Identity	11	False	513
16	Maximum Open Rows	3	1	513
17	Maximum Pending Rows	3	1	513
18	Maximum Rows	3	0	513
19	Column Writable	11	True	513
20	Memory Usage	3	0	513
21	Notification Phases	3	27	513
22	Bookmarks Ordered	11	False	513
23	Others' Inserts Visible	11	False	513
24	Others' Changes Visible	11	False	1537
25	Own Inserts Visible	11	False	1537
26	Own Changes Visible	11	False	1537
27	Quick Restart	11	True	1537
28	Reentrant Events	11	False	513
29	Remove Deleted Rows	11	True	513
30	Report Multiple Changes	11	True	513
31	Row Privileges	11	False	513
32	Row Threading Model	3	1	513
33	Objects Transacted	11	True	513
34	Updatability	3	0	1537
35	Strong Row Identity	11	False	513
36	IAccessor	11	True	513
37	IColumnsInfo	11	True	513
38	IColumnsRowset	11	False	1537
39	IConnectionPointContainer	11	False	1537
40	IRowset	11	True	513
41	IRowsetChange	11	False	1537
42	IRowsetIdentity	11	False	1537
43	IRowsetInfo	11	True	513
44	IRowsetLocate	11	False	1537
45	IRowsetResynch	11	False	513
46	IRowsetScroll	11	False	1537
47	IRowsetUpdate	11	False	1537
48	ISupportErrorInfo	11	False	1537
49	ILockBytes	11	False	1537
50	ISequentialStream	11	True	1537
51	IStorage	11	False	513
52	IStream	11	False	513
53	IRowsetIndex	11	False	1537
54	Column Set Notification	3	3	513
55	Row Delete Notification	3	3	513
56	Row First Change Notification	3	3	513
57	Row Insert Notification	3	3	513
58	Row Resynchronization Notification	3	3	513
59	Rowset Release Notification	3	3	513
60	Rowset Fetch Position Change Notification	3	3	513
61	Row Undo Change Notification	3	3	513
62	Row Undo Delete Notification	3	3	513
63	Row Undo Insert Notification	3	3	513
64	Row Update Notification	3	3	513
65	Append-Only Rowset	11	False	1537
66	Change Inserted Rows	11	True	513
67	Return Pending Inserts	11	False	513
68	IConvertType	11	True	513
69	Notification Granularity	3	2	513
70	Access Order	3	2	513
71	Lock Mode	3	1	1537
72	Server Data on Insert	11	True	1537
73	IRowsetCurrentIndex	11	False	1537
74	IRowsetFind	11	False	1537
75	Find Operations	3	27	513
76	Client Cursor	11	True	1537
77	IRowsetRefresh	11	True	513
78	Jet OLEDB:Validate Rules On Set	11	False	1537
79	Jet OLEDB:Fat Cursor Cache Size	3	0	1537
80	Jet OLEDB:Enable Fat Cursors	11	False	1537
81	Jet OLEDB:Partial Bulk Ops	3	0	1537
82	Jet OLEDB:Pass Through Query Connect String	8		1537
83	Jet OLEDB:ODBC Pass-Through Statement	11	False	1537
84	Jet OLEDB:Grbit Value	3	0	1537
85	Jet OLEDB:Use Grbit	3	0	1537
86	Jet OLEDB:Stored Query	11	False	1537
87	Jet OLEDB:Locking Granularity	3	2	1537
88	Jet OLEDB:Bulk Transactions	3	0	1537
89	Jet OLEDB:Inconsistent	11	True	1537
90	Jet OLEDB:Pass Through Query Bulk-Op	11	False	1537
91	Bookmarkable	11	False	1537

Table,Columns,Columnとの違いは接続方法で4つ数が違う。
Set cat.ActiveConnection = CurrentProject.Connectionのように現在のデータベースに接続している場合と、外部のデータベースに接続している場合にはの4つは出現しない。
これらのプロパティは一部はクエリのデザインビューのプロパティシートにある。フィールドのプロパティシートではないことに注意してほしい。

最大レコード数は、このプロパティと考えられる。

Microsoft OLE DB Provider for Microsoft Jet

留意点

テキスト型（adVarWChar）とメモ型（adLongVarWChar）の場合は、フィールドサイズ（DefineSize）を指定します。

$\style{background-color:DodgerBlue;}{ADOXを用いて新規テーブルを作成する}$

このプロシージャを実行すると、ID、名前、生年月日、性別フィールドを持った「tbl_sample」テーブルが新規作成されます。但し、同名のテーブルが既に存在している場合は、エラーが発生しますので注意して下さい。

Sub MyCreateTable()

    On Error GoTo エラー

    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table

    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection 'カレントデータベースに接続

    Set tbl = New ADOX.Table
    tbl.Name = "tbl_sample" ' --- A
    Set tbl.ParentCatalog = cat ' --- B

    With tbl
        .Columns.Append "ID", adInteger
        .Columns.Item("ID").Properties("AutoIncrement") = True ' --- C
        .Columns.Append "名前", adVarWChar, 50
        .Columns.Append "生年月日", adDate
        .Columns.Append "性別", adVarWChar, 10
    End With

    cat.Tables.Append tbl ' --- D
    Set cat = Nothing

    Exit Sub

エラー:

    MsgBox "エラーID : " & Err.Number & vbNewLine & Err.Description, 16

End Sub

解説

A. テーブル名を指定します。
B. フィールドプロパティを操作するためにParentCatalog プロパティをセットします。
C. IDフィールドを「数値型」から「オートナンバー型」に変更します。
D. テーブルをTablesコレクションに追加します。

07.ADOXを用いてテーブルを削除する ： ADOX入門講座

ADOX機能を用いてテーブルを削除するには、Deleteメソッドを用いてオブジェクトを削除する形式をとります。方法は、 削除したいオブジェクト名を指定し、Deleteオブジェクトを実行するだけです。

$\style{background-color:DodgerBlue;}{テーブル作成の基本構文}$

テーブルを削除するADOXの構文は、以下のようになります。

Collection.Delete 削除対象のオブジェクト

解説

項目	意味
Collection	開いているコレクションです。

$\style{background-color:DodgerBlue;}{ADOXを用いてテーブルを削除する}$

このプロシージャを実行すると、tbl_sampleテーブルが削除されます。但し、該当テーブルが存在している場合を想定してエラー処理は必需品です。

Sub MyDeleteTable()

On Error GoTo エラー

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection 'カレントデータベースに接続

cat.Tables.Delete "tbl_sample" '--- A

Set cat = Nothing

Exit Sub

エラー:

If Err.Number = 3265 Then
MsgBox "該当テーブルが存在しません。", vbCritical
Else
MsgBox "エラーID : " & Err.Number & vbNewLine & Err.Description, 16
End If

解説

A.削除するテーブル名を指定します。

08.ADOXを用いて選択クエリを作成する ： ADOX入門講座

http://accessclub.jp/ado/adox/08.html
ADOX機能を用いて選択クエリを作成するには、Viewsコレクションに新規ViewオブジェクトをAppendメソッドを用いて追加する形式をとります。クエリフィールド等の指定は、ADOのコマンドテキスト設定を利用します。

留意事項

ADOとADOXを利用しますので、共に参照設定が必要になります。

選択クエリを作成するADOXの構文は、以下のようになります。

Collection.Views.Append "新規クエリ名", Cmd

解説

項目	意味
Collection	開いているコレクションです。
Views 開いているViewsコレクションです。
Append	Appendメソッドです。
新規クエリ名	作成し追加するプロシージャの名前を指定する文字列型の値を指定します。
Cmd	作成し追加するプロシージャを表すADO の Command オブジェクトを指定します。

ADOのコマンドテキスト設定

クエリフィールドの追加設定は、ADOのコマンドテキストを利用します。

cmd.CommandText = SQL構文

$\style{background-color:DodgerBlue;}{ADOXを用いて選択クエリを作成する}$

このプロシージャを実行すると、Access関連データだけを抽出したQ_providerクエリが新規作成されます。なお、元になるtbl_providerテーブルは、以下のとおりです。

ID	FileName	kakuchoshi
1	Access	mdb
2	Access_MDE	mde
3	Excel	xls
4	Word	doc

作成までの流れ

1. Catalogオブジェクトを開きます。
2. 新規にCommandオブジェクトを作成します。
3. CommandオブジェクトのCommandTextプロパティにSQLステートメントを記述します。
4. ViewsコレクションにCommandオブジェクトを追加します。
5. コレクションにオブジェクトを追加するにはAppendメソッドを利用します。

プロシージャの作成

Sub MyCreateQuery()

On Error GoTo エラー

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

Cat.ActiveConnection = CurrentProject.Connection

mySQL = "SELECT * FROM tbl_provider "
mySQL = mySQL & "WHERE FileName Like 'A*';" '--- A
Cmd.CommandText = mySQL '--- B
Cat.Views.Append "Q_provider", Cmd '--- C

Set cmd = Nothing
Set cat = Nothing

Exit Sub

エラー:

If Err.Number = -2147217816 Then
  MsgBox "既に同名のクエリが存在します。", vbCritical
Else
  MsgBox "エラーID : " & Err.Number & vbNewLine & Err.Description, 16
End If

End Sub

解説

A.SQLを記述します。
B.コマンドテキスト設定します。
C.ViewsコレクションにViewオブジェクトを追加します。

動作確認

1.データベースウィンドウに以下のようにクエリが作成されます。

2.作成されたクエリを実行すると、以下のようにデータが表示されます。

09.ADOXを用いてアクションクエリを作成する ： ADOX入門講座

ADOX機能を用いてアクションクエリやパラメータを持つ選択クエリを作成するには、Proceduresコレクションに新規ProcedureオブジェクトをAppendメソッドを用いて追加する形式をとります。クエリフィールド等の指定は、ADOのコマンドテキスト設定を利用します。

留意事項

ADOとADOXを利用しますので、共に参照設定が必要になります。

$\style{background-color:DodgerBlue;}{アクションクエリ作成の基本構文}$

アクションクエリを作成するADOXの構文は、以下のようになります。

Collection.Procedures.Append "新規クエリ名", Cmd

解説

項目	意味
Collection	開いているコレクションです。
Procedures	開いているProceduresコレクションです。
Append	Appendメソッドです。
新規クエリ名	作成し追加するプロシージャの名前を指定する文字列型の値を指定します。
Cmd	作成し追加するプロシージャを表すADO の Command オブジェクトを指定します。

ADOのコマンドテキスト設定

クエリフィールドの追加設定は、ADOのコマンドテキストを利用します。

cmd.CommandText = SQL構文

$\style{background-color:DodgerBlue;}{ADOXを用いてアクションクエリを作成する}$

このプロシージャを実行すると、「kakuchoshi」フィールドの値の先頭にピリオド（.）を付加するアクションQ_providerクエリが新規作成されます。なお、元になるtbl_providerテーブルは、以下のとおりです。

ID	FileName	kakuchoshi
1	Access	mdb
2	Access_MDE	mde
3	Excel	xls
4	Word	doc

作成までの流れ

1. Catalogオブジェクトを開きます。
2. 新規にCommandオブジェクトを作成します。
3. CommandオブジェクトのCommandTextプロパティにSQLステートメントを記述します。
4. ProceduresコレクションにCommandオブジェクトを追加します。
5. コレクションにオブジェクトを追加するにはAppendメソッドを利用します。

留意点

アクションクエリを作成するだけであって実行するのではありません。

Sub MyCreateQuery()

On Error GoTo エラー

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

Cat.ActiveConnection = CurrentProject.Connection
mySQL = "UPDATE tbl_provider "
mySQL = mySQL & "SET kakuchoshi = '.' & kakuchoshi;" ' ---A

Cmd.CommandText = mySQL ' --- B
Cat.Procedures.Append "Q_provider ", Cmd ' --- C

Set Cat = Nothing

Exit Sub

エラー:

If Err.Number = -2147217816 Then
MsgBox "既に同名のクエリが存在します。", vbCritical
Else
MsgBox "エラーID : " & Err.Number & vbNewLine & Err.Description, 16
End If

End Sub

　

解説

アクションクエリのSQLを記述します。
コマンドテキスト設定します。
ProceduresコレクションにProcedureオブジェクトを追加します。

解説

1.データベースウィンドウに以下のようにクエリが作成されます。

2.作成されたクエリを実行するとtbl_providerテーブルのデータは、以下のようになっています。

関連MS公式

Microsoft Active X データ オブジェクト (ADO) リファレンス

目的

Microsoft ActiveX Data Objects (ADO) により、クライアント アプリケーションが、OLE DB プロバイダーを通じてデータベース サーバーのデータにアクセスし、これを操作できるようになります。 ADO の主な利点は、使用が簡単で、高速に動作し、メモリのオーバーヘッドが小さく、ディスクの使用量が少ないことです。 ADO には、クライアント/サーバーおよび web ベースのアプリケーションを構築するための主要な機能がサポートされています。
Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX) は、ADO のオブジェクトとプログラミング モデルを拡張したものです。ADOX には、セキュリティのためのオブジェクトに加え、スキーマの作成および修正のためのオブジェクトが用意されています。ADOX では、オブジェクト ベースの手法を使用してスキーマを操作するため、使用される構文の違いにかかわらず、さまざまなデータ ソースで動作するコードを記述できます。

ADOX

ADOX は、ADO のコア オブジェクトに添付されるライブラリで、テーブルやプロシージャなどのスキーマ オブジェクトを作成、修正、および削除するための追加のオブジェクトを公開します。また、ユーザーとグループを管理したり、オブジェクトに対する権限を付与および削除したりするためのセキュリティ オブジェクトも備えています。

ADOX API リファレンス

コレクション

コレクション	説明
Columns	テーブル、インデックス、またはキーのすべての Column オブジェクトを含みます。
Groups	カタログまたはユーザーのすべての格納された Group オブジェクトを含みます。
Indexes	テーブルのすべての Index オブジェクトを含みます。
Keys	テーブルのすべての Key オブジェクトを含みます。
Procedures	カタログのすべての Procedure オブジェクトを含みます。
Tables	カタログのすべての Table オブジェクトを含みます。
Users	カタログまたはグループのすべての格納された User オブジェクトを含みます。
Views	カタログのすべての View オブジェクトを含みます。

オブジェクト

オブジェクト	説明
Catalog	データソースのスキーマカタログを表すコレクションを含みます。
Column	テーブル、インデックス、またはキーの列を表します。
Group	保護されているデータベースへの権限を持つグループアカウントを表します。
Index	データベーステーブルのインデックスを表します。
Key	データベーステーブルの主キーフィールド、外部キーフィールド、または一意なキーフィールドを表します。
Procedureプロシージャ	ストアドプロシージャを表します。
Table	列、インデックス、およびキーを含むデータベーステーブルを表します。
User	保護されているデータベースへの権限を持つユーザーアカウントを表します。
View	抽出されたレコードのセットまたは仮想テーブルを表します。

セクション IV: データ定義言語およびセキュリティの ADO 拡張機能 (ADOX)
ADOX コードの例
Access VBA リファレンス
Microsoft Data Access Objects リファレンス
Microsoft Access SQL リファレンス
Access マクロのアクション (Access の開発者用リファレンス)

2016 の Office で VBA の新機能
Access

名前	説明
CodeProject.IsSQLBackend プロパティ (アクセス)	現在のCurrentのCode ProjectがAccess2013以降のAccessで作成されている場合Trueそれ以外はFalseを返す
CurrentProject.IsSQLBackend のプロパティ (アクセス)	Access 2013 の前に現在のプロジェクトが作成された場合は true現在のプロジェクトは Access 2013 以降で作成された場合とfalseを返します。 値の取得のみ可能なブール型 (Boolean) の値です。Current ProjectがAccess2013以降で作成されている場合True、Access2010以前のバージョンのAccessで作成されている場合はFalse。読み取り専用。