#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を用いると、以下の操作が思いのまま実現できます。
- 新規データベースの作成
- 新規テーブルの作成
- 新規クエリの作成
- 既存テーブルの定義変更
- 既存テーブルへのフィールドの追加、削除
- 既存クエリへのフィールドの追加、削除
- テーブル一覧の参照
- クエリ一覧の参照
###留意点
テーブルやクエリのレコード操作は、ADOを用いて行います。
ADOXオブジェクト モデル
下図は、ADOXオブジェクトモデルの階層構造です。各コレクションとオブジェクトが組み合わされています。この中では、Catalog オブジェクト、Table オブジェクト、Column オブジェクトが主力です。
- Catalog オブジェクト
最上層にあるCatalog オブジェクトは、データベースへの接続を表わします。ADOXを利用する場合、必ず作成する必要があります。 - Table オブジェクト
データベースに存在するテーブル名、クエリ名の一覧を参照する際に利用します。 - Column オブジェクト
テーブルフィールドの作成、フィールド一覧の参照、インデックスの作成を行なう際に利用します。 - Index オブジェクト
データベース テーブルのインデックスの作成、主キーの作成を行います。 - Key オブジェクト
データベース テーブルの主、外部、または一意のキー フィールドを表します。 - Group オブジェクト
保護されているデータベースへのアクセス権を持つグループ アカウントを表します。 - User オブジェクト
保護されているデータベースへのアクセス権を持つユーザー アカウントを表します。 - 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オブジェクトを利用する場合、定められた手順に従ってプロシージャ内で記述していきます。大別すると以下のようになります。
- オブジェクト変数を宣言する。
- 宣言済みのオブジェクト変数にオブジェクトの参照を割り当てる。
- データベースに接続する。
- オブジェクトを削除する。
この段階でVBAからADOXオブジェクトに接続が完了し、利用できるようになります
1.オブジェクト変数の宣言
プロシージャ内で、以下のステートメントを用いてオブジェクト変数を宣言します。
- Dimステートメント
- Staticステートメント
- Privateステートメント
- 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に設定する内容は、作成予定のデータベースの種類と格納場所（パス）になります。
- Providerキーワードは、データベースの種類を記述します。
- 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入門講座
http://accessclub.jp/ado/adox/04.html
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データベースの空文字列を許可のプロパティを取得してみる](https://www.tetsuyanbo.net/tetsuyanblog/49515)
>Microsoft Access データベース (.mdb または .accdb) で次の関数を使用すると、オートナンバー型フィールドのシード値をプログラムからリセットできます。この関数をモジュールに追加して、デバッグ ウィンドウで実行することも、コマンド ボタンやマクロから呼び出すこともできます。
[Access データベースの最適化後オートナンバー型のフィールドがリセットされない](https://support.microsoft.com/ja-jp/topic/access-%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%81%AE%E6%9C%80%E9%81%A9%E5%8C%96%E5%BE%8C%E3%82%AA%E3%83%BC%E3%83%88%E3%83%8A%E3%83%B3%E3%83%90%E3%83%BC%E5%9E%8B%E3%81%AE%E3%83%95%E3%82%A3%E3%83%BC%E3%83%AB%E3%83%89%E3%81%8C%E3%83%AA%E3%82%BB%E3%83%83%E3%83%88%E3%81%95%E3%82%8C%E3%81%AA%E3%81%84-1162440b-c43a-dab7-d3ef-63a7b4e1bb7f)
>注 : このコードを正しく実行するには、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] の各チェック ボックスがオンになっていることを確認します。
>```vb
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のプロパティ
| 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入門講座
http://accessclub.jp/ado/adox/07.html
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 |
###作成までの流れ
- Catalogオブジェクトを開きます。
- 新規にCommandオブジェクトを作成します。
- CommandオブジェクトのCommandTextプロパティにSQLステートメントを記述します。
- ViewsコレクションにCommandオブジェクトを追加します。
- コレクションにオブジェクトを追加するには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 |
###作成までの流れ
- Catalogオブジェクトを開きます。
- 新規にCommandオブジェクトを作成します。
- CommandオブジェクトのCommandTextプロパティにSQLステートメントを記述します。
- ProceduresコレクションにCommandオブジェクトを追加します。
- コレクションにオブジェクトを追加するには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 のコア オブジェクトに添付されるライブラリで、テーブルやプロシージャなどのスキーマ オブジェクトを作成、修正、および削除するための追加のオブジェクトを公開します。また、ユーザーとグループを管理したり、オブジェクトに対する権限を付与および削除したりするためのセキュリティ オブジェクトも備えています。
####コレクション
| コレクション | 説明 |
|---|---|
| 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。読み取り専用。 |