8
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

updated at

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

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 オブジェクトが主力です。
image.png
image.png
image.pngimage.pngimage.png

  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画面に切り替えます。
メニューバーの ツール から 参照設定 をクリックします。
下図のような参照設定ダイアログが表示されます。
image.png

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

2005/07/29

編注

2021/05/15現在では、
image.png
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を指定しているので標準テーブル名のみ取得することができます。
image.png

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.チルダ(~)から始まるクエリ名を除外しています。
image.png

チルダ(~)を含むクエリを含めた場合。

image.png

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 :star:
76 Client Cursor 11 True 1537 :star:
77 IRowsetRefresh 11 True 513 :star:
78 Jet OLEDB:Validate Rules On Set 11 False 1537 :star:
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のように現在のデータベースに接続している場合と、外部のデータベースに接続している場合には:star:の4つは出現しない。
これらのプロパティは一部はクエリのデザインビューのプロパティシートにある。フィールドのプロパティシートではないことに注意してほしい。

image.png

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

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コレクションに追加します。
image.png

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.データベースウィンドウに以下のようにクエリが作成されます。

image.png

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

image.png

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.データベースウィンドウに以下のようにクエリが作成されます。

image.png

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

image.png

関連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。読み取り専用。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Sign upLogin
8
Help us understand the problem. What are the problem?