2022/05/15

ExcelからAccessデータベースを作成・操作

• １．背景
• ２．ADOXについて
• 　２－１．ADOXの定数宣言
• ３．Accessデータベースの作成と削除
• 　３－１．データベースの作成
• 　　よりみち（プロバイダとAccessの拡張子）
• 　３－２．データベースの削除
• 　　よりみち（Accessロックファイルの残留）
• ４．テーブルの作成と削除
• 　４－１．データ型について
• 　４－２．テーブル作成の手順
• 　４－３．作成される列の詳細プロパティ
• 　４－４．テーブルの作成
• 　　４－４－１．ADOXによる方法
• 　　４－４－２．SQLによる方法
• 　　　よりみち（SQL文の実行方法）
• 　４－５．テーブルの削除
• 　　４－５－１．ADOXによる方法
• 　　４－５－２．SQLによる方法
• 　４－６．テーブルの修正
• 　　４－６－１．ADOXによる方法
• 　　４－６－２．SQLによる方法
• ５．データベース制約の設定と解除
• 　５－１．ADOXによる制約設設定方法
• 　５－２．SQLによる制約設定方法
• 　　５－２－１．Alter Tableで後付け設定する方法
• 　　５－２－２．Create Table時に併行して設定する方法
• 　５－３．ADOXよる制約解除方法
• 　５－４．SQLよる制約解除方法
• 　　５－４－１．Key名が既知の場合の制約解除
• 　　５－４－２．Key名が未知の場合の制約解除
• ６．インデックスの作成と削除
• 　６－１．インデックスの作成
• 　　６－１－１．ADOXによるインデックス作成
• 　　６－１－２．SQLによるインデックス作成
• 　６－２．インデックスの削除
• 　　６－２－１．ADOXによるインデックス削除
• 　　６－２－２．SQLによるインデックス削除
• 　６－３．制約（Key）とインデックスの関係
• ７．テーブル構造の確認
• ８．データの操作
• 　８－１．データの挿入・更新・削除
• 　８－２．データ取得
• ９．最後に
• サンプルファイル

１．背景

今まで「Excelのシート上にデータベース（DB）を作成し、別なExcelブックからデータを操作する」ものとして下記を紹介してきました。
「Excelシート上にDBを作り、SQLを使ってデータを入出力する」
「ExcelシートDBとSQLを使った会議室予約システム」
「ExcelシートDBとSQLを使った倉庫管理システム」
「共有資料の登録と閲覧ができるサーバーシステム」
「複数の備品を同時予約可能な貸出台帳」

今回は「（Accessが入っていないPC上の）Excel」から「Accessデータベース」ファイルを作成し操作をする方法について紹介します。データがExcelシート上にあってもAccessデータベース上にあっても、複数の人とデータ共有できることに変わりは無いのですが、Accessという「データの保管・管理・入出力に特化したシステム」にある方が、以下のようなメリットは得られると思います（私見です）。

DBデータの置き場所での比較

ﾃﾞｰﾀ場所	メリット	デメリット
Excel シート	・ﾃｰﾌﾞﾙが簡単に作れる ・保存ﾃﾞｰﾀが誰でも確認可	・Delete使えず､削除には工夫要 ・ﾃﾞｰﾀ量増加で(たぶん)処理遅い ・ﾃﾞｰﾀ型制約等はVBA側で作り込み要
Access	・ﾃﾞｰﾀの完全性が容易 ・大量ﾃﾞｰﾀでも(たぶん)処理速い ・自動で型制約してくれる ・Deleteが使用可	・ﾃｰﾌﾞﾙを作る時もSQL必要 ・ﾃｰﾌﾞﾙが直接確認できない ・SQLを知らないとﾃﾞｰﾀ閲覧･操作不可

図１－１

但し、どちらにしても「データベース」としてデータを操作するには「SQL」は避けては通れないので、VBAとは違った技術習得が必要です。
もし「SQLを使わずに」と言うのであれば、Excelなら可能です。データの読出し・書き出しごとに「データが保存されているExcelブックを開く」という手法を使うことになりますが、誰かがデータブックを開いている間は他の人は作業できません（読取専用で開く事は可）し、検索も自分で行う必要があるので、処理時間大というデメリットが生じます。

なお、今回はAccessデータベースの作成と操作に特化した説明ですが、実際のアプリ形式に落とし込んだものとしては以下のものが有ります。併せて参照下さい。
　「Accessデータベースを使用した売上台帳」
　「設備の稼働状態を入力し、グラフで確認」

２．ADOXについて

Accessのデータベースファイル（拡張子：accdb または mdb ）を作るには、通常AccessがインストールされているPCから行いますが、インストールされていなくてもExcelがあればADOXというものを使って作成することが出来ます。また、実際にデータを収めるテーブルもADOXを使って作成・削除することも可能です。

ADOXオブジェクトは「ActiveX Data Objects Extensions for Data Definition Language and Security」の略であり、和訳すると「データ定義とセキュリティのために拡張したADO」となります。「データ定義」は上記のデータベースファイルやテーブルを作成できることを指し、「セキュリティ」はデータベースへのアクセス許可を設定できることを指しているようです。

ADOXオブジェクトは、図２－１のような９つのオブジェクトに分類できますが、Catalogオブジェクトが最上層にあり、ADOXを利用する場合は必ずCatalogオブジェクトを生成する必要があるようです。

オブジェクト	内容
Catalog	データソースのスキーマ カタログを記述するコレクションを格納
Table	列、インデックス、キーなどのデータベース テーブルを表す
Index	データベース テーブルのインデックスを表す
Key	データベース テーブルのプライマリ、外部、または一意のキー フィールドを表す
Column	テーブル、インデックス、キーの列を表す
Group	セキュリティで保護されたデータベース内のアクセス許可を持つグループアカウントを表す
User	セキュリティで保護されたデータベースへのアクセス許可を持つユーザーアカウントを表す
Procedure	ストアドプロシージャ（複数処理をまとめて実行）を表す
View	フィルター処理されたレコードセットまたは仮想テーブルを表す

図２－１

上記のオブジェクトの関係をMicrosoftのサイトで「ADOXオブジェクトモデル」として、図２－２のように紹介しています。青色がオブジェクト、白色がコレクションを示しています。

図２－２

Catalogオブジェクトが最上位にあり、その下に他のオブジェクトがあるのは理解できるのですが、一番下の「Command」はオブジェクトではありません。どうもCommandは、ProcedureやViewに設定するためのCommandプロパティ、またはADOのCommandオブジェクトを指しているようです。

ADOXを利用するには、事前バインディングする方法と実行時バインディングで生成する方法の２通りがあります。
サンプルファイルでは実行時バインディングを使用していますが、事前バインディングでADOXを利用するのであれば、図２－３のようにVBEの「ツール」→「参照設定」から「Microsoft ADO Ext.〇〇 for DDL and ecurity」を有効にして下さい。なお〇〇はバージョンなので環境により異なります。
また事前バインディングの時は、オブジェクトを生成する時のコードが多少変わりますので注意下さい。

図２－３

２－１．ADOXの定数宣言

ADOXを事前バインディングしている場合には当たり前のように使える定数も、今回は実行時バインディングのコードとしているため「定数宣言」しておく必要があります。
今回は、下記４種類を標準モジュール先頭の宣言部で宣言します（図２－４）。これ以外の定数を使う場合は追加が必要ですし、不要な定数は省略してもOKです。
　０２～１５行目：図４－１で説明するADOのデータ型
　１７～１８行目：図４－４で説明するAttributeプロパティの値
　２０～２２行目：図５－５で説明する制約の値
　２４～２７行目：図５－１０、図６－３で説明するIndexNullsプロパティの値

1. '==========　⇩(1)　ADOXの定数宣言　============
2. Const adVarWChar As Long = 202
3. Const adLongVarWChar As Long = 203
4. Const adUnsignedTinyInt As Long = 17
5. Const adSmallInt As Long = 2
6. Const adInteger As Long = 3
7. Const adSingle As Long = 4
8. Const adDouble As Long = 5
9. Const adGUID As Long = 72
10. Const adNumeric As Long = 131
11. Const adDate As Long = 7
12. Const adCurrency As Long = 6
13. Const adBoolean As Long = 11
14. Const adLongVarBinary As Long = 205
15. Const adVarBinary As Long = 204
17. Const adColFixed As Integer = 1
18. Const adColNullable As Integer = 2
20. Const adKeyPrimary As Long = 1
21. Const adKeyForeign As Long = 2
22. Const adKeyUnique As Long = 3
24. Const adIndexNullsAllow As Long = 0
25. Const adIndexNullsDisallow As Long = 1
26. Const adIndexNullsIgnore As Long = 2
27. Const adIndexNullsIgnoreAny As Long = 4

図２－４

３．Accessデータベースの作成と削除

３－１．データベースの作成

Accessのデータベースファイルを作成するコードが図３－１です。

31. '==========　⇩(2)　データベースファイルの作成　============
32. Sub makeDB()
33. 　Const DBname As String = "it-081.accdb"
34. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
35. 　Dim Cat As Object
37. 　Set Cat = CreateObject("ADOX.Catalog")
39. 　Cat.Create "Provider=Microsoft.Ace.OLEDB.12.0;" & _
40. 　　　　　　　"Data Source=" & DBpath & DBname
42. 　Set Cat = Nothing
43. End Sub

図３－１

３３行目「Const DBname As String = "it-081.accdb"」では、作成するデータベースファイルを「it-081.accdb」としています。ファイル名は何でもOKですが、拡張子は下記のように決められています。今回は最新の拡張子（.accdb）を使いましたが、.mbdでも動作します。

Access	拡張子
～97	.mdb(Access97形式？)
2000	.mdb(Access2000形式)
2002、2003	.mdb(Access2002-2003形式)
2007～	.accdb

図３－２

３４行目「Dim DBpath As String」「DBpath = ThisWorkbook.Path & "¥"」は、２つのコードを「:（コロン）」でつなぎ、１行で表しています。内容は、前半でファイルを置く場所の変数DBpathを宣言し、後半で値を指定しています。今回は、このサンプルファイルと同じ場所にしています。

３５行目「Dim Cat As Object」はADOXのCatalogオブジェクトを宣言しています。今回３７行目でCreatObjectを使い、実行時バインディングでADOXオブジェクトを生成していますので、型はObjectとなっています。
３７行目「Set Cat = CreateObject("ADOX.Catalog")」では、ADOXのCatalogオブジェクトを生成しています。
３９～４０行目「Cat.Create "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & DBpath & DBname 」ではADOXのCreateメソッドで「新規にデータベースファイルを作成」しています。作成する時の構文は以下の通りです。
　Catalogオブジェクト.Create　"Provider=プロバイダ―; Data Source=データベースファイル名"

３９行目で指定している「Microsoft.Ace.OLEDB.12.0」は、Access2007～2019に対応したProviderです。図３－３のようにバージョンにより対応するProviderが変わりますが、Accessがインストールされていない場合は、まずはAccessのバージョンをOfficeのバージョンに置き換えたProvider（例えばExcel2016であれば、Access2016として12.0または16.0）を使うのが良いと思います。なお、12.0は「何でもOK」みたいなので、今回は12.0を使用しています。

Provider	対応するAccess
	2000～2003	2007	2010	2013	2016	2019
Microsoft.Jet.OLEDB.4.0	〇
Microsoft.Ace.OLEDB.12.0	(〇)	〇	〇	〇	〇	〇
Microsoft.Ace.OLEDB.15.0	？			〇
Microsoft.Ace.OLEDB.16.0	？				〇	〇

図３－３

図３－２と図３－３を見比べてみると、「Microsoft.Jet.OLEDB.4.0」に対応している拡張子は「.mdb」のみのはずなのですが、「Microsoft.Jet.OLEDB.4.0 で、拡張子accdbのファイルを作成」するコードを実行してもファイルは一応作成されます。しかし内容はmbdのようで、DB使用時のみに作成される「ロック状態を示すファイル」の拡張子が「.ldb（mdbファイルのロックファイル）」となります。 なおデータ授受（Insert文やSelect文）の時には、 　・4.0で作成した.accdbファイルには、4.0で操作可 　・12.0で作成した.accdbファイルには、4.0では操作不可 となり「拡張子を見ているのでは無く、ファイルの構造を見ている」という事のようです。

４０行目の「Data Source=･･･」には、データベースファイルのパスとファイル名を指定します。
なお、既に同じファイル名が存在する場合には、図３－４のように実行時エラーが発生します。図３－５の５９～６１行目のように「ファイルが存在しているか確認後、存在しているファイルを削除」してから作成する手順が必要です。

図３－４

４２行目「Set Cat = Nothing」は、３７行目で生成したオブジェクトを解放しています。

３－２．データベースの削除

データベースファイルを削除するコードが図３－５です。

50. '==========　⇩(3)　データベースの削除　============
51. Sub deleteDB()
52. 　Const DBname As String = "it-081.accdb"
53. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
54. 　Dim Ans As VbMsgBoxResult
55. 　Dim Fso As Object
57. 　Set Fso = CreateObject("Scripting.FileSystemObject")
59. 　If Not Dir(DBpath & DBname) = "" Then
60. 　　Ans = MsgBox("DBファイルが存在します。削除して良いですか？", vbYesNo)
61. 　　If Ans = vbNo Then Exit Sub
63. 　　On Error Resume Next
64. 　　　Fso.GetFile(DBpath & DBname).Delete
65. 　　　If Not Err.Number = 0 Then
66. 　　　　MsgBox "削除できません。ファイル使用中の可能性があります。"
67. 　　　End If
68. 　　On Error GoTo 0
70. 　Else
71. 　　MsgBox "ファイルが存在しません。"
72. 　End If
74. 　Set Fso = Nothing
75. End Sub

図３－５

５２～５３行目は図３－１の時と同様に、データベースファイルの「ファイル名」と「パス名」です。
５４行目「Dim Ans As VbMsgBoxResult」は、ユーザーの希望（＝ファイルを削除するか否か）を取得するMsgBox関数（６０ 行目）の戻り値を入れる変数です。データ型を「VbMsgBoxResult」としていますが、内部的にはLong型ですし、戻り値は1～7の整数ですのでInteger型で宣言しても問題はありません。ただし「VbMsgBoxResult型で宣言」しておくことで、コードを書いている途中で「候補値を自動メンバー表示」してくれる機能が働きますので、コード記述時のミスが減ります。

５５行目「Dim Fso As Object」は、FileSystemObjectオブジェクトの変数宣言をしています。今回は実行時バインディングを行っていますが、もし事前バインディングの場合は、VBEの参照設定で「Microsoft Scripting Runtime」を有効にして下さい。
５７行目「Set Fso = CreateObject("Scripting.FileSystemObject")」では、FileSystemObjectオブジェクトを生成しています。今回は「ファイル削除」のためにFileSystemObjectを使用します。

５９行目「If Not Dir(DBpath & DBname) = "" Then」ではDir関数を使って「作ろうとしているファイルが存在するか」を調べています。Dir関数は、引数に指定したファイルが存在するときには「そのファイル名」を戻してきますので、空で無い時（＝ファイルが存在する時）に６０～６８行目を実行することになります。
ファイルが存在する時には、まず６０行目「Ans = MsgBox("DBファイルが存在します。削除して良いですか？", vbYesNo)」で「削除して良いか」をユーザーに確認します。MsgBoxには「はい・いいえ（vbYesNo）」のボタンがついており、どちらかの値「vbYes または vbNo」が戻ってきます。
６１行目「If Ans = vbNo Then Exit Sub」で、戻って来た値がvbNo（＝「いいえ」をクリック）の場合は、削除処理を中止（Exit Sub を実行）します。

６４行目「Fso.GetFile(DBpath & DBname).Delete」は、ユーザーが「はい」をクリックした場合に実行されます。
前半の「Fso.GetFile(DBpath & DBname)」では、FileSystemObject（Fso）のGetFileメソッドを使い、引数に指定したファイルを「Fileオブジェクト」にしています。そしてその「Fileオブジェクト」をDeleteメソッドで削除します。
異なる方法として、DeleteFileメソッドを使い「Fso.DeleteFile DBpath & DBname」でも削除できます。

但し、ファイルを削除できない場合があります。今回について言えば「ファイルが使用中」の場合です。その場合は図３－６の左側のようなエラーが発生しますので、６３行目「On Error Resume Next」でエラーをスルーさせ、６５行目「If Not Err.Number = 0 Then」でエラーが出ている（≒ファイル使用中）時は、６６行目「MsgBox "削除できません。ファイル使用中の可能性があります。"」でコメント（図３－６の右側）を出します。

図３－６

なおファイルが使用中の場合、同じファイル名で拡張子が「.laccdb（ロック状態）」のファイルが同じフォルダー内に作成されます（使用中が解除されると、ファイルが削除される）。 しかし何らかの条件でロックファイルが消えない状態が発生するようです。ですので、データベース未使用の場合でも「ファイル使用中」が出ることがあります。 今回のExcelを作っている最中にも何回も発生したのですが、発生条件は結局把握できませんでした。対策はネット上に以下のようなものが紹介されています。（4つ目は私が考えたもの） 　・Excelの「オプション」→「トラストセンター」→「信頼できる場所」に、データベースファイルのパスを登録。 　・更新プログラム（2016はKB5002099、2013はKB5002104）をインストールする。 　・再起動後に.laccdbファイルを削除する。 　・Excel終了後、タスクマネージャーで生きているExcelプロセスを終了させてから、ファイルを削除。 ちなみに２つ目の更新プログラムも試しましたが、（Accessが無いためか）インストールすら出来ませんでした。また１番目も（私の場合は）効果が見られませんでした。 運用中にデータベースファイルを削除する場面は無いとは思いますが、注意が必要です。

削除するファイルが無い（５９行目のDir関数の戻り値が「""（長さゼロの文字列）」）場合は、７１行目「MsgBox "ファイルが存在しません。"」でメッセージを出します。
最後に７４行目「Set Fso = Nothing」で、FileSystemObjectオブジェクトを解放します。

４．テーブルの作成と削除

４－１．データ型について

テーブルを作成する時には、その中の列（カラム）の名前と同時にデータ型を指定する必要があります。Accessとして設定できるデータ型は図４－１の一番左の列のようになっているようです。
ADOXを利用してテーブルを作るときには「ADOのデータ型」を使用します。一方、SQL文を使ってテーブルを作ることもでき、その時にはSQLで使う型（「AccessDBのSQLで設定する時の型」の列）を使用します。

図４－１

図４－１の表は、Microsoftのサイト「データ型の比較」を元にして作成しましたが、SQLを使って設定をしてみると、異なった型になってしまうデータ型が複数あることが分かりました（図４－１の二重取り消し線で見え消しにしてあるデータ型）。なお、この結果はExcel VBA から実行した場合であり、他の手段（Access内のコマンド等）を使った場合にはまた別の結果が出る可能性があると思います。
また、adBinary型（128）で列設定をしても、その結果はadVarBinary型（204）に変換されてしまう事も分かりました。

なお、VBAのVariant型に相当する「adVariant型」は存在するようですが、ADOXで列設定しようとするとエラーが発生し、またSQLで「VALUE型（VBAのVariant型に相当）」で設定しようとしてもエラーが発生します。
しかし、もし設定できる方法が見つかったとしても、ExcelにとってVariant型は「セル範囲を一気に配列化する」など特有の使い方には無くてはならない型である一方「何でもありの型」ですので、同一種類のデータを制御する目的のデータベースの型には適さないと思います。

図４－１に示した以外で、「adBigInt型（20）：8バイトの符号付き整数」「adWChar型（130）：Nullで終わるUnicode文字列」もテーブル作成時に使用可能でしたが、Accessとしてどの型に結び付いているのかは分かりませんでした。

４－２．テーブル作成の手順

テーブルを作る方法は２種類あります。１つはADOXを使う方法で、図４－２のような手順になります。

• 　Set Cat = CreateObject("ADOX.Catalog") 　 '←Catalogオブジェクト生成
• 　Set TBL = CreateObject("ADOX.Table") 　　 '←Tableオブジェクト生成
• 　Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
• 　　　　　　　　　　　　　 "Data Source= データベースファイル" 　 '←データベースに接続
• 　
• 　TBL.Name = テーブル名 　　　　　　　　 '←テーブル名を設定
• 　Set TBL.ParentCatalog = Cat 　　　　 '←データベースと繋げる
• 　
• 　TBL.Columns.Append "列名", ADOのデータ型 　 '←新たな列を新テーブルに作成
• 　
• 　Cat.Tables.Append TBL 　　　　　　　 '←新テーブルをデータベースに追加

図４－２

ADOXでテーブルを作る際、列のプロパティを変更する場合は「TBL.Columns.Append "列名", ADOのデータ型」の直後で設定する必要があります。一旦テーブルをデータベースファイルに追加「Cat.Tables.Append TBL」してしまうと、プロパティは読取のみになってしまい、変更することが不可能になります（可能なプロパティもあります）。

もう１つはSQLを使う方法で、図４－３のような手順になります。

• 　Set cn = CreateObject("ADODB.Connection") 　 '←Connectionオブジェクト生成
• 　Set rs = CreateObject("ADODB.Recordset") 　 '←Recordsetオブジェクト生成
• 　
• 　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
• 　cn.Open データベースファイル名 　　　　　　　　 '←データベースに接続
• 　
• 　sql = "Create Table テーブル名 ( 列名　SQLデータ型 )" 　 '←新テーブルを作るSQL
• 　
• 　rs.Open sql, cn 　　 '←SQLを実行（cn.Execute sql でもOK）

図４－３

ただし図４－２と図４－３のデータ型部分に、図４－１の表の同じ系列のデータ型を使ったとしても、全く同じ列が出来る訳ではありません。データ型は同じでも、細かいプロパティは異なってきます。その違いを説明しているのが「4-3.作成される列の詳細プロパティ」です。
大雑把に言うと、ADOXでテーブルを作ると「ほぼ全てのデータ型でNot Nullの制約が掛かった状態」になります。通常作られるテーブルの列は「Null を許可」が既定状態だと思っていますので、ADOXで作成する場合は注意が必要です。

４－３．作成される列の詳細プロパティ

まず、Catalogオブジェクトの列のプロパティを図４－４に示します。その中の７番目の「Properties」には更に細かいプロパティがありますので図４－５に示しています。

プロパティ	内容
Attribute	属性を示す。 adColFixed(値=1)：列は固定長 adColNullable(値=2)：列へのNullは可
DefinedSize	列の最大ｻｲｽﾞ
Name	列の名前
NumericScale	小数点以下の桁数
ParentCatalog	親のﾃﾞｰﾀﾍﾞｰｽﾌｨﾙ
Precision	全桁数
Properties	（図４－５を参照）
RelatedColumn	関連する列の名前
SortOrder	並べ替え順序
Type	データ型

図４－４

No	プロパティ名	内容
1	AutoIncrement	列にｵｰﾄﾅﾝﾊﾞｰ型を設定する
2	Default	その列の値を設定する
3	Description	詳細を設定可能
4	Nullable	Null値を許可する
5	Fixed Length	固定長＝True
6	Seed	ｵｰﾄﾅﾝﾊﾞｰ型の場合の初期値(行追加と共に増える)
7	Increment	ｵｰﾄﾅﾝﾊﾞｰ型の場合の増分値
8	Jet OLEDB:Column Validation Text	ﾙｰﾙに合致しない時に出されるｴﾗｰﾒｯｾｰｼﾞ(~255文字)
9	Jet OLEDB:Column Validation Rule	入力規制の式(~2048文字)｢例:<>0｣
10	Jet OLEDB:IISAM Not Last Column	この列の後に追加される列が存在することをIISAMに伝える
11	Jet OLEDB:AutoGenerate	列に対しGUID値を自動的に生成(adGUIDのみ)
12	Jet OLEDB:One BLOB per Page	ｽﾍﾟｰｽ節約の為にDBﾍﾟｰｼﾞを共有する(adLongVarBinaryのみ)
13	Jet OLEDB:Compressed Unicode Strings	ﾃﾞｨｽｸ上のUNICODE文字列を圧縮する
14	Jet OLEDB:Allow Zero Length	長さ0の文字列(≠Null)を挿入可｡(文字列型のみ)
15	Jet OLEDB:Hyperlink	ﾊｲﾊﾟｰﾘﾝｸ型に設定(LongTextのみ)

図４－５

実際に、以下の様な単純なコードでテーブル・列を作成した時のプロパティを比較していきます。
　ADOX：「TBL.Columns.Append "列名", ADOのデータ型 → Cat.Tables.Append TBL」（図４－２）
　SQL ：「Create Table テーブル名 ( 列名　SQLデータ型 )」（図４－３）
データ型ごとに様子が異なりますのでデータごとに説明し、また各表のADOXとSQLで異なる部分は黄色背景にしています。

４－４．テーブルの作成

４－４－１．ADOXによる方法

ADOXを利用してテーブルを作成するのが図４－２１です。ここでは、Access操作上の型を列名にし、それに対応したADOデータ型を設定しています。
また全ての列は、列の既定状態と思われる「Null値挿入可」に設定します。この設定を後付けで設定出来れば良いのですが、データベース制約のところでも説明する通りテーブル作成後では困難になる為に、この「テーブルの作成段階」で設定します。
設定のやり方は「作成される列の詳細プロパティ」の各データ型のところで説明したように、列のプロパティ値をNull値挿入可の状態に変更します。

80. '==========　⇩(4)　ADOXによるテーブルの作成　============
81. Sub ADOmakeTable()
82. 　Const DBname As String = "it-081.accdb"
83. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
84. 　Const Tname As String = "AllDataType"
85. 　Dim Cat As Object 　　 '←Catalogオブジェクト
86. 　Dim TBL As Object 　　 '←Tableオブジェクト
88. 　Set Cat = CreateObject("ADOX.Catalog")
89. 　Set TBL = CreateObject("ADOX.Table")
90. 　Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
91. 　　　　　　　　　　　　　"Data Source=" & DBpath & DBname
93. 　TBL.Name = Tname
94. 　Set TBL.ParentCatalog = Cat
96. 　With TBL
97. 　　.Columns.Append "短いテキスト", adVarWChar
98. 　　　.Columns("短いテキスト").Attributes = adColNullable 　　 '←Nullを許可
99. 　　.Columns.Append "長いテキスト", adLongVarWChar
100. 　　　.Columns("長いテキスト").Attributes = adColNullable 　　 '←Nullを許可
101. 　　.Columns.Append "数値バイト", adUnsignedTinyInt
102. 　　　.Columns("数値バイト").Attributes = adColNullable + adColFixed 　　 '←Nullを許可
103. 　　.Columns.Append "数値整数", adSmallInt
104. 　　　.Columns("数値整数").Attributes = adColNullable + adColFixed 　　 '←Nullを許可
105. 　　.Columns.Append "数値長整数", adInteger
106. 　　　.Columns("数値長整数").Attributes = adColNullable + adColFixed 　　 '←Nullを許可
107. 　　.Columns.Append "数値単精度", adSingle
108. 　　　.Columns("数値単精度").Attributes = adColNullable + adColFixed 　　 '←Nullを許可
109. 　　.Columns.Append "数値倍精度", adDouble
110. 　　　.Columns("数値倍精度").Attributes = adColNullable + adColFixed 　　 '←Nullを許可
111. 　　.Columns.Append "数値ﾚﾌﾟﾘｹｰｼｮﾝ", adGUID
112. 　　　.Columns("数値ﾚﾌﾟﾘｹｰｼｮﾝ").Attributes = adColNullable + adColFixed 　　 '←Nullを許可
113. 　　.Columns.Append "数値十進型", adNumeric
114. 　　　.Columns("数値十進型").Attributes = adColNullable + adColFixed 　　 '←Nullを許可
115. 　　　.Columns("数値十進型").Precision = 4
116. 　　　.Columns("数値十進型").NumericScale = 2
117. 　　.Columns.Append "日付時刻", adDate
118. 　　　.Columns("日付時刻").Attributes = adColNullable + adColFixed 　　 '←Nullを許可
119. 　　.Columns.Append "通貨", adCurrency
120. 　　　.Columns("通貨").Attributes = adColNullable + adColFixed 　　 '←Nullを許可
121. 　　.Columns.Append "ｵｰﾄﾅﾝﾊﾞｰ", adInteger
122. 　　　.Columns("ｵｰﾄﾅﾝﾊﾞｰ").Properties("AutoIncrement") = True
123. 　　.Columns.Append "Yes_No", adBoolean
124. 　　.Columns.Append "OLE_object", adLongVarBinary
125. 　　　.Columns("OLE_object").Attributes = adColNullable 　　 '←Nullを許可
126. 　　.Columns.Append "ﾊｲﾊﾟｰﾘﾝｸ", adLongVarWChar
127. 　　　.Columns("ﾊｲﾊﾟｰﾘﾝｸ").Properties("Jet OLEDB:Hyperlink") = True
128. 　　.Columns.Append "添付ファイル", adVarBinary
129. 　　　.Columns("添付ファイル").Attributes = adColNullable 　　 '←Nullを許可
130. 　End With
132. 　On Error Resume Next
133. 　　Cat.Tables.Append TBL
134. 　　If Not Err.Number = 0 Then
135. 　　　MsgBox "作成できません。設定が無効か、または既に存在している可能性があります。"
136. 　　End If
137. 　On Error GoTo 0
139. 　Set Cat = Nothing
140. 　Set TBL = Nothing
141. End Sub

図４－２１

８４行目「Const Tname As String = "AllDataType"」は、作成するテーブルの名前を定数設定しています。
８８行目「Set Cat = CreateObject("ADOX.Catalog")」でCatalogオブジェクトを生成し、８９行目「Set TBL = CreateObject("ADOX.Table")」でTableオブジェクトを生成します。

９０～９１行目「Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & DBpath & DBname」でデータベースファイルに接続します。この「ActiveConnectionプロパティ」は、アクティブな接続オブジェクトを返すもので、その引数には「接続の定義を含む接続オブジェクト 又は 文字列」を設定しますので、今回は「接続定義の文字列」を指定しています。
なおMicrosoftのサイトで紹介されている手法は「接続の定義を含む接続オブジェクト」を指定するもので、図４－２２のようにまずADODB.ConnectionのOpenメソッドでデータベースファイルに接続し、その接続状態をCat.ActiveConnectionに設定するようです。但しこの場合は「cn.Close」で接続を終了させる必要があります。

• 　Set Cat = CreateObject("ADOX.Catalog")
• 　Set cn = CreateObject("ADODB.Connection")
• 　
• 　cn.Open "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & DBpath & DBname
• 　Set Cat.ActiveConnection = cn

図４－２２

９３行目「TBL.Name = Tname」では、TableオブジェクトのNameプロパティに「テーブル名」を設定します。
９４行目「Set TBL.ParentCatalog = Cat」では、Tableオブジェクトの親Catalog（＝テーブルを所有するCatalog）として、データベースファイルに接続しているCatalog（Cat）を設定します。
なお、このParentCatalogの設定は、１３３行目「Cat.Tables.Append TBL」でテーブルを登録する時に自動的に設定されます。しかしNull設定以外でも、例えば１２２行目「.Columns("ｵｰﾄﾅﾝﾊﾞｰ").Properties("AutoIncrement") = True」のような列のプロパティ設定が必要な場合には「事前にParentCatalog設定をしておかないとエラー」となりますので、列設定の前（９４行目）のタイミングでParentCatalog設定をしておいた方が無難です。

９６～１３０行目で合計１６列を作っていきます。今回は図４－１の表の「Access操作上の型名称」を列名にし、それに対応したデータ型としました。
基本的には「 Table.Columns.Append 列名 , ADOデータ型 」という形で各列を設定していきます。
但し９７行目の「adVarWChar（短いテキスト型）」は、データ型の後ろにカンマに続けて「文字数」を設定することが可能です。９７行目の様に未設定の場合には最大の255文字が設定されますが、255超の値を設定するとエラーが発生します。
また９９行目「adLongVarWChar（長いテキスト型）」も文字数設定がありそうですが、短いテキスト型と同様の方法で設定してもプロパティ（Defined Size）値はゼロのまま変化ありません（且つ、制限である65,536文字を超えた値を設定してもエラーも出ません）。

一方１１３～１１６行目の「十進型」は、Precisionプロパティに「全体の桁数」を指定し、NumericScaleプロパティに「小数点以下の桁数」を指定する必要があります。
また１２１～１２２行目のオートナンバー型では、プロパティの「AutoIncrement」をTrueにしておく必要があります。なおオートナンバー型には「adGUID型」もあるようですが、adGUIDを設定するとエラーが発生してしまいます。

新たに作るテーブル（ここではTBLオブジェクト）の列設定が完了したら、１３３行目「Cat.Tables.Append TBL」でデータベースファイルにテーブルを追加します。しかし「追加しようとしたテーブル名は既に存在」していたり「テーブルの設定が間違っている」場合もありますので、１３２行目「On Error Resume Next」でエラーをスルーさせ、１３４行目「If Not Err.Number = 0 Then」でエラー有無を確認し、エラー（＝テーブルが作られない）の場合は１３５行目「MsgBox "作成できません。設定が無効か、または既に存在している可能性があります。"」でコメントを表示します。

４－４－２．SQLによる方法

ADOXを使わず、データベースに対してSQLを発行してテーブルを作成する方法が、図４－２３です。

150. '==========　⇩(5)　SQLによるテーブルの作成　============
151. Sub SQLmakeTable()
152. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
153. 　Const DBname As String = "it-081.accdb"
154. 　Const Tname As String = "AllDataType"
155. 　Dim sql As String 　　 '←SQL文
156. 　Dim cn As Object 　　 '←Connectionオブジェクト
157. 　Dim rs As Object 　　 '←Recordsetオブジェクト
159. 　Set cn = CreateObject("ADODB.Connection")
160. 　Set rs = CreateObject("ADODB.Recordset")
162. 　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
163. 　cn.Open DBpath & DBname
165. 　sql = "Create Table " & Tname & " (" & _
166. 　　　　　"短いテキスト ALPHANUMERIC," & _
167. 　　　　　"長いテキスト LONGTEXT," & _
168. 　　　　　"数値バイト BYTE," & _
169. 　　　　　"数値整数 SHORT," & _
170. 　　　　　"数値長整数 LONG," & _
171. 　　　　　"数値単精度 SINGLE," & _
172. 　　　　　"数値倍精度 DOUBLE," & _
173. 　　　　　"数値ﾚﾌﾟﾘｹｰｼｮﾝ GUID," & _
174. 　　　　　"数値十進型 NUMERIC(4,2)," & _
175. 　　　　　"日付時刻 DATETIME," & _
176. 　　　　　"通貨 CURRENCY," & _
177. 　　　　　"ｵｰﾄﾅﾝﾊﾞｰ COUNTER," & _
178. 　　　　　"Yes_No BIT," & _
179. 　　　　　"OLE_object LONGBINARY," & _
180. 　　　　　"ﾊｲﾊﾟｰﾘﾝｸ LONGTEXT," & _
181. 　　　　　"添付ファイル BINARY" & _
182. 　　　　　")"
184. 　On Error Resume Next
185. 　　rs.Open sql, cn 　　 '← cn.Execute sql でもOKです
186. 　　If Not Err.Number = 0 Then
187. 　　　MsgBox "設定が無効か、作成するテーブルが既に存在する可能性があります。"
188. 　　End If
189. 　On Error GoTo 0
191. 　cn.Close
193. 　Set rs = Nothing
194. 　Set cn = Nothing
195. End Sub

図４－２３

１５９行目「Set cn = CreateObject("ADODB.Connection")」でConnectionオブジェクトを生成し、１６０行目「Set rs = CreateObject("ADODB.Recordset")」でRecordsetオブジェクトを生成します。
１６２行目「cn.Provider = "Microsoft.ACE.OLEDB.12.0"」でプロバイダを設定し、１６３行目「cn.Open DBpath & DBname」でデータベースファイルを開きます。
１６２～１６３行目を一緒にして「cn.Open "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & DBpath & DBname」としてもOKです。

１６５～１８２行目は１つのSQL文で、ADOXでテーブルの時と同じく合計１６列を作ります。列名は「Access操作上の型名称」とし、それに対応したデータ型は図４－１の表の「AccessDBのSQLで設定する時の型」を使用しています。複数の型が使用できるものは先頭の型を使用していますが、他の型でも同じ結果が得られるはずです（但し、添付ファイル型以外は）。
特記するものとして「十進型」は、NUMERICまたはDECIMALを使用し引数のカッコ内に「全体の桁数」と「小数点以下の桁数」をカンマで区切って指定します。
また、オートナンバー型はCOUNTERまたはAUTOINCREMENTを指定するだけで、図４－２１の１２２行目「AutoIncrement」プロパティがTrueになってくれます。

なおSQLの場合、列名に「YesNo」や「OLEobject」という文字列を使用すると、「フィールド定義の構文エラー」というエラーが発生します。一般的なデータベースでは、どちらも予約語の対象にはなっていないのですが、Accessでは予約語（列名、オブジェクト名、変数名には使用できないワード）に登録されています（Microsoftサイト：Access の予約語の一覧）。「なぜかSQL文がエラーになる」ような場合は、一度予約語の問題を疑ってみると良いと思います。

SQL文が完成したら、１８５行目「rs.Open sql, cn」でRecordsetをOpenさせてSQL文を実行します。
但し、SQL文が間違ってたり、既に同名のテーブルが存在する場合にはエラーとなりますので、１８４行目「On Error Resume Next」でエラーをスルーさせ、１８６行目「If Not Err.Number = 0 Then」でエラーを検出し、１８７行目「MsgBox "設定が無効か、作成するテーブルが既に存在する可能性があります。"」でコメントを出します。

Create TableのSQL文の時は「SQLによって取得するデータは無い」ためにRecordsetは勝手に閉じるので、まだ閉じていないConnectionを１９１行目「cn.Close」で閉じます。

なお「Create」「Drop」「Insert」「Update」のように、テーブルからのレコード（戻り値）を受け取らない（＝テーブルに対して命令を実行するだけ）時の実行には、いくつかの方法があります。 「ExcelシートDBとSQLを使った倉庫管理システム」でも紹介していますが、３種が考えられます。 １つ目は図４－２４の「Recordsetオブジェクトを使用」する方法です。サンプルファイルではこの方法を多く使っています。 戻り値であるレコードを受取らないSQL文の場合にはRecordsetが勝手に閉じるため、rs.Closeは不要となっています。 （私は良く使う方法ですが、他サイトではほとんど紹介されていません。） 　Set cn = CreateObject("ADODB.Connection") 　Set rs = CreateObject("ADODB.Recordset") '　Set cmd = CreateObject("ADODB.Command") 　 　cn.Provider = "Microsoft.ACE.OLEDB.12.0" 　cn.Open データベースファイル名 　 　　sql = "Create Table " & テーブル名 & " (列設計)" 　 　　rs.Open sql, cn 　 　cn.Close 図４－２４ ２つ目は図４－２５の「ConnectionオブジェクトのExecuteメソッドでSQL文を実行」させる方法です。最もコード数の少ない方法で、しかも実行部分を「Set rs = cn.Execute sql」などとしておけば、Select文のSQLを実行した場合に「取得したレコードが変数rsに戻ってくる」ようにもできます。 　Set cn = CreateObject("ADODB.Connection") '　Set rs = CreateObject("ADODB.Recordset") '　Set cmd = CreateObject("ADODB.Command") 　 　cn.Provider = "Microsoft.ACE.OLEDB.12.0" 　cn.Open データベースファイル名 　 　　sql = "Create Table " & テーブル名 & " (列設計)" 　 　　cn.Execute sql 　 　cn.Close 図４－２５ ３つ目は図４－２６の「CommandオブジェクトのExecuteメソッドでSQL文を実行」させる方法です。実行部分を「Set rs = cmd.Execute」などとしておけば、Select文を実行した場合に「取得したレコードが変数rsに戻ってくる」ようにもできます。 またSQLインジェクション対策として「プレースホルダ」を使う場合は、この手法が必要そうです。 　Set cn = CreateObject("ADODB.Connection") '　Set rs = CreateObject("ADODB.Recordset") 　Set cmd = CreateObject("ADODB.Command") 　 　cn.Provider = "Microsoft.ACE.OLEDB.12.0" 　cn.Open データベースファイル名 　 　　sql = "Create Table " & テーブル名 & " (列設計)" 　 　　cmd.ActiveConnection = cn 　　cmd.CommandText = sql 　　cmd.Execute 　 　cn.Close 図４－２６ 他サイトを見た限りでは３つ目の方法が一番多いようですが、分かり易さから言うと２つ目かな　と思います。また、Select文でレコードを取得する時にはほとんどが１つ目のパターンですので、Select文以外でも同じパターンを使って「SQL実行時以降を取得データ有無で分岐させる」という考え方で、私は１つ目を良く使っています。

４－５．テーブルの削除

４－５－１．ADOXによる方法

ADOXを利用してテーブルを削除するのが図４－２７です。

200. '==========　⇩(6)　ADOXによるテーブルの削除　============
201. Sub ADOdeleteTable()
202. 　Const DBname As String = "it-081.accdb"
203. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
204. 　Const Tname As String = "AllDataType"
205. 　Dim Cat As Object 　　　 '←Catalogオブジェクト
207. 　Set Cat = CreateObject("ADOX.Catalog")
208. 　Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
209. 　　　　　　　　　　　　　"Data Source=" & DBpath & DBname
211. 　On Error Resume Next
212. 　　Cat.Tables.Delete Tname
214. 　　If Not Err.Number = 0 Then
215. 　　　MsgBox "削除できません。｢" & Tname & "｣が存在しないか、使用中の可能性があります。"
216. 　　End If
217. 　On Error GoTo 0
219. 　Set Cat = Nothing
220. End Sub

図４－２７

２０７行目「Set Cat = CreateObject("ADOX.Catalog")」でCatalogオブジェクトを生成し、２０８～２０９行目「Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & DBpath & DBname」でデータベースファイルを開きます。

そして２１２行目「Cat.Tables.Delete Tname」で、テーブル「Tname」を削除します。構文は以下です。
　「Catalogオブジェクト.Tables.Delete 削除するテーブル名 」
但し削除しようとするテーブルが無かったり、テーブルが使用中だったりした時には削除できずエラーが出ます。そこで２１１行目「On Error Resume Next」でエラーをスルーさせ、２１４行目「If Not Err.Number = 0 Then」でエラーを検出し、２１５行目「MsgBox "削除できません。｢" & Tname & "｣が存在しないか、使用中の可能性があります。"」でコメントを出します。

４－５－２．SQLによる方法

ADOXを使わず、データベースに対してSQLを発行してテーブルを削除する方法が、図４－２８です。

230. '==========　⇩(7)　SQLによるテーブルの削除　============
231. Sub SQLdeleteTable()
232. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "\"
233. 　Const DBname As String = "it-081.accdb"
234. 　Const Tname As String = "AllDataType"
235. 　Dim sql As String 　　　 '←SQL文
236. 　Dim cn As Object 　　　 '←Connectionオブジェクト
237. 　Dim rs As Object 　　　 '←Recordsetオブジェクト
239. 　Set cn = CreateObject("ADODB.Connection")
240. 　Set rs = CreateObject("ADODB.Recordset")
242. 　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
243. 　cn.Open DBpath & DBname
245. 　sql = "drop Table " & Tname
247. 　On Error Resume Next
248. 　　rs.Open sql, cn
249. 　　If Not Err.Number = 0 Then
250. 　　　MsgBox "削除するテーブルが存在しないか、使用中の可能性があります。"
251. 　　End If
252. 　On Error GoTo 0
254. 　cn.Close
256. 　Set rs = Nothing
257. 　Set cn = Nothing
258. End Sub

図４－２８

２３９行目「Set cn = CreateObject("ADODB.Connection")」でConnectionオブジェクトを生成し、２４０行目「Set rs = CreateObject("ADODB.Recordset")」でRecordsetオブジェクトを生成します。
２４２行目「cn.Provider = "Microsoft.ACE.OLEDB.12.0"」でプロバイダを設定し、２４３行目「cn.Open DBpath & DBname」でデータベースに接続します。

２４５行目「sql = "drop Table " & Tname」は、テーブルを削除するSQL文で構文は以下の通りです。
　「Drop Table 削除するテーブル名 」
２４８行目「rs.Open sql, cn」でRecordsetをOpenさせてSQL文を実行します。
但しテーブルが無かったり、テーブルが使用中だったりすると、削除できずにエラーとなりますので、２４７行目「On Error Resume Next」でエラーをスルーさせ、２４９行目「If Not Err.Number = 0 Then」でエラーを検出し、２５０行目「MsgBox "削除するテーブルが存在しないか、使用中の可能性があります。"」でコメントを出します。
Drop TableのSQL文の時は「SQLによって取得するデータは無い」ためにRecordsetは勝手に閉じるので、まだ閉じていないConnectionを２５４行目「cn.Close」で閉じます。

４－６．テーブルの修正

４－６－１．ADOXによる方法

ADOXを使ってテーブルの修正をするのが図４－２９です。なお図４－２９では、「追加した列」を「列の削除」しているため、そのまま実行しても意味がありません。また見え消しにしている「列属性の変更」を実行するとエラーがでますので注意して下さい。

260. '==========　⇩(8)　ADOXによるテーブルの修正　============
261. Sub ADOalterTable()
262. 　Const DBname As String = "it-081.accdb"
263. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "\"
264. 　Const Tname As String = "AllDataType"
265. 　Dim Cat As Object 　　　 '←Catalogオブジェクト
267. 　Set Cat = CreateObject("ADOX.Catalog")
268. 　Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
269. 　　　　　　　　　　　　　"Data Source=" & DBpath & DBname
271. 　On Error Resume Next
272. 　　'// 列の追加 //
273. 　　Cat.Tables(Tname).Columns.Append "alterColumn", adUnsignedTinyInt
275. 　　'// 列属性の変更 //
276. '　　Cat.Tables(Tname).Columns("alterColumn").Type = adInteger
278. 　　'// 列の削除 //
279. 　　Cat.Tables(Tname).Columns.Delete "alterColumn"
281. 　　If Not Err.Number = 0 Then
282. 　　　MsgBox "変更するテーブルが存在しないか、列変更が間違っている可能性があります。"
283. 　　End If
284. 　On Error GoTo 0
286. 　Set Cat = Nothing
287. End Sub

図４－２９

２６７行目「Set Cat = CreateObject("ADOX.Catalog")」でCatalogオブジェクトを生成し、２６８～２６９行目「Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & DBpath & DBname」でデータベースファイルを開きます。

列の修正には３種類が考えられます。「列の追加」「列の属性（データ型）変更」「列の削除」です。
まず「列の追加」は２７３行目「Cat.Tables(Tname).Columns.Append "alterColumn", adUnsignedTinyInt」のように
　「Catalogオブジェクト.Tables(テーブル名).Columns.Append 追加する列名,データ型」
とします。ここでは追加する列名をalterColumn、データ型をバイト型（adUnsignedTinyInt）としています。
テーブルを新規に作成（図４－２１）する際には、Tableオブジェクトに対して列をAppendしていたのですが、列追加では「既に存在するテーブルに対する変更」のため、直接Catalogオブジェクトの列に対して処理をします。

１つ飛ばして「列の削除」は、２７９行目「Cat.Tables(Tname).Columns.Delete "alterColumn"」のように
　「Catalogオブジェクト.Tables(テーブル名).Columns.Delete 削除する列名」
とします。

最後に「列の属性（データ型）変更」ですが、本来ならば２７６行目「Cat.Tables(Tname).Columns("alterColumn").Type = adInteger」のような形でデータ型を変更できれば良いのですが、エラーが出てしまいます。
ですので、一旦「列の削除」を行った後、新たなデータ型で「列の追加」をするしか手が無さそうです。

４－６－２．SQLによる方法

SQLによるテーブルの修正を行っているのが図４－３０です。なおここでは、同じ列名に対して「列の追加」「列属性の変更」「列の削除」を行っているため、そのまま実行しても意味がない事を御了解下さい。

290. '==========　⇩(9)　SQLによるテーブルの修正　============
291. Sub SQLalterTable()
292. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
293. 　Const DBname As String = "it-081.accdb"
294. 　Const Tname As String = "AllDataType"
295. 　Dim sql As String 　　　 '←SQL文
296. 　Dim cn As Object 　　　 '←Connectionオブジェクト
297. 　Dim rs As Object 　　　 '←Recordsetオブジェクト
299. 　Set cn = CreateObject("ADODB.Connection")
300. 　Set rs = CreateObject("ADODB.Recordset")
302. 　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
303. 　cn.Open DBpath & DBname
305. 　　'// 列の追加 //
306. 　　sql = "alter Table " & Tname & " ADD alterColumn BYTE "
307. 　　rs.Open sql, cn
309. 　　'// 列属性の変更 //
310. 　　sql = "alter Table " & Tname & " ALTER COLUMN alterColumn long "
311. 　　rs.Open sql, cn
313. 　　'// 列の削除 //
314. 　　sql = "alter Table " & Tname & " DROP alterColumn "
315. 　　rs.Open sql, cn
317. 　cn.Close
319. 　Set rs = Nothing
320. 　Set cn = Nothing
321. End Sub

図４－３０

２９９行目「Set cn = CreateObject("ADODB.Connection")」でConnectionオブジェクトを生成し、３００行目「Set rs = CreateObject("ADODB.Recordset")」でRecordsetオブジェクトを生成します。
３０２行目「cn.Provider = "Microsoft.ACE.OLEDB.12.0"」でプロバイダを設定し、３０３行目「cn.Open DBpath & DBname」でデータベースに接続します。

各SQL文の下の行の「rs.Open sql, cn」でRecordsetをOpenさせてSQL文を実行します。
テーブルが無かったり使用中だったりした時のエラー処理は省略しています。必要に応じて付けて下さい。

５．データベース制約の設定と解除

データベースの制約には、主として以下のようなものがあります。

種類	内容	Accessでの設定可否
Check(検査制約)	列のﾃﾞｰﾀ値が特定の条件を満たすか否かを検査	△(Column Validation Ruleで設定可)
NOT NULL(非ヌル制約)	列のﾃﾞｰﾀがNullで無いことを保証	〇
UNIQUE(一意性制約)	列のﾃﾞｰﾀが他の行と重複しないことを保証	〇
PRIMARY KEY(主ｷｰ制約)	主ｷｰの列に設定。(＝｢UNIQUE｣＋｢NOT NULL｣)	〇
FOREIGN KEY(参照制約)	列のﾃﾞｰﾀが他のﾃｰﾌﾞﾙのﾃﾞｰﾀを参照して一致することを保証	〇

図５－１

Accessとして「Check制約」という名前は使っていないようですが、プロパティ「Jet OLEDB:Column Validation Rule」を使えば同様の機能を得ることが可能です。
「ADOによるテーブルの作成」でも説明しましたが、ADOを使った場合「NOT NULL」制約はテーブルを新規作成する段階での設定が必要です（KeyやIndexを使った後付け設定も出来そうな気はするのですが、詳しくは調べていません）。
但しSQLを使えば、Alter Tableを使って「後付けでNot Nullの設定」が可能となります。

５－１．ADOXによる制約設設定方法

ADOXを使ったデータベース制約の設定を説明するために、図５－２で２つのテーブル（KeyTypeA、KeyTypeB）を作成し、その後の図５－４で制約の設定をします。但しNOT NULLはテーブル作成時の設定が必要なために図５－２側で行います。
サンプルファイルを実行するのであれば、図５－２のあとで図５－４を実行して下さい。

340. '==========　⇩(10)　２つのテーブル作成　============
341. Sub ADOmakeKey1()
342. 　Const DBname As String = "it-081.accdb"
343. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
344. 　Const TnameA As String = "KeyTypeA"
345. 　Const TnameB As String = "KeyTypeB"
346. 　Dim Cat As Object 　　 '←Catalogオブジェクト
347. 　Dim TBL1 As Object 　 '←Tableオブジェクト(KeyTypeA用)
348. 　Dim TBL2 As Object 　 '←Tableオブジェクト(KeyTypeB用)
350. 　Set Cat = CreateObject("ADOX.Catalog")
351. 　Set TBL1 = CreateObject("ADOX.Table")
352. 　Set TBL2 = CreateObject("ADOX.Table")
353. 　Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
354. 　　　　　　　　　　　　　"Data Source=" & DBpath & DBname
356. 　TBL1.Name = TnameA
357. 　Set TBL1.ParentCatalog = Cat
359. 　With TBL1
360. 　　.Columns.Append "数値整数", adSmallInt
361. 　　.Columns("数値整数").Attributes = adColNullable + adColFixed
362. 　　.Columns.Append "数値長整数", adInteger
363. 　　.Columns("数値長整数").Attributes = adColNullable + adColFixed
364. 　End With
366. 　Cat.Tables.Append TBL1
368. '============================
369. 　TBL2.Name = TnameB
370. 　Set TBL2.ParentCatalog = Cat
372. 　With TBL2
373. 　　.Columns.Append "短いテキスト", adVarWChar
374. 　　　.Columns("短いテキスト").Properties("Nullable") = False 　 '←Not Nullの設定
375. 　　.Columns.Append "長いテキスト", adLongVarWChar
376. 　　　.Columns("長いテキスト").Attributes = adColNullable
377. 　　.Columns.Append "数値バイト", adUnsignedTinyInt
378. 　　　.Columns("数値バイト").Attributes = adColNullable + adColFixed
379. 　　.Columns.Append "数値整数", adSmallInt
380. 　　　.Columns("数値整数").Attributes = adColNullable + adColFixed
381. 　　.Columns.Append "数値長整数", adInteger
382. 　　　.Columns("数値長整数").Attributes = adColNullable + adColFixed
383. 　End With
385. 　Cat.Tables.Append TBL2
387. 　Set TBL1 = Nothing
388. 　Set TBL2 = Nothing
389. 　Set Cat = Nothing
390. End Sub

図５－２

３５０行目「Set Cat = CreateObject("ADOX.Catalog")」でCatalogオブジェクトを生成した後、３５１行目「Set TBL1 = CreateObject("ADOX.Table")」と３５２行目「Set TBL2 = CreateObject("ADOX.Table")」で２つのTableオブジェクトを生成しています。これは今回２つのテーブルを１つのプロシージャ内で作成している為です。
３５３～３５４行目「Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & DBpath & DBname」でデータベースファイルに接続します。

ここで作る２つのテーブルのイメージは、図５－３のようになっています。

図５－３

まず、３５６～３６６行目は１つ目のテーブル（KeyTypeAテーブル）の作成です。
３５６行目「TBL1.Name = TnameA」でテーブル名を設定し、３５７行目「Set TBL1.ParentCatalog = Cat」で、その所属を接続中のデータベースファイル（Cat）にしています。

３５９行目「With TBL1」以下でTBL1（KeyTypeAテーブル）の列を作成します。
３６０行目「.Columns.Append "数値整数", adSmallInt」で「数値整数」列を整数型（adSmallInt）で作成し、３６１行目「.Columns("数値整数").Attributes = adColNullable + adColFixed」で、その列を「Null挿入可」の状態にします。
３６２行目「.Columns.Append "数値長整数", adInteger」で「数値長整数」列を長整数型（adInteger）で作成し、３６３行目「.Columns("数値長整数").Attributes = adColNullable + adColFixed」で、その列を「Null挿入可」の状態にします。

３６６行目「Cat.Tables.Append TBL1」で、テーブル（KeyTypeAテーブル）をデータベース上に作成します。

次に、３６９～３８５行目は２つ目のテーブル（テーブル名＝KeyTypeB）の作成です。
３６９行目「TBL2.Name = TnameB」でテーブル名を設定し、３７０行目「Set TBL2.ParentCatalog = Cat」で、その所属を接続中のデータベースファイル（Cat）にしています。

３７２行目「With TBL2」以下でTBL2（KeyTypeBテーブル）の列を作成します。
３７３行目「.Columns.Append "短いテキスト", adVarWChar」で「短いテキスト」列を作成し、３７４行目「.Columns("短いテキスト").Properties("Nullable") = False」で「Not Null」の設定（＝非ヌル制約）を行っています。
３７４行目でNot Nullを指定しなくても、図４－６で示したように３７３行目だけで「Not Null状態」になっているのですが、明示的に非ヌル制約設定をしています。

３７５行目「.Columns.Append "長いテキスト", adLongVarWChar」で「長いテキスト」列を作成し、３７６行目「.Columns("長いテキスト").Attributes = adColNullable」でNull挿入可の状態にしています。
３７７行目「.Columns.Append "数値バイト", adUnsignedTinyInt」で「数値バイト」列を作成し、３７８行目「.Columns("数値バイト").Attributes = adColNullable + adColFixed」でNull挿入可の状態にしています。
３７９行目「.Columns.Append "数値整数", adSmallInt」で「数値整数」列を作成し、３８０行目「.Columns("数値整数").Attributes = adColNullable + adColFixed」でNull挿入可の状態にしています。
３８１行目「.Columns.Append "数値長整数", adInteger」で「数値長整数」列を作成し、３８２行目「.Columns("数値長整数").Attributes = adColNullable + adColFixed」でNull挿入可の状態にしています。

３８５行目「Cat.Tables.Append TBL2」で、テーブル（KeyTypeBテーブル）をデータベース上に作成します。

これで、KeyTypeBテーブルの短いテキスト列のみ「Not Null制約」を設定し、残りはKeyTypeAの全列を含めてNull許容の状態になりました。
この状態から、各列に残りの制約を設定していくのが、図５－４です。

400. '==========　⇩(11)　ADOによるデータベース制約の設定　============
401. Sub ADOmakeKey2()
402. 　Const DBname As String = "it-081.accdb"
403. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
404. 　Const TnameA As String = "KeyTypeA"
405. 　Const TnameB As String = "KeyTypeB"
406. 　Dim Cat As Object 　　 '←Catalogオブジェクト
408. 　Set Cat = CreateObject("ADOX.Catalog")
409. 　Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
410. 　　　　　　　　　　　　　"Data Source=" & DBpath & DBname
412. 　With Cat.Tables(TnameA)
413. 　　.Keys.Append "key1", adKeyPrimary, "数値長整数"
414. 　End With
416. 　With Cat.Tables(TnameB)
417. 　　.Columns("長いテキスト").Properties("Jet OLEDB:Column Validation Rule") = "<>'abc'"
418. 　　.Columns("長いテキスト").Properties("Jet OLEDB:Column Validation Text") = "違反です"
420. 　　.Keys.Append "key2", adKeyUnique, "数値バイト"
421. 　　.Keys.Append "key3", adKeyPrimary, "数値整数"
422. 　　.Keys.Append "key4", adKeyForeign, "数値長整数", TnameA, "数値長整数"
423. 　End With
425. 　Set Cat = Nothing
426. End Sub

図５－４

４０８行目でCatalogオブジェクトを生成し、４０９～４１０行目でデータベースファイルに接続します。
４１３行目「.Keys.Append "key1", adKeyPrimary, "数値長整数"」は、KeyTypeAテーブル（TnameA）の「数値長整数」列に対して主キー制約を設定しています。ADOで主キー設定をするには、キーの型を図５－５の表の中の定数adKeyPrimaryを選びます。
　テーブル.Keys.Append　キーの名前 , キーの型 , 列名 （主キー・一意性制約）

定数	値	内容
adKeyPrimary	1	主キー制約（既定値）
adKeyForeign	2	参照制約
adKeyUnique	3	一意性制約

図５－５

４１６～４２３行目ではKeyTypeBテーブル（TnameB）に対して制約設定をしています。
４１７行目「.Columns("長いテキスト").Properties("Jet OLEDB:Column Validation Rule") = "<>'abc'"」では、「長いテキスト」列のPropertiesの９番目（図４－５参照）に「入力規制の式」として「<>'abc'」を設定しています。これは「文字列として 'abc' 以外を入力しなさい」という意味です。
そして、その規制に引っ掛かった（＝'abc' を入力した）場合に出力する文字をPropertiesの８番目に設定します。ここでは４１８行目「.Columns("長いテキスト").Properties("Jet OLEDB:Column Validation Text") = "違反です"」で「違反です」との文字列が出力されるようにしています。

なお違反した時の文字列を取得するには、データを行挿入するコード（今回だと「cn.Execute sql」など）の前に「On Error Resume Next」でエラーが出てもスルーさせるようにしておき、エラーが発生していたら「MsgBox Err.Description」とエラー内容の説明文字列をメッセージ出力させます（図５－６の左側）。
なお出力文字列を設定しない場合は、図５－６の右側のように詳しいエラー内容の説明文が表示されることになりますが、テーブル構造（テーブル名、列名、規制内容）が丸見えになってしまいますので、もし設定する場合は適切なエラー内容を出力させるようにして下さい。

図５－６

４２０行目「.Keys.Append "key2", adKeyUnique, "数値バイト"」は「数値バイト」列にUNIQUE規制を設定しています。このUniqueは「他の行のデータと重複しない」という意味ですが、Null値は許容していますので注意が必要です。
４２１行目「.Keys.Append "key3", adKeyPrimary, "数値整数"」は「数値整数」列を主キー（PRIMARY KEY）に設定しています。主キーの列は「Not Null」＋「Unique」ですので、「他の行のデータと重複せず、Nullも含まない」列となり、「テーブル内でレコードを一意に識別」することができます。

４２２行目「.Keys.Append "key4", adKeyForeign, "数値長整数", TnameA, "数値長整数"」は、「KeyTypeBの数値長整数行」と「KeyTypeAの数値長整数行」の間に参照制約を設定しています。図５－３で、KeyTypeAテーブルとKeyTypeBテーブルを結びつけている「青い線」の部分になります。
　テーブル.Keys.Append　ｷｰの名前 , adKeyForeign , ｷｰ作成側の列名 , 関連ﾃｰﾌﾞﾙ名 , 関連ﾃｰﾌﾞﾙ側の列名 （参照制約）

ここで「関連テーブル側の列」は、キー作成側の列と「同じデータ型」で、且つ「Primary Key 相当」である必要があります。但し「キー作成側の列」はNot Null やUniqueである必要はありません。

ここで設定しているデータベース制約の記録は「テーブル内のKeyオブジェクト」のプロパティとして保存されます。プロパティは図５－７のように６つあります。

Keyのプロパティ

プロパティ	内容
Columns	対象の列
DeleteRule	主ｷｰが削除された場合の動作
Name	キー名
RelatedTable	環形ﾃｰﾌﾞﾙの名前
Type	ｷｰの種類(主ｷｰ､参照ｷｰ､一意ｷｰ)
UpdateRule	主ｷｰが更新された場合の動作

図５－７

図５－４で設定した４つのKey（Key1～Key4）が、どこにどのように書き込まれているかを表したのが図５－８です。
Colomnsプロパティの中は更に分かれているので、その内の「列名（Name）」と「関連する列（RelatedColumn）」のみを図５－８では示しています。

Table	KeyTypeA	KeyTypeB
Keyの ﾌﾟﾛﾊﾟﾃｨ	1 Primary	2 Unique	3 Primary	4 Foreign
Columns().Name	"数値長整数"	"数値ﾊﾞｲﾄ"	"数値整数"	"数値長整数"
Columns.RelatedColumn	""	""	""	"数値長整数"
DeleteRule	adRlNone	adRlNone	adRlNone	adRlNone
Name	"Key1"	"Key2"	"Key3"	"Key4"
RelatedTable	""	""	""	"KeyTypeA"
Type	adKeyPrimary	adKeyUnique	adKeyPrimary	adKeyForeign
UpdateRule	adRlNone	adRlNone	adRlNone	adRlNone

図５－８

図５－８の１番目は、４１３行目「.Keys.Append "key1", adKeyPrimary, "数値長整数"」を実行した結果です。「Key1」というキー名で、Primary制約を設定していますので、TypeプロパティにはadKeyPrimaryという値が設定されています。
２番目は４２０行目「.Keys.Append "key2", adKeyUnique, "数値バイト"」でUnique制約を、３番目は４２１行目「.Keys.Append "key3", adKeyPrimary, "数値整数"」でPrimary制約をしています。
そして４番目は４２２行目「.Keys.Append "key4", adKeyForeign, "数値長整数", TnameA, "数値長整数"」で、Foreign制約のため、TypeプロパティにはadKeyForeign値が設定され、且つその関連先の情報を「相手先のテーブル名」はRelatedTableプロパティに、「相手先の列名」はColumnsプロパティの中のRelatedColumnに設定されています。

なお「インデックスの設定と解除」でも説明しますが、設定した制約は、Keyオブジェクトのみに記録されるのでは無く、Indexオブジェクトのプロパティにも記録されている様です。図５－４で設定した後のIndexの各プロパティ値を図５－９に示します。

Table	KeyTypeA	KeyTypeB
Indexの ﾌﾟﾛﾊﾟﾃｨ	1 Primary	2 Unique	3 Primary	4 Foreign
Clustered	False	False	False	False
Columns().Name	"数値長整数"	"数値バイト"	"数値整数"	"数値長整数"
IndexNulls	adIndexNullsDisallow	adIndexNullsAllow	adIndexNullsDisallow	adIndexNullsAllow
Name	"Key1"	"Key2"	"Key3"	"Key4"
PrimaryKey	True	False	True	False
Properties	-	-	-	-
Unique	True	True	True	False

図５－９

１番目と３番目はPrimary制約ですので、PrimaryKeyプロパティがTrueになります。またPrimaryとは「Unique」＋「Not Null」ですので、UniqueプロパティがTrueに、IndexNullsプロパティがadIndexNullsDisallow（Null値入力を許可しない：図５－１０参照）となります。
２番目はUnique制約ですので、UniqueプロパティがTrueになります。UniqueではNullは許しますので、IndexNullsはadIndexNullsAllow（Null挿入を許可：図５－１０参照）となります。
4番目はForeign制約ですが、その列（KeyTypeBの数値長整数列）はPrimary、Not Null、Uniqueのどれにも当てはまらないため、図５－９のような値となります。

定数	値	内容
adIndexNullsAllow	0	Null値入力を許可
adIndexNullsDisallow	1	Null値入力を許可しない(既定値)
adIndexNullsIgnore	2	Null値はｲﾝﾃﾞｯｸｽに含めない
adIndexNullsIgnoreAny	4	複数列ｷｰの場合､Null値はｲﾝﾃﾞｯｸｽに含めない

図５－１０

５－２．SQLによる制約設定方法

５－２－１．Alter Tableで後付け設定する方法

SQLにより制約を設定するのが、図５－１１です。ADOXの時と同様に２つのテーブル（KeyTypeA、KeyTypeB）を作成した後、制約設定をしています。なお、Not NullもAlter Tableで「列修正に合わせてNot Null設定可」ですが、ここではADOX時と同様にCreate Table時に設定をしています。

440. '==========　⇩(12)　SQLによるデータベース制約の設定１　============
441. Sub SQLmakeKey1()
442. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
443. 　Const DBname As String = "it-081.accdb"
444. 　Const TnameA As String = "KeyTypeA"
445. 　Const TnameB As String = "KeyTypeB"
446. 　Dim sql As String 　　 '←実行するSQL文
447. 　Dim cn As Object 　　 '←Connectionオブジェクト
448. 　Dim rs As Object 　　 '←Recordsetオブジェクト
450. 　Set cn = CreateObject("ADODB.Connection")
451. 　Set rs = CreateObject("ADODB.Recordset")
453. 　cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
454. 　　　　　　　　　　"Data Source=" & DBpath & DBname
456. 　sql = "Create Table " & TnameA & " (" & _
457. 　　　　　"数値整数 SHORT ," & _
458. 　　　　　"数値長整数 LONG" & _
459. 　　　　　")"
460. 　rs.Open sql, cn
462. 　sql = "Create Table " & TnameB & " (" & _
463. 　　　　　"短いテキスト ALPHANUMERIC NOT NULL," & _
464. 　　　　　"長いテキスト LONGTEXT," & _
465. 　　　　　"数値バイト BYTE ," & _
466. 　　　　　"数値整数 SHORT," & _
467. 　　　　　"数値長整数 LONG" & _
468. 　　　　　")"
469. 　rs.Open sql, cn
471. '=============================
473. 　sql = "Alter Table " & TnameA & _
474. 　　　　　" ADD CONSTRAINT key1 Primary Key(数値長整数)"
475. 　rs.Open sql, cn
477. 　sql = "Alter Table " & TnameB & _
478. 　　　　　" ADD CONSTRAINT key2 Unique(数値バイト)"
479. 　rs.Open sql, cn
481. 　sql = "Alter Table " & TnameB & _
482. 　　　　　" ADD CONSTRAINT key3 Primary Key(数値整数)"
483. 　rs.Open sql, cn
485. 　sql = "Alter Table " & TnameB & _
486. 　　　　　" ADD CONSTRAINT key4 Foreign Key(数値長整数) " & _
487. 　　　　　" References " & TnameA & " (数値長整数)"
488. 　rs.Open sql, cn
490. 　cn.Close
492. 　Set rs = Nothing
493. 　Set cn = Nothing
494. End Sub

図５－１１

４５０～４５１行目で、ConnectionオブジェクトとRecordsetオブジェクトを生成し、４５３～４５４行目でデータベースファイルと接続します。それ以降４５６～４６９行目で、第一段階として「KeyTypeAテーブルとKeyTypeBテーブルを作成」し、４７３～４８８行目で第二段階として「Keyを追加」します。

４５６～４５９行目のSQL文は、２つの列（数値整数、数値長整数）を持つテーブルを作成するものです。
４５６行目「sql = "Create Table " & TnameA & " (" & _ 」は、TnameA（＝"KeyTypeA"）という名のテーブルを作ります。
４５７行目「"数値整数 SHORT ," & _ 」で「数値整数」という名の列をSHORT型（ADOXだとadSmallInt型）で指定します。
４５８行目「"数値長整数 LONG" & _ 」で「数値長整数」という名の列をLONG型（ADOXだとadInteger型）で指定します。
４６０行目「rs.Open sql, cn」では、そのSQL文を実行し「KeyTypeAテーブル」を作成します。

４６２～４６８行目のSQL文は、５つの列を持つテーブルを作成するものです。
４６２行目「sql = "Create Table " & TnameB & " (" & _ 」は、TnameB（＝"KeyTypeB"）という名のテーブルを作ります。
４６３行目「"短いテキスト ALPHANUMERIC NOT NULL," & _ 」で「短いテキスト」という名の列をALPHANUMERIC型（ADOXだとadVarWChar型）で指定します。この時 Not Null制約も付加し、Null値を挿入できないようにします。
４６４行目「"長いテキスト LONGTEXT," & _ 」で「長いテキスト」という名の列をLONGTEXT型（ADOXだとadLongVarWChar型）で指定します。
４６５行目「"数値バイト BYTE ," & _ 」で「数値バイト」という名の列をBYTE型（ADOXだとadUnsignedTinyInt型）で指定します。
４６６行目「"数値整数 SHORT," & _ 」で「数値整数」という名の列をSHORT型（ADOXだとadSmallInt型）で指定します。
４６７行目「"数値長整数 LONG" & _ 」で「数値長整数」という名の列をLONG型（ADOXだとadInteger型）で指定します。
４６９行目「rs.Open sql, cn」では、そのSQL文を実行し「KeyTypeBテーブル」を作成します。

４７３行目以降は、上記で作った２つのテーブル（KeyTypeA、KeyTypeB）に対して、キーを設定していきます。キーを設定する構文は以下の様になります。
　「 Alter Table　テーブル名　Add Constraint　キー名　制約(列名) 」
制約の部分には図５－１２を指定します。但し、Not NullはExcel側からは実行不可（エラーが出る）なので、列のプロパティで設定するしか手がありません（Access上では実行可能かもしれません）。
また参照制約の場合は、制約(列名)に続けて関連するテーブルと列を指定する必要があるため、以下のような構文になります。
　「 Alter Table　テーブル名　Add Constraint　キー名　Foreign Key(列名) References　テーブル名(列名) 」

制約	内容
Primary Key	主キー
Unique	一意
Not Null	非ﾇﾙ。Excelからは実行不可
Foreign Key	参照｡＋References ﾃｰﾌﾞﾙ名(列名)

図５－１２

４７３行目「sql = "Alter Table " & TnameA & _ 」でKeyTypeAテーブルを対象に、４７４行目「" ADD CONSTRAINT key1 Primary Key(数値長整数)" 」で「数値長整数」列に対して「Primary Key」をKey設定します。Key値は「key1」とします。
SQLでは、列に対して制約を設定する場合は「制約(列名)」という形で指定します。
４７５行目「rs.Open sql, cn」で、そのSQL文を実行し「KeyTypeAテーブル」にKey1を追加します。

４７７行目「sql = "Alter Table " & TnameB & _ 」でKeyTypeBテーブルを対象に、４７８行目「" ADD CONSTRAINT key2 Unique(数値バイト)" 」で「数値バイト」列に対して「Unique」をKey設定します。Key値は「key2」とします。
４７９行目「rs.Open sql, cn」で、そのSQL文を実行し「KeyTypeBテーブル」にKey2を追加します。

４８１行目「sql = "Alter Table " & TnameB & _ 」でKeyTypeBテーブルを対象に、４８２行目「" ADD CONSTRAINT key3 Primary Key(数値整数)" 」で「数値整数」列に対して「Primary Key」をKey設定します。Key値は「key3」とします。
４８３行目「rs.Open sql, cn」で、そのSQL文を実行し「KeyTypeBテーブル」にKey3を追加します。

４８５行目「sql = "Alter Table " & TnameB & _ 」でKeyTypeBテーブルを対象に、４８６行目「" ADD CONSTRAINT key4 Foreign Key(数値長整数) " & _ 」で「数値長整数」列に対して「Foreign Key」をKey設定します。Key値は「key4」とします。
そして、その関連するテーブル・列を４８７行目「" References " & TnameA & " (数値長整数)" 」で指定します。指定の方法は、「References」の後に「テーブル名(列名)」という形にします。
４８８行目「rs.Open sql, cn」で、そのSQL文を実行し「KeyTypeBテーブル」にKey4を追加します。

なお、ADOX側の図５－４の４１７～４１８行目で設定したような「"長いテキスト"列へのCheck制約」は、SQL文では実現できないようです。Microsoftのサイト等では「sql = "Alter Table " & TnameB & " ADD CONSTRAINT key5 CHECK (長いテキスト <> 'abc') "」とすればCheck制約の設定は可能　との様に読み取れるのですが、エラーが出ないのにKeyへの登録はされません。
もしCheck制約を設定したい場合は、ADOXを使用した図５－４の「直接プロパティ値に書き込む」方法が良さそうです。

この図５－１１では Key1～Key4の４つのKeyを設定していますが、それらの情報はADOXの時と同様に、図５－８（Keyオブジェクト）と図５－９（Indexオブジェクト）に保存されています。

５－２－２．Create Table時に併行して設定する方法

制約を図５－１１のように後付け設定するのもOKですが、SQLを使うのであれば図５－１３のように「テーブル作成（Create Table）時に併せて設定」する方が簡単です。構文としては以下になります。
　「Create Table　テーブル名 ( 列名　データ型　制約 , ･･･)」
なお参照制約については、通常の列作成に続けて下記のように指定します。
　「Create Table　テーブル名 ( ･･･ , 列名　データ型 , Foreign Key(列名)　関連ﾃｰﾌﾞﾙ名(関連ﾃｰﾌﾞﾙの列名) )」

510. '==========　⇩(13)　SQLによるデータベース制約の設定２　============
511. Sub SQLmakeKey2()
512. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
513. 　Const DBname As String = "it-081.accdb"
514. 　Const TnameA As String = "KeyTypeA"
515. 　Const TnameB As String = "KeyTypeB"
516. 　Dim sql As String 　　 '←実行するSQL文
517. 　Dim cn As Object 　　 '←Connectionオブジェクト
518. 　Dim rs As Object 　　 '←Recordsetオブジェクト
520. 　Set cn = CreateObject("ADODB.Connection")
521. 　Set rs = CreateObject("ADODB.Recordset")
523. 　cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
524. 　　　　　　　"Data Source=" & DBpath & DBname
526. 　sql = "Create Table " & TnameA & " (" & _
527. 　　　　　　"数値整数 SHORT ," & _
528. 　　　　　　"数値長整数 LONG PRIMARY KEY" & _
529. 　　　　　　")"
530. 　rs.Open sql, cn
532. 　sql = "Create Table " & TnameB & " (" & _
533. 　　　　　　"短いテキスト ALPHANUMERIC NOT NULL," & _
534. 　　　　　　"長いテキスト LONGTEXT," & _
535. 　　　　　　"数値バイト BYTE UNIQUE," & _
536. 　　　　　　"数値整数 SHORT PRIMARY KEY," & _
537. 　　　　　　"数値長整数 LONG," & _
538. 　　　　　　"FOREIGN KEY(数値長整数) REFERENCES " & TnameA & " (数値長整数) " & _
539. 　　　　　　")"
540. 　rs.Open sql, cn
542. 　cn.Close
544. 　Set rs = Nothing
545. 　Set cn = Nothing
546. End Sub

図５－１３

５２０～５２１行目で、ConnectionオブジェクトとRecordsetオブジェクトを生成し、５２３～５２４行目でデータベースファイルと接続します。

５２６～５２９行目では「KeyTypeAテーブルを制約付きで作成」しています。
５２６行目「sql = "Create Table " & TnameA & " (" & _ 」で、TnameA（＝"KeyTypeA"）という名のテーブルを作ります。
５２７行目「"数値整数 SHORT ," & _ 」で「数値整数」という名の列をSHORT型（ADOXだとadSmallInt型）で指定します。
５２８行目「"数値長整数 LONG PRIMARY KEY" & _ 」で「数値長整数」という名の列をLONG型（ADOXだとadInteger型）で指定し、その列をPrimary Keyに指定しています。
５３０行目「rs.Open sql, cn」では、そのSQL文を実行し「KeyTypeAテーブル」を作成します。

５３２～５４０行目のSQL文は「KeyTypeBテーブルを制約付きで作成」しています。
５３２行目「sql = "Create Table " & TnameB & " (" & _ 」は、TnameB（＝"KeyTypeB"）という名のテーブルを作ります。
５３３行目「"短いテキスト ALPHANUMERIC NOT NULL," & _ 」で「短いテキスト」という名の列をALPHANUMERIC型（ADOXだとadVarWChar型）で指定します。この時 Not Null制約も付加し、Null値を挿入できないようにします。

５３４行目「"長いテキスト LONGTEXT," & _ 」で「長いテキスト」という名の列をLONGTEXT型（ADOXだとadLongVarWChar型）で指定します。この列はADOXではCheck制約を付けており、もしSQLで設定するとすれば「"長いテキスト LONGTEXT Check (長いテキスト <> 'abc')," & _ 」としたり、SQL文の最後に「" CHECK (長いテキスト <> 'abc') " & _」を付けたりするのでしょうが、今回環境ではどちらも有効にはなりません（列のデータ型の後にCheckを付けると、エラーとなります）。
ですので、今回は（ADOX側とテーブルの形を合わせるために）通常の列を作るだけとしました。

５３５行目「"数値バイト BYTE UNIQUE," & _ 」で「数値バイト」という名の列をBYTE型（ADOXだとadUnsignedTinyInt型）で指定し、その列をUniqueに指定しています。
５３６行目「"数値整数 SHORT PRIMARY KEY," & _ 」で「数値整数」という名の列をSHORT型（ADOXだとadSmallInt型）で指定し、その列をPrimary Keyに指定しています。
５３７行目「"数値長整数 LONG" & _ 」で「数値長整数」という名の列をLONG型（ADOXだとadInteger型）で指定します。その列の制約として、５３８行目「"FOREIGN KEY(数値長整数) REFERENCES " & TnameA & " (数値長整数) " & _ 」で「数値長整数」列に対して「Foreign Key」をKey設定し、その関連するテーブル（KeyTypeA）・列（数値長整数）を指定します。
最後に５４０行目「rs.Open sql, cn」では、そのSQL文を実行し「KeyTypeBテーブル」を作成します。

この図５－１３で作成されたテーブルの制約情報は、図５－８（Keyオブジェクト）と図５－９（Indexオブジェクト）の様にに保存されています。但し図５－１３は「Key名」を指定しない方法ですので、「Key.Name」や「Index.Name」にはKey1～Key4の様な分かり易いキー名の代わりに「Index_・・・」や「Rel_・・・」等のランダムな名前が付きます。ですので、後からKeyを削除する事が考えられるのであれば、図５－１１のように「Key名」を指定した制約設定の方が良いかもしれません。

５－３．ADOXよる制約解除方法

図５－４で設定したデータベース制約を解除するのが図５－１４です。構文としては以下になります。
　「Catalogオブジェクト.Tables(テーブル名).Keys.Delete　キー名 」

560. '==========　⇩(14)　ADOXよる制約解除　============
561. Sub ADOdeleteKey()
562. 　Const DBname As String = "it-081.accdb"
563. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
564. 　Const TnameA As String = "KeyTypeA"
565. 　Const TnameB As String = "KeyTypeB"
566. 　Dim Cat As Object 　　 '←Catalogオブジェクト
568. 　Set Cat = CreateObject("ADOX.Catalog")
569. 　Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
570. 　　　　　　　　　　　　　　"Data Source=" & DBpath & DBname
572. 　With Cat.Tables(TnameB)
573. 　　.Columns("長いテキスト").Properties("Jet OLEDB:Column Validation Rule") = ""
574. 　　.Columns("長いテキスト").Properties("Jet OLEDB:Column Validation Text") = ""
576. 　　.Keys.Delete "Key4" 　 '← Foreign Keyの解除
577. 　　.Keys.Delete "Key3" 　 '← Primary Keyの解除
578. 　　.Keys.Delete "Key2" 　 '← Uniqueの解除
579. 　End With
581. 　With Cat.Tables(TnameA)
582. 　　.Keys.Delete "Key1" 　 '← Primary Keyの解除
583. 　End With
585. 　Set Cat = Nothing
586. End Sub

図５－１４

５６８行目でCatalogオブジェクトを生成し、５６９～５７０行目でデータベースファイルに接続します。
５７２行目以降で制約を解除するのですが、解除する順番には注意が必要です。図５－３でも分かるように、KeyTypeAとKeyTypeBを結びつけている「Foreign Key」は、KeyTypeBから「KeyTypeAのPrimary Keyである列」につなげています。結んでいる矢印の先は「Primary Key」である必要があります。
ですので、KeyTypeBのForeign Keyを解除する前にKeyTypeAのPrimary Keyを解除してしまうと、エラーが発生してしまいます。

そのため、ここでは設定の時（図５－４）とは逆の順序「KeyTypeB → KeyTypeA」で解除していきます。
まずKeyTypeBです。まず図５－４の４１７～４１８行目で設定したCheck制約相当を解除します。
５７３行目「.Columns("長いテキスト").Properties("Jet OLEDB:Column Validation Rule") = ""」では、「入力規制の式（元は、"<>'abc'"）」を空欄に戻しています。
５７４行目「.Columns("長いテキスト").Properties("Jet OLEDB:Column Validation Text") = ""」では、入力規制に引っ掛かった時に出力する文字列（元は、"違反です"）を空欄にしています。５７４行目はそのままでも問題無いかもしれませんが、入力規制の式とセットと考えて削除しています。

５７６行目「.Keys.Delete "Key4"」、５７７行目「.Keys.Delete "Key3"」、５７８行目「.Keys.Delete "Key2"」と逆の順番に削除しています。今回はKeyTypeB側の削除処理さえ先に行えば問題ありませんが、同じテーブル内での処理順序が関係ある場合も考えてテーブル内でも逆順で処理をしています。
KeyTypeB側の削除処理が終了したら、５８２行目「.Keys.Delete "Key1"」でKeyTypeA側の削除処理をします。

５－４．SQLよる制約解除方法

５－４－１．Key名が既知の場合の制約解除

図５－１１のように「Key名をCONSTRAINTで設定して制約設定」した場合は、図５－１５のような手順で制約解除します。削除するKey名が分かっている訳ですから解除も容易です。

600. '==========　⇩(15)　SQLよる制約解除(Key名を指定して制約設定した場合)　============
601. Sub SQLdeleteKey1()
602. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
603. 　Const DBname As String = "it-081.accdb"
604. 　Const TnameA As String = "KeyTypeA"
605. 　Const TnameB As String = "KeyTypeB"
606. 　Dim sql As String 　　 '←SQL文
607. 　Dim cn As Object 　　 '←Connectionオブジェクト
608. 　Dim rs As Object 　　 '←Recordsetオブジェクト
610. 　Set cn = CreateObject("ADODB.Connection")
611. 　Set rs = CreateObject("ADODB.Recordset")
613. 　cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
614. 　　　　　　"Data Source=" & DBpath & DBname
616. 　sql = "Alter Table " & TnameB & _
617. 　　　　" DROP CONSTRAINT key4 " 　 '← Foreign Keyの解除
618. 　rs.Open sql, cn
620. 　sql = "Alter Table " & TnameB & _
621. 　　　　" DROP CONSTRAINT key3 " 　 '← Primary Keyの解除
622. 　rs.Open sql, cn
624. 　sql = "Alter Table " & TnameB & _
625. 　　　　" DROP CONSTRAINT key2 " 　 '← Uniqueの解除
626. 　rs.Open sql, cn
628. 　sql = "Alter Table " & TnameA & _
629. 　　　　" DROP CONSTRAINT key1 " 　 '← Primary Keyの解除
630. 　rs.Open sql, cn
632. 　cn.Close
634. 　Set rs = Nothing
635. 　Set cn = Nothing
636. End Sub

図５－１５

６１０～６１１行目で、ConnectionオブジェクトとRecordsetオブジェクトを生成し、６１３～６１４行目でデータベースファイルと接続します。
６１６行目以降で、１つずつ制約を解除していきますが、その解除する順序はADOX（図５－１４）の時と同様に「設定とは逆の順番」で行っていきます。

解除の構文は以下の通りです。
　「Alter Table　テーブル名　Drop Constraint　解除するキー名 」
６１６～６１７行目「sql = "Alter Table " & TnameB & " DROP CONSTRAINT key4 " 」ではKeyTypeBテーブル上の制約Key4を削除（Drop）します。６１８行目「rs.Open sql, cn」で制約解除を実行しています。
６２０～６２１行目「sql = "Alter Table " & TnameB & " DROP CONSTRAINT key3 " 」ではKeyTypeBテーブル上の制約Key3を削除します。
６２４～６２５行目「sql = "Alter Table " & TnameB & " DROP CONSTRAINT key2 " 」ではKeyTypeBテーブル上の制約Key2を削除します。

６２８～６２９行目「sql = "Alter Table " & TnameA & " DROP CONSTRAINT key1 " 」では、今度はKeyTypeAテーブル上の制約Key1を削除します。

５－４－２．Key名が未知の場合の制約解除

図５－１３のように「CONSTRAINTを使用せずに制約設定」した場合は「削除するKey名が未知」です。この場合の解除方法として書籍やサイトでは「sql = "Alter Table " & TnameA & " DROP Primary key"」や「sql = "Alter Table " & TnameA & " DROP Unique(数値バイト)"」等と書いてありますが、残念ながら今回は受け付けてくれません（Accessがインストールされている環境であれば別かもしれません）。
そこで今回は「ADOXで未知のKey名を取得し、CONSTRAINTを使って制約を削除」します（図５－１６）。

650. '==========　⇩(16)　Key名を指定せずに制約設定した場合　============
651. Sub SQLdeleteKey2()
652. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
653. 　Const DBname As String = "it-081.accdb"
654. 　Const TnameA As String = "KeyTypeA"
655. 　Const TnameB As String = "KeyTypeB"
656. 　Dim sql As String 　　 '←SQL文
657. 　Dim cn As Object 　　 '←Connectionオブジェクト
658. 　Dim rs As Object 　　 '←Recordsetオブジェクト
659. 　Dim Cat As Object 　　 '←Catalogオブジェクト
660. 　Dim i As Integer 　　 '←カウンタ変数（Keyの数）
661. 　Dim t As Variant 　　 '←テーブル名
663. 　Set Cat = CreateObject("ADOX.Catalog")
665. 　Set cn = CreateObject("ADODB.Connection")
666. 　Set rs = CreateObject("ADODB.Recordset")
668. 　Cat.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
669. 　　　　　　　　　　　　　　"Data Source=" & DBpath & DBname
671. 　cn.Open Cat.ActiveConnection
673. 　For Each t In Array(TnameB, TnameA)
674. 　　With Cat.Tables(t)
675. 　　　For i = .Keys.Count - 1 To 0 Step -1
676. 　　　　sql = "ALTER TABLE " & t & _
677. 　　　　　　　" DROP CONSTRAINT " & .Keys(i).Name
678. 　　　　rs.Open sql, cn
679. 　　　Next i
680. 　　End With
681. 　Next t
683. 　cn.Close
685. 　Set rs = Nothing
686. 　Set cn = Nothing
687. End Sub

図５－１６

６６３行目「Set Cat = CreateObject("ADOX.Catalog")」で、ADOXのCatalogを生成します。このCatalogオブジェクトは「Key名を取得」するために使用します。
６６５～６６６行目で、ConnectionオブジェクトとRecordsetオブジェクトを生成し、６６８～６６９行目でCatalogオブジェクトをデータベースファイルと接続します。 そして６７１行目「cn.Open Cat.ActiveConnection」で、Connectionオブジェクトとしても接続をします。こちらは、SQL文を実行するために使用します。

６７３行目「For Each t In Array(TnameB, TnameA)」では、テーブル名をTnameB → TnameA の順（＝設定時とは逆の順序）に変数tに代入しています。For Eachで呼ばれる順番に保証は無く、もし TnameA → TnameB の順になった場合はエラーが発生してしまいますが、試したところ一応大丈夫な様です。
６７４行目「With Cat.Tables(t)」で、そのテーブル名を基準にして処理を進めます。
６７５行目「For i = .Keys.Count - 1 To 0 Step -1」では、カウンタ変数ｉを「テーブルのKeyの数」の分だけ回します。この時も、設定時とは逆順に回すために Step -1 とし、またKeyのインデックスはゼロ始まりのため１を差し引いています。

６７６～６７７行目「sql = "ALTER TABLE " & t & " DROP CONSTRAINT " & .Keys(i).Name」の最後の「.Keys(i).Name」は、削除対象のKeyの名前（＝Key名）です。設定時にシステムが自動で割り振ったKey名をここで取得します。
あとは構文に従って「Alter Table テーブル名 Drop Constraint 解除するKey名」という形にしています。
SQL文が完成したら、６７８行目「rs.Open sql, cn」で実行し、Keyを削除します。

６．インデックスの作成と削除

データベーステーブルに対してインデックス（索引）を設定することで、データ検索や並べ替えを効率的に行うことが出来るようになります。インデックスの概念としては「テーブル本体とは別に、テーブルのインデックス指定列のデータを順番に並べ、同じ行にテーブル本体へのポインタ（テーブルのどの位置にあるかを示す情報）を記したテーブル」のようなものです。
検索をした列がインデックスに登録してあれば、まずインデックスのテーブルを順番に探し、見つかったらそのポインタを頼りにテーブル本体からデータを取得する、という動作をします。

６－１．インデックスの作成

６－１－１．ADOXによるインデックス作成

ADOXを使ってインデックスを作成するのが図６－１です。

700. '==========　⇩(17)　ADOXによるインデックス作成　============
701. Sub ADOmakeIndex()
702. 　Const DBname As String = "it-081.accdb"
703. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
704. '　Const TnameA As String = "KeyTypeA"
705. 　Const TnameB As String = "KeyTypeB"
706. 　Dim Cat As Object 　　 '←Catalogオブジェクト
707. 　Dim Idx As Object 　　 '←Indexオブジェクト
709. 　Call ADOmakeKey1 　　 '←図５－２を呼出してテーブル作成
711. 　Set Cat = CreateObject("ADOX.Catalog")
712. 　Set Idx = CreateObject("ADOX.Index")
713. 　Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
714. 　　　　　　　　　　　　　　"Data Source=" & DBpath & DBname
716. 　With Idx
717. 　　.Name = "Idx1"
718. 　　.Columns.Append "長いテキスト"
719. 　　.IndexNulls = adIndexNullsDisallow
720. '　　.PrimaryKey = True
721. '　　.Unique = True
722. 　End With
724. 　Cat.Tables(TnameB).Indexes.Append Idx
726. 　Set Cat = Nothing
727. 　Set Idx = Nothing
728. End Sub

図６－１

インデックスを作成するためには、元となるテーブルが必要ですので、７０９行目「Call ADOmakeKey1」で図５－２を呼び出し、KeyTypeAとKeyTypeBの２つのテーブルを作成しておきます。

７１１行目「Set Cat = CreateObject("ADOX.Catalog")」で、Catalogオブジェクトを生成し、７１２行目「Set Idx = CreateObject("ADOX.Index")」ではIndexオブジェクトを生成します。
７１３～７１４行目でデータベースファイルに接続します。

７１６行目「With Idx」以下で、まずインデックスの中身を組み立てています。図４－２１で「データベースに追加する前に、Tableの中身を組み立てる」のと同じ流れになります。
まず７１７行目「.Name = "Idx1"」でインデックスの名前を設定します。ここでは仮に「Idx1」としました。.Nameはもちろんプロパティで、インデックスのプロパティは図６－２のように７つあります。
（Propertiesの下には更に１１個のプロパティがありますが、他サイトでも殆ど情報が無いため今回は割愛します。）

Indexのプロパティ

ﾌﾟﾛﾊﾟﾃｨ	内容
Clustered	ﾃｰﾌﾞﾙのｸﾗｽﾀｰ化ｲﾝﾃﾞｯｸｽを表すか否か
Columns	対象の列
IndexNulls	Null値のﾚｺｰﾄﾞをｲﾝﾃﾞｯｸｽの対象とするか否か
Name	インデックス名
PrimaryKey	主キーか否か
Properties	詳細設定
Unique	一意か否か

図６－２

７１８行目「.Columns.Append "長いテキスト"」で、「長いテキスト」列をインデックスの列に追加します。
７１９行目「.IndexNulls = adIndexNullsDisallow」で、「IndexNullsプロパティ」に値を設定します。このIndexNullsプロパティに入れる値は図５－１０でも紹介しましたが、図６－３として再掲します。なお全く同じ表です。

定数	値	内容
adIndexNullsAllow	0	Null値入力を許可
adIndexNullsDisallow	1	Null値入力を許可しない(既定値)
adIndexNullsIgnore	2	Null値はｲﾝﾃﾞｯｸｽに含めない
adIndexNullsIgnoreAny	4	複数列ｷｰの場合､Null値はｲﾝﾃﾞｯｸｽに含めない

図６－３

７２０行目「.PrimaryKey = True」、７２１行目「.Unique = True」は見え消しにしてありますが、ここで併せて「PrimaryKey」「Unique」の設定も可能です。また図６－３のIndexNullsプロパティの設定により、「Not Null」の設定も可能ということになります。

インデックスの組み立てが完了したら、７２４行目「Cat.Tables(TnameB).Indexes.Append Idx」でテーブルにインデックスを追加します。

６－１－２．SQLによるインデックス作成

SQLでインデックスを作成するには「Create Index」を使用します。

740. '==========　⇩(18)　SQLによるインデックス作成　============
741. Sub SQLmakeIndex()
742. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
743. 　Const DBname As String = "it-081.accdb"
744. 　Const TnameB As String = "KeyTypeB"
745. 　Dim sql As String 　　 '←SQL文
746. 　Dim cn As Object 　　 '←Connectionオブジェクト
747. 　Dim rs As Object 　　 '←Recordsetオブジェクト
749. 　Set cn = CreateObject("ADODB.Connection")
750. 　Set rs = CreateObject("ADODB.Recordset")
752. 　cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
753. 　　　　　　"Data Source=" & DBpath & DBname
755. 　sql = "Create Table " & TnameB & " (" & _
756. 　　　　　　　"短いテキスト ALPHANUMERIC NOT NULL," & _
757. 　　　　　　　"長いテキスト LONGTEXT," & _
758. 　　　　　　　"数値バイト BYTE ," & _
759. 　　　　　　　"数値整数 SHORT," & _
760. 　　　　　　　"数値長整数 LONG" & _
761. 　　　　　　　")"
762. 　rs.Open sql, cn
764. 　sql = "Create Index Idx1 On " & TnameB & " (短いテキスト) " & _
765. 　　　　　　　"With DISALLOW NULL"
766. 　rs.Open sql, cn
768. 　sql = "Create Index Idx2 On " & TnameB & " (長いテキスト)"
769. 　rs.Open sql, cn
771. 　sql = "Create Unique Index Idx3 On " & TnameB & " (数値バイト)"
772. 　rs.Open sql, cn
774. 　sql = "Create Index Idx4 On " & TnameB & " (数値整数)" & _
775. 　　　　　　　"With Primary"
776. 　rs.Open sql, cn
778. 　sql = "Create Index Idx5 On " & TnameB & " (数値長整数)" & _
779. 　　　　　　　"With IGNORE NULL"
780. 　rs.Open sql, cn
782. 　cn.Close
784. 　Set rs = Nothing
785. 　Set cn = Nothing
786. End Sub

図６－４

７４９～７５０行目で、ConnectionオブジェクトとRecordsetオブジェクトを生成し、７５２～７５３行目でデータベースファイルと接続します。７５５～７６２行目では、５列のKeyTypeBテーブルを作成します。そして作ったテーブルKeyTypeBを元にして、７６４行目以降でインデックスを作成します。

Create Indexの構文は以下の通りです。
　①基　本　形「Create Index インデックス名 On テーブル名 (列名)」
　②一意併用形「Create Unique Index インデックス名 On テーブル名 (列名)」
　③制約併用形「Create Index インデックス名 On テーブル名 (列名) With 制約」

７６４～７６５行目「sql = "Create Index Idx1 On " & TnameB & " (短いテキスト) " & "With DISALLOW NULL"」は③の制約併用形です。「短いテキスト」列に対してインデックスを作成し、且つその列はNull値の挿入を禁止します。つまり「Not Null」を設定したのと同等になります。
With句の後に続けられるのは、図６－５の通り３種類です。

With句 ～	内容
DISALLOW NULL	Null値の挿入を禁止
IGNORE NULL	Null値のﾚｺｰﾄﾞはｲﾝﾃﾞｯｸｽに含めない
PRIMARY	主キーとして指定

図６－５

７６８行目「sql = "Create Index Idx2 On " & TnameB & " (長いテキスト)"」は、①の基本形で、「長いテキスト」列に対してインデックスを作成しています。
７７１行目「sql = "Create Unique Index Idx3 On " & TnameB & " (数値バイト)"」は、②の一意併用形で、「数値バイト」列に対してインデックスを作成し、且つその列をUnique制約にしています。

７７４～７７５行目「sql = "Create Index Idx4 On " & TnameB & " (数値整数)" & "With Primary"」は③制約併用形で、「数値整数」列に対してインデックスを作成し、その列をPrimary Keyにします。
７７８～７７９行目「sql = "Create Index Idx5 On " & TnameB & " (数値長整数)" & "With IGNORE NULL"」も③制約併用形で、「数値長整数」列に対してインデックスを作成し、その列はNull挿入は許すものの、インデックスにはNull値を含めないことになります。

６－２．インデックスの削除

６－２－１．ADOXによるインデックス削除

ADOXを使ってインデックスを削除するには、以下の構文を使用します。
　「Catalogオブジェクト.Tables(テーブル名).Indexes.Delete　インデックス名」
図６－６は、図６－１を実行してインデックス付きのテーブルを作成した後、そのインデックスを削除しています。

790. '==========　⇩(19)　ADOXによるインデックス削除　============
791. Sub ADOdeleteIndex()
792. 　Const DBname As String = "it-081.accdb"
793. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
794. 　Const TnameB As String = "KeyTypeB"
795. 　Dim Cat As Object
797. 　Call ADOmakeIndex 　 '←図６－１でインデックス付きテーブルを作成
798. 　
799. 　Set Cat = CreateObject("ADOX.Catalog")
800. 　Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
801. 　　　　　　　　　　　　　　"Data Source=" & DBpath & DBname
803. 　Cat.Tables(TnameB).Indexes.Delete "Idx1"
805. 　Set Cat = Nothing
806. End Sub

図６－６

７９７行目「Call ADOmakeIndex」で図６－１を呼び出し、インデックス付きテーブルを作成します。
７９９行目「Set Cat = CreateObject("ADOX.Catalog")」でCatalogオブジェクトを生成し、８００～８０１行目「Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & DBpath & DBname」でデータベースファイルに接続します。

８０３行目「Cat.Tables(TnameB).Indexes.Delete "Idx1"」で、KeyTypeBテーブルのインデックス「Idx1（長いテキスト列に対して作成したインデックス名）」を削除します。

６－２－２．SQLによるインデックス削除

SQLを使ってインデックスを削除するには、以下の構文を使用します。
　「Drop Index　インデックス名　On　テーブル名」
図６－７は、図６－４を実行してインデックス付きのテーブルを作成した後、そのインデックスを削除しています。

810. '==========　⇩(20)　SQLによるインデックス削除　============
811. Sub SQLdeleteIndex()
812. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
813. 　Const DBname As String = "it-081.accdb"
814. 　Const TnameB As String = "KeyTypeB"
815. 　Dim sql As String 　　 '←SQL文
816. 　Dim cn As Object 　　 '←Connectionオブジェクト
817. 　Dim rs As Object 　　 '←Recordsetオブジェクト
818. 　Dim i As Integer 　　 '←カウンタ変数(インデックスの数)
820. 　Call SQLmakeIndex 　　 '←図６－４でインデックス付きテーブルを作成
822. 　Set cn = CreateObject("ADODB.Connection")
823. 　Set rs = CreateObject("ADODB.Recordset")
825. 　cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
826. 　　　　　　"Data Source=" & DBpath & DBname
828. 　For i = 5 To 1 Step -1
829. 　　sql = "Drop Index Idx" & i & " On " & TnameB
830. 　　rs.Open sql, cn
831. 　Next i
833. 　cn.Close
835. 　Set rs = Nothing
836. 　Set cn = Nothing
837. End Sub

図６－７

８２０行目「Call SQLmakeIndex」で図６－４を呼出し、インデックス付きのテーブルを作成します。作られたインデックスは５個で、名前を Idx1～Idx5 としています。
８２２～８２３行目でConnectionオブジェクトとRecordsetオブジェクトを生成し、８２５～８２６行目でデータベースファイルに接続しています。

８２８行目「For i = 5 To 1 Step -1」で、カウンタ変数ｉを５→１へ降順で回し、１つずつインデックスを削除しています。これは「作った順番と逆の順番でインデックスを削除」するためです。
８２９行目「sql = "Drop Index Idx" & i & " On " & TnameB」でSQL文を作成します。インデックス名はIdx1～Idx5ですのでカウンタ変数ｉを利用してインデックス名にしています。今回、インデックスを作成したテーブルはKeyTypeBのみのため、「TnameB」で固定しています。
８３０行目「rs.Open sql, cn」でインデックス削除実行しています。

６－３．制約（Key）とインデックスの関係

図２－２で示したADOXオブジェクトモデルの中で、Key（制約）とIndex（索引）は、かなり関係しているように思います。例えばKeyのPrimary Key設定をすれば、テーブル内のKeyオブジェクトのプロパティ（図５－８）に記録されるだけで無く、Indexオブジェクトのプロパティ（図５－９）にも記録されます。逆にIndex設定時に併行してUnique設定すれば、Indexオブジェクトのプロパティだけで無くKeyオブジェクトのプロパティにも記録されます。
ここで制約（Key）とインデックスの関係を整理してみたのが下記の表です。図６－８は、制約（Key）を設定した時に、対象列のプロパティを変更するか否か、またテーブルのKeyプロパティ、Indexプロパティに記録されるか否かを示したものです。

制約	列ﾌﾟﾛﾊﾟﾃｨ	Key	Index
Check	〇
Not Null	〇
Unique		〇	〇
Primary Key		〇	〇
Foreign Key		〇	〇

図６－８

AccessにはCheck制約が無いので、テーブルのKeyやIndexには記録されません。しかし、ADOXを使って列プロパティの「Jet OLEDB:Column Validation Rule」に式を記入すれば同等の機能となります。
またNot Null制約については、ADOXを使用した場合はKeys.Appendには対応する定数がありませんので、列のプロパティを設定するしかありません。SQLを使った場合も図５－１２で説明した通り、ADD CONSTRAINTに「Not Null」を指定することは（Excel VBAからは）不可能なために、列設定時にNot Nullを指定することで、列のプロパティを設定するしかありません。

一方で図６－９は、インデックス（Index）を設定した時に、テーブルのIndexプロパティ、Keyプロパティに記録されるか否かを示したものです。

インデックス		列ﾌﾟﾛﾊﾟﾃｨ	Index	Key
IndexNulls	Allow		〇
	DisAllow		〇
	Ignore		〇
	IgnoreAny		〇
Unique			〇	〇
Primary Key			〇	〇

図６－９

この表の通り、インデックスを設定したものは全てテーブルのIndexプロパティに記録されます。しかしNull関連はKeyプロパティには記録されません。言い換えれば、Not Nullを指定するにはインデックスから行えば良い、と言えるかもしれません。

なお、削除する時は同じオブジェクトの筋で削除した方が良さそうです。制約を設定したらKeyを削除、インデックスを設定したらインデックスを削除という具合です。
例えば、Foreign Keyを設定するとIndexプロパティにも記録されますが、それをIndexで削除するとエラーが出てしまいます。

７．テーブル構造の確認

作ったテーブルの列名とデータ型を確認するのが図７－１です。このプロシージャでは「テーブル名」「列名／データ型」のみを表示させていますが、必要なプロパティ、KeyやIndex値なども必要であれば値を取得し表示する事も可能です。

850. '==========　⇩(21)　テーブル構造の確認　============
851. Sub getTable()
852. 　Const DBname As String = "it-081.accdb"
853. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
854. 　Dim Cat As Object 　　　 '←Catalogオブジェクト
855. 　Dim TBL As Object 　　　 '←Tableオブジェクト
856. 　Dim str As String 　　　 '←MsgBoxに表示する文字列
857. 　Dim i As Long 　　　　　 '←カウンタ変数（列の数）
859. 　Set Cat = CreateObject("ADOX.Catalog")
860. 　Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
861. 　　　　　　　　　　　　　"Data Source=" & DBpath & DBname
863. 　For Each TBL In Cat.Tables
864. 　　If TBL.Type = "TABLE" Then
865. 　　　str = TBL.Name & vbLf & vbLf
866. 　　　For i = 0 To TBL.Columns.Count - 1
867. 　　　　str = str & TBL.Columns(i).Name & " / " & TBL.Columns(i).Type & vbLf
868. 　　　Next i
870. 　　　MsgBox str
871. 　　End If
872. 　Next TBL
874. 　Set Cat = Nothing
875. End Sub

図７－１

図７－１を実行すると、例えば図７－２のようなメッセージボックスが表示されます。複数のテーブルが存在すれば、テーブルの数だけ表示されることになります。
表示は「テーブル名」と「列名／データ型」になり、データ型は図４－１で示したADOのデータ型の値になります。

図７－２

８５９行目「Set Cat = CreateObject("ADOX.Catalog")」で、Catalogオブジェクトを生成し、８６０行目でデータベースファイルに接続します。

８６３行目「For Each TBL In Cat.Tables」で、データベースファイル内の全てのテーブルを走査します。テーブルには、ユーザーが作るデータを入れるためのテーブル（TABLE）以外に、システムテーブル（SYSTEM TABLE）などがあります。
８６４行目「If TBL.Type = "TABLE" Then」では、その走査したテーブルが「データを入れるテーブルか否か」を調べています。大文字小文字は別扱いですので、「大文字半角」で "TABLE" とします。本当は「TABLE」という文字列を戻してくれる関数があれば良いのですが見当たりませんでした。

メッセージボックスには変数strを文字列として表示しますので、まず８６５行目「str = TBL.Name & vbLf & vbLf」で、テーブル名を変数strに代入します。その後に「vbLf」を２つ入れることでテーブル名と列情報の間を空けています。
８６６行目「For i = 0 To TBL.Columns.Count - 1」でカウンタ変数ｉをそのテーブルの列数分だけ回します。
８６７行目「str = str & TBL.Columns(i).Name & " / " & TBL.Columns(i).Type & vbLf」では、列名「TBL.Columns(i).Name」と列のデータ型「TBL.Columns(i).Type」を変数strに加えていきます。

全ての列情報を変数strに書き込んだら、８７０行目「MsgBox str」でメッセージを表示します。
１つのテーブルの表示が終わったら、８６３行目のFor Eachで次のテーブルを走査します。

８．データの操作

テーブルの準備が出来たらデータの出し入れの段階になりますが、この操作は「SQLを使用する」しかありません。データ操作をするSQLの命令文は基本的に４つです。
　テーブルからデータを検索し、その結果を戻す「 Select 」
　テーブルに新たにデータ行を挿入する「 Insert 」
　既存のデータ行（レコード）を更新する「 Update 」
　データ行を削除する「 Delete 」

８－１．データの挿入・更新・削除

テーブル上のデータに変化を与えるのが「Insert」「Update」「Delete」です。図８－１ではその３種を実行しています。

880. '==========　⇩(22)　データの挿入・更新・削除　========
881. Sub execData()
882. 　Const DBname As String = "it-081.accdb"
883. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
884. 　Const Tname As String = "AllDataType"
885. 　Dim cn As Object 　　　　　　 '←Connectionオブジェクト
886. 　Dim sql(1 To 4) As String 　　 '←SQL文(4種)
887. 　Dim i As Integer 　　　　　　 '←カウンタ変数(列数)
889. 　Call ADOmakeTable 　　　　　　 '←図４－２１でテーブル作成
891. 　Set cn = CreateObject("ADODB.Connection")
893. 　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
894. 　cn.Open DBpath & DBname
896. 　sql(1) = "insert into " & Tname & _
897. 　　　　　　" (短いテキスト,長いテキスト,数値整数)" & " values('abc','def',123)"
899. 　sql(2) = "insert into " & Tname & _
900. 　　　　　　" (短いテキスト,長いテキスト,数値整数)" & " values('ghi','jkm',45)"
902. 　sql(3) = "update " & Tname & _
903. 　　　　　　" set 短いテキスト='ghi' where 数値整数=123"
905. 　sql(4) = "delete from " & Tname & _
906. 　　　　　　" where 数値整数=123"
908. 　For i = 1 To 4
909. 　　On Error Resume Next
910. 　　　cn.Execute sql(i)
911. 　　　If Not Err.Number = 0 Then MsgBox Err.Description
912. 　　On Error GoTo 0
913. 　Next i
915. 　cn.Close
917. 　Set cn = Nothing
918. End Sub

図８－１

８８９行目「Call ADOmakeTable」では図４－２１を呼出し、一応全てのデータ型を持つテーブルを作成します。
８９１行目「Set cn = CreateObject("ADODB.Connection")」でConnectionオブジェクトを生成し、８９３行目「cn.Provider = "Microsoft.ACE.OLEDB.12.0"」でデータベースに接続するプロバイダをACE系にし、８９４行目「cn.Open DBpath & DBname」でデータベースファイルに接続します。

８９６～９０６行目ではSQL文を作成しています。今回は「Insert（挿入）文」を２つ、「Update（更新）文」「Delete（削除）文」を各１つ作り、８８６行目「Dim sql(1 To 4) As String」で宣言した配列にそのSQL文を代入しています。
それぞれの基本的な構文を示します。
　挿入： Insert into テーブル名(列名1,列名2,･･･) values(挿入データ1,挿入データ2,･･･)
　更新： Update テーブル名 set 列名1 = データ1,列名2 = データ2,･･･ Where 更新する条件式
　削除： Delete from テーブル名 Where 削除する条件式

８９６～８９７行目「sql(1) = "insert into " & Tname & " (短いテキスト,長いテキスト,数値整数)" & " values('abc','def',123)"」は、３つの列にデータを挿入するSQL文です。残りの列はNullとなるのですが、オートナンバー型・はい／いいえ型は図４－１７、図４－１８で説明した様に「Nullにはならず、オートナンバー型には連番が、はい／いいえ型にはいいえ（False）」が入ることになります。
８９９～９００行目「sql(2) = "insert into " & Tname & " (短いテキスト,長いテキスト,数値整数)" & " values('ghi','jkm',45)"」も異なるデータが挿入されます。

９０２～９０３行目「sql(3) = "update " & Tname & " set 短いテキスト='ghi' where 数値整数=123"」は、数値整数列が123のレコードに対して、短いテキスト列のデータを更新しています。Where句を付け忘れたり、常に成立するようなWhere句（例：Where 1 = 1 ）にすると、全データを更新してしまうことになりますので注意が必要です。

９０５～９０６行目「sql(4) = "delete from " & Tname & " where 数値整数=123"」は、数値整数列が123のレコードを削除しています。Where句を忘れたり常に成立するようなWhere句だと全データが削除されます。

９０８～９１３行目では、上記で作成した４つのSQL文を順番に実行しています。
９０８行目「For i = 1 To 4」では、カウンタ変数ｉを１～４まで変化させ、９１０行目「cn.Execute sql(i)」でSQL文を１つずつ実行していきます。しかし、接続が切れた・SQL文が間違っていた等の理由で実行できなかった場合にはエラーが出てしまいますので、９０９行目「On Error Resume Next」でエラーをスルーさせ、９１１行目「If Not Err.Number = 0 Then MsgBox Err.Description」でエラー内容「Err.Description」を表示させます。

なお、ここではSQL文を実行するのに「cn.Execute SQL文」という構文を使っていますが、もちろんRecordsetオブジェクトを生成して「rs.Open SQL文,cn」としてもOKです。

８－２．データ取得

データを取り出すのが「Select」です。データ取得しワークシート上にデータ一覧を貼り付けるのが図８－２です。Select文にはRecordsetオブジェクトは必ず必要となります。

930. '==========　⇩(23)　データ取得と表示　============
931. Sub selectData()
932. 　Const DBname As String = "it-081.accdb"
933. 　Dim DBpath As String: DBpath = ThisWorkbook.Path & "¥"
934. 　Const Tname As String = "AllDataType"
935. 　Dim cn As Object 　　　 '←Connectionオブジェクト
936. 　Dim rs As Object 　　　 '←Recordsetオブジェクト
937. 　Dim sql As String 　　　 '←SQL文
938. 　Dim i As Integer 　　　　 '←カウンタ変数(列数)
940. 　Set cn = CreateObject("ADODB.Connection")
941. 　Set rs = CreateObject("ADODB.Recordset")
943. 　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
944. 　cn.Open DBpath & DBname
946. 　sql = "SELECT * FROM " & Tname
947. 　rs.Open sql, cn
949. 　Sheet1.UsedRange.ClearContents
951. 　For i = 1 To rs.Fields.Count
952. 　　Sheet1.Cells(1, i) = rs.Fields(i - 1).Name
953. 　Next i
955. 　Sheet1.Cells(2, 1).CopyFromRecordset rs
957. 　rs.Close
958. 　cn.Close
960. 　Set rs = Nothing
961. 　Set cn = Nothing
962. End Sub

図８－２

９４０行目「Set cn = CreateObject("ADODB.Connection")」でConnectionオブジェクトを生成し、９４１行目「Set rs = CreateObject("ADODB.Recordset")」でRecordsetオブジェクトを生成します。
９４３行目「cn.Provider = "Microsoft.ACE.OLEDB.12.0"」で、Connectionオブジェクトにデータベース接続プロバイダをセットし、９４４行目「cn.Open DBpath & DBname」でデータベースファイルに接続します。
９４３～９４４行目を１行にまとめて「cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & データベースファイル」としてもOKです。

９４６行目「sql = "SELECT * FROM " & Tname」ではSQL文を作成します。Select文の基本構文は以下になります。
　選択： Select 検索する列名1,検索する列名2,･･･ from テーブル名 Where 検索する条件式 ここでは「全ての列」という意味の「*（アスタリスク）」を使っています。またWhere句を付けていないため「全てのレコード」を選択することになります。
９４７行目「rs.Open sql, cn」でそのSQL文を実行し、結果はRecordsetオブジェクト（変数rs）に戻されます。

９４９行目「Sheet1.UsedRange.ClearContents」では、データ一覧を貼り付けるSheet1をクリアしています。

９５１～９５３行目では、データの列名をワークシート上に書き込んでいます。SQL文を実行して取得するRecordsetには、もちろんデータだけでなくその他の情報も入っていますが、単純にワークシート上に貼り付ける（９５５行目）と「データのみ」が貼り付きます。ですので、列名を１つずつ取り出し、１つずつワークシート上に貼っていきます。
９５１行目「For i = 1 To rs.Fields.Count」で、カウンタ変数ｉを列数分だけ回します。そして９５２行目「Sheet1.Cells(1, i) = rs.Fields(i - 1).Name」で列の名前「rs.Fields(i - 1).Name」を取り出し、ワークシート上に順番に貼っていきます。RecordsetのFields（列）のインデックスはゼロ始まりのため、－１しています。

列名が貼り終わったら、９５５行目「Sheet1.Cells(2, 1).CopyFromRecordset rs」でCopyFromRecordsetメソッドを使ってRecordsetオブジェクトの内容（データのみ）を列名の下に貼り付けます。
なおCopyFromRecordsetメソッドの第一引数にデータの入ったRecordsetオブジェクトを指定していることになるのですが、第二引数「貼り付けるレコード数」、第三引数「貼り付ける列数」を指定可能です。これを使用すると、Excelの固定した範囲内に収まるようにデータを貼り付けることも可能です。

最後に９５７行目「rs.Close」でRecordsetを閉じ、９５８行目「cn.Close」で接続を閉じます。

９．最後に

昔、Accessでは無かったですがデータベースを使ったシステムを作った際、それまで使っていた旧システムのデータも（古いデータも見たいだろうと思って）データベース化したものですから、稼働開始時であるにも関わらずデータ量が100万レコードにもなってしまいました。
テーブルは４列だったのですが、そのシステムの重いこと重いこと。これでは使ってもらえないと思い、その時に初めてインデックスにトライしました。
どの列をインデックスにすれば良いのか知識も無く、設定をしては実行速度を確かめるのトライ＆エラーでした。インデックスをやたらに付けても逆効果になることも分かり、最終的には桁２つ分くらい速くすることが出来ました。

システム使用開始時からお世話になるのは珍しいかもしれませんが、データが増えて来た時には必須のアイテムだと思いますし、そのインデックスをうまく働かせられるか否かが、アプリをユーザーが使い続けてくれるかの分かれ道になるかもしれません。


ExcelからAccessデータベースを作成・操作（it-081.xlsm）

この「サンプルファイルit-081.xlsm 」内の各プロシージャは、Accessデータベースの作成・操作を１つ１つ説明したもので、基本的には繋がりは薄いです。ですので、プロシージャの実行順序によっては「テーブルは既に存在します」などのエラーが出ることがありますのでご了承下さい。 プロシージャ操作を簡単にするため、シート上にプロシージャを呼び出すボタンを配置しましたが、そのボタンの操作でも同様です。一旦データベースファイルを更新（削除→再度作成）すれば、エラーは少なくなると思います。

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始） 解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。