【ADO / VBA】SQL文でAccess DBのテーブルを作成・削除する方法

はじめに

SQLを使用してAccessデータベースのテーブルを作成・削除する方法についてまとめておきます。
使用する言語はVBAです。

＜目次＞
1. テーブルを作成する（CREATE TABLE文）
　1-1. テーブルを作成するソースコード
　1-2. SQL文について
　1-3. Accessデータ型と対応するSQLデータ型
2. テーブルのデータを削除する（DELETE文）
　2-1. テーブルのデータを削除するソースコード
　2-2. SQL文について
3. テーブル自体を削除する（DROP TABLE文）
　3-1. テーブル自体を削除するソースコード
　3-2. SQL文について
4. 同名のテーブルが存在するかどうかを確認してテーブルを削除する方法（参考）
　4-1. ADOXの使用
　4-2. ソースコード例

コードが書きやすいように、先にADOの参照設定をしておきます（参照設定をしない方法は、こちらを参考にしてください）。
VBEの参照設定の画面で、次のようにMicrosoft ActiveX Data Object X.X LibraryをチェックしてOKボタンを押せば完了です

1. テーブルを作成する（CREATE TABLE文）

まず、CREATE TABLE文を使用してテーブルを作成する方法について、基本的なところを書いておきます。

1-1. テーブルを作成するソースコード

テーブルを作成する基本的なコードは次のとおりです。
最低限の記載ですが、これでテーブルの作成ができます。
同名のテーブルが既にあるとエラーが起きますが、この点については後述します。

1-1-1. RunSQLメソッドを使用したソースコード例

DoCmdオブジェクトのRunSQLメソッドを使用する場合は次のように書いてSQL文を実行します。

'テーブルを作成する
Sub CreateTableByDoCmd()
    DoCmd.RunSQL "CREATE TABLE 商品リスト(商品ID LONG PRIMARY KEY, 商品名 TEXT(20), 単価 CURRENCY)"
End Sub

1-1-2. ADOを使用したソースコード例

ADOで接続する場合は次のようにしてSQL文を実行します。

'テーブルを作成する
Sub CreateTableTest()
    Dim cn As New ADODB.Connection 'Connectionオブジェクトのインスタンスを生成
    Set cn = CurrentProject.Connection '現在のAccessDBに接続
    
    Dim cm As New ADODB.Command 'Commandオブジェクトのインスタンスを生成
    cm.ActiveConnection = cn 'ActiveConnectionプロパティで接続の関連付けを行う
    cm.CommandText = "CREATE TABLE 商品リスト(商品ID LONG PRIMARY KEY, 商品名 TEXT(20), 単価 CURRENCY)" 'CommandTextプロパティにSQL文をセット
    cm.Execute 'ExecuteメソッドでSQL文を実行
    
    Set cm = Nothing 'Commandオブジェクトの解放
    cn.Close: Set cn = Nothing 'Connectionオブジェクトの解放
End Sub

AccessでADOを使用するのは迂遠なようにも見えますが、Recordsetオブジェクトを使用してデータ処理ができるので、普段はADOを使用することが多いです（私の場合）。

＜他のアプリケーションからADOを使用する場合＞
Excelなどの他のアプリケーションからADOで接続する場合は、最初の2行のConnectionオブジェクトの設定を次のように書き換えます。

    Dim cn As New ADODB.Connection 'Connectionオブジェクトのインスタンスを生成
    cn.Provider = "Microsoft.ACE.OLEDB.12.0" 'プロバイダの指定（Office2007以降はこの指定）
    cn.Open "C:\VBA\ADOSample.accdb" 'Accessファイルのフルパスを指定して開く

1-1-3. 作成されたテーブルの確認

作成されたテーブルは、すぐにAccessのナビゲーションウィンドウには反映されませんのでご注意ください（ファイルを開き直すなどすれば見れます）。

テーブルのデザインビューを見てみると、次のようになっています。

商品IDを主キー（PRIMARY KEY）に設定しているので、鍵のマークが付いています。
なお、プロパティを見ると、値要求が「はい」、インデックスが「はい（重複なし）」となっています。
この点は、「1-2-2. PRIMARY KEY制約（主キーの設定）」で説明します。

1-1-4. コードの説明

以下は、ADOに関する説明になります。
まず、**Connectionオブジェクトを使用して、現在のDBとの接続（CurrentProject.Connection）を取得します。
次に、Command オブジェクト**を使用して、次の操作を実行します。
①ActiveConnectionプロパティを使用して、現在DBとの接続（cn）を関連付ける。
②CommandTextプロパティを使用して、コマンド文字列（SQL文）をセットする。
③Executeメソッドを使用して、SQL文を実行する。

最後に、使用したオブジェクトを解放して処理を終了します。

＜テーブル作成に関する参考サイト＞
・テーブルの作成（アプリケーションとしてのVBA）
・クエリからSQL（CREATE TABLE文）を実行してAccessのテーブルを作成する

1-2. SQL文について

上記のソースコードで使用したものを中心に、Accessデータベースで使用できる主なSQLの構文と使用方法を列挙しておきます。

1-2-1. CREATE TABLE文

SQLでテーブルを作成するときは、CREATE TABLE構文を使用します。

CREATE_TABLE構文

＜構文＞
CREATE TABLE テーブル名(カラム名 データ型, …… );

＜記載例＞
CREATE TABLE 商品リスト(商品ID LONG, 商品名 TEXT(20), 単価 CURRENCY);

上記の＜記載例＞では、テーブル名を「商品リスト」と指定して、カラム名とデータ型のセットとして次の3つを指定しているということになります。

カラム名	データ型	データ型の説明
商品ID	LONG	長整数型
商品名	TEXT(20)	文字列型（上限20文字）
単価	CURRENCY	通貨型

なお、商品IDのデータ型はLONGと指定していますが、これをCOUNTERと指定すると「オートナンバー型」で作成されます（データ型の詳細については後述）。

CREATE TABLE構文については、MySQLの例ですが「テーブルを作成する(CREATE TABLE文)」というサイトが参考になります。

1-2-2. PRIMARY KEY制約（主キーの設定）

テーブル作成時に主キーを設定する場合は、カラム名、データ型の後ろに、PRIMARY KEYと記載します（主キーの指定がなくともテーブルは作成可能です）。

PRIMARY_KEY制約

＜構文＞
CREATE TABLE テーブル名(カラム名 データ型 PRIMARY KEY, …… );

＜記載例＞
CREATE TABLE 商品リスト(商品ID LONG PRIMARY KEY, 商品名 TEXT(20), 単価 CURRENCY);

なお、Accessの場合は、主キーに設定すると、自動的に、値要求（NOT NULL制約）が「はい」、インデックス（INDEX）が「はい」、ユニーク制約（UNIQUE制約）が「重複なし」と設定されます。
NOT NULL制約とUNIQUE制約は主キー（PRIMARY KEY）の必要条件なので、特に違和感はないと思います。

1-2-3. NOT NULL制約（値要求）

テーブル作成時にNOT NULL制約（値要求）を設定する場合は、カラム名、データ型の後ろに、NOT NULLと記載します。

NOT_NULL制約

＜構文＞
CREATE TABLE テーブル名(カラム名 データ型 NOT NULL, …… );

＜記載例＞ 商品IDにNOT NULL制約を設定
CREATE TABLE 商品リスト(商品ID LONG NOT NULL, 商品名 TEXT(20), 単価 CURRENCY);

1-2-4. UNIQUE制約（一意性制約）

テーブル作成時にUNIQUE制約（一意性制約）を設定する場合は、カラム名、データ型の後ろに、UNIQUEと記載します。

UNIQUE制約

＜構文＞
CREATE TABLE テーブル名(カラム名 データ型 UNIQUE, …… );

＜記載例1＞ 商品IDにUNIQUE制約を設定
CREATE TABLE 商品リスト(商品ID LONG UNIQUE, 商品名 TEXT(20), 単価 CURRENCY);

＜記載例2＞ さらにNOT NULL制約も付加する場合
CREATE TABLE 商品リスト(商品ID LONG NOT NULL UNIQUE, 商品名 TEXT(20), 単価 CURRENCY);

1-2-5. DEFAULT制約（規定値の設定）

テーブル作成時にデフォルト値（規定値）を設定する場合は、カラム名、データ型の後ろに、DEFAULT デフォルト値と記載します。

UNIQUE制約

＜構文＞
CREATE TABLE テーブル名(カラム名 データ型 DEFAULT デフォルト値, …… );

＜記載例1＞ 単価にデフォルト値（数値）を設定
CREATE TABLE 商品リスト(商品ID LONG, 商品名 TEXT(20), 単価 CURRENCY DEFAULT 0);

＜記載例2＞ 倉庫区分にデフォルト値（文字列）を設定
CREATE TABLE 商品リスト(商品ID COUNTER, 倉庫区分 TEXT(10) DEFAULT 東京, 商品名 TEXT(20), 単価 CURRENCY);

1-2-6. FOREIGN KEY制約（外部キー制約）

外部キーを持ったカラムを設定する場合は、カラム名、データ型の後ろに、CONSTRAINT 外部キー名 REFERENCES 親テーブル名 (親カラム名)と記載します（参照：Access SQLを使用してテーブル間のリレーションシップを定義する）。

FOREIGN_KEY制約構文

CREATE TABLE テーブル名(カラム名 データ型 CONSTRAINT 外部キー名 REFERENCES 親テーブル名 (親カラム名), …… );

＜ソースコード例＞

'外部キーのあるテーブルを作成する
Sub CreateTableFOREIGNKEYTest()
    Dim cn As New ADODB.Connection 'Connectionオブジェクトのインスタンスを生成
    Set cn = CurrentProject.Connection '現在のAccessDBに接続
    
    Dim cm As New ADODB.Command 'Commandオブジェクトのインスタンスを生成
    cm.ActiveConnection = cn 'ActiveConnectionプロパティで接続の関連付けを行う
    cm.CommandText = "CREATE TABLE 販売リスト(販売ID COUNTER PRIMARY KEY, 商品ID LONG CONSTRAINT FK_商品名 REFERENCES 商品リスト (商品ID), 購入数 LONG)" 'CommandTextプロパティにSQL文をセット
    cm.Execute 'ExecuteメソッドでSQL文を実行
    
    Set cm = Nothing 'Commandオブジェクトの解放
    cn.Close: Set cn = Nothing 'Connectionオブジェクトの解放
End Sub

実行してリレーションシップを確認すると、一対多の関係で結合されていることが分かります。

この外部キー制約は、MySQLの場合などとは、結構異なる書き方になっていますのでご注意ください。

1-2-7. INDEX（インデックスの作成）

調べた範囲では、Access SQLでは「CREATE TABLE文中でインデックスを設定する方法」が見つかりませんでした（MySQLは可能です）。
インデックスの作成を実現するには、既に作成されたテーブルに、インデックスを追加する方法を使用します（Access SQLを使用してテーブルとインデックスを作成および削除する）。

INDEX作成構文

CREATE INDEX インデックス名 ON テーブル名(カラム名):

＜ソースコード例＞

Sub CreateIndexTest()
    Dim cn As New ADODB.Connection 'Connectionオブジェクトのインスタンスを生成
    Set cn = CurrentProject.Connection '現在のAccessDBに接続
    
    Dim cm As New ADODB.Command 'Commandオブジェクトのインスタンスを生成
    cm.ActiveConnection = cn 'ActiveConnectionプロパティで接続の関連付けを行う
    cm.CommandText = "CREATE INDEX IDX商品名 ON 商品リスト(商品名)" 'CREATE INDEX構文でインデックスを作成
    cm.Execute 'ExecuteメソッドでSQL文を実行
    
    Set cm = Nothing 'Commandオブジェクトの解放
    cn.Close: Set cn = Nothing 'Connectionオブジェクトの解放
End Sub

CREATE TABLE文を実行した後に、続けてCREATE INDEX文を実行すれば、テーブルの生成時にインデックスをつけることができます。

1-3. Accessデータ型と対応するSQLデータ型

ADOを使用してCREATE TABLE文を書くときに気になるのが、データ型の種類です。
わかりやすい一覧が見当たらなかったので、いくつかのサイトを参考にしつつ、実際に確認した内容を表にしました。

Accessデータ型	英語データ型	SQL表記	扱える範囲	サイズ
短いテキスト	Short Text	TEXT(0-255), VARCHAR, VARCHAR(0-255), CHAR, CHAR(0-255)	最大255文字	最大256byte
長いテキスト	Long Text	TEXT, LONGTEXT, MEMO	最大65,535文字程度	-
単精度浮動小数点型	Single	SINGLE	有効桁数7桁の実数（おおよそ）	4byte
倍精度浮動小数点型	Double	DOUBLE, FLOAT	有効桁数15桁の実数（おおよそ）	8byte
バイト型	Byte	BYTE, TINYINT	0 ～ 255	1byte
整数型	Integer	SHORT	-32,768 ～ 32,767	2byte
長整数型	Long Integer	LONG, INT, INTEGER	-2,147,483,648 ～ 2,147,483,647	4byte
十進型	Decimal	DECIMAL(1-28,0-28), DECIMAL, NUMERIC	10進数で最大28桁の整数	最大12byte
大きい数値	Large Number	（対応するSQL文は不明）	-9,223,372,036,854,775,808 ～ 9,223,372,036,854,775,807	8byte
日付/時刻型	Date/Time	DATETIME, DATE	100-01-01 00:00:00 ～ 9999-12-31 23:59:59.999	8byte
拡張した日付/時刻	Date/Time Extended	（対応するSQL文は不明）	0001-01-01 00:00:00 ～ 9999-12-31 23:59:59.9999999	42byteらしい
通貨型	Currency	CURRENCY	-922,337,203,685,477.5808 ～ 922,337,203,685,477.5807	8byte
オートナンバー型	AutoNumber	COUNTER	-2,147,483,648 ～ 2,147,483,647	4byte
Yes/No型	Yes/No	BIT	True(1) / False(0)	1bit
OLE オブジェクト型	OLE Object	LONGBINARY	最大1ギガバイト	-
バイナリ型	Binary	BINARY(0-510), BINARY, VARBINARY, VARBINARY(0-510)	最大510byte（255文字）	最大512byte

短いテキスト（Short Text）の補足
短いテキストは**TEXT(最大文字数)**で指定します。
指定できるのは、TEXT(1)～TEXT(255)の範囲です。0文字の指定はできず、TEXT(0)とするとTEXT(255)が設定されます。
TEXT(最大文字数)の部分を、VARCHAR(最大文字数)又はCHAR(最大文字数)と置き換えても同様の結果となります。
なお、データは可変長で格納されますので、CHARと記載しても実際はVARCHAR型の指定をしているのと同じになります。

長いテキスト（Long Text）の補足
長いテキストは**TEXT**で指定します。文字数の指定はできません。
TEXTの部分を、LONGTEXT又はMEMOと置き換えても同様の結果となります。

十進型（Decimal）の補足
十進型は、DECIMAL(全体の桁数, 小数点以下の桁数)で指定します。最大で指定できるのは28桁です。
桁数の指定をせずDECIMALのみ記載すると18桁に設定されます。

＜データ型に関する参考サイト＞
・Microsoft Access データ型
・OfficePro データ型の種類
・Access フィールドのデータ型一覧：データ型とは、説明とサイズ
・オートナンバー型フィールドが上限値を超えた場合はどうなる？
・新しいbigintフィールド用のMicrosoftAccess DDL
・MySQLのデータ型

2. テーブルのデータを削除する（DELETE文）

次に、テーブルのデータの削除についてです。
ここで使用するDELETE文は、テーブルに格納されているデータのみ削除します。
テーブル自体は削除されません。

2-1. テーブルのデータを削除するソースコード

2-1-1. RunSQLメソッドを使用したソースコード例

DoCmdオブジェクトのRunSQLメソッドを使用する場合は、テーブルの生成（CREATE TABLE構文）の場合と同様に次のように書きます。

'テーブルのデータを削除する
Sub DeleteTableDataByDoCmd()
    DoCmd.RunSQL "DELETE FROM 商品リスト"
End Sub

しかし、このままだと、実行した際にシステムメッセージが表示されます。

このメッセージを出さないようにするには、次のようにDoCmdオブジェクトのSetWarningsメソッドでシステムメッセージの表示を制御します。

'テーブルのデータを削除する
Sub DeleteTableByDoCmd()
    DoCmd.SetWarnings False '一旦システムメッセージを非表示に設定
    DoCmd.RunSQL "DELETE FROM 商品リスト"
    DoCmd.SetWarnings True 'システムメッセージを表示するよう再設定
End Sub

2-1-2. ADOを使用したソースコード例

ADOを使用する場合は、次のように書きます。

'テーブルのデータを削除する
Sub DeleteTableDataByADO()
    Dim cn As New ADODB.Connection 'Connectionオブジェクトのインスタンスを生成
    Set cn = CurrentProject.Connection '現在のAccessDBに接続
    
    Dim cm As New ADODB.Command 'Commandオブジェクトのインスタンスを生成
    cm.ActiveConnection = cn '現在のAccessDBに接続の関連付けを行う
    cm.CommandText = "DELETE FROM 商品リスト" 'DELETE文を実行
    cm.Execute 'ExecuteメソッドでSQL文を実行
    
    Set cm = Nothing 'Commandオブジェクトの解放
    cn.Close: Set cn = Nothing 'Connectionオブジェクトの解放
End Sub

2-2. SQL文について

SQL文でテーブルを削除するときは、DELETE構文を使用します。
次の例は、テーブルのデータを全て削除する例です。

テーブルのデータを全て削除する場合

＜構文＞
DELETE FROM テーブル名;

＜記載例＞
DELETE FROM 商品リスト;

テーブルのうち、条件に合ったレコードのみ削除する場合は、次のように条件を指定します。

テーブルのデータの一部を削除する場合

＜構文＞
DELETE FROM テーブル名 WHERE 条件;

＜記載例＞
DELETE FROM 商品リスト WHERE 単価 < 100;

さほど難しい内容ではないと思います。

3. テーブル自体を削除する（DROP TABLE文）

今度は、テーブル自体を削除するソースコードです。
これを実行すると、テーブル自体がなくなってしまいます。

3-1. テーブル自体を削除するソースコード

3-1-1. RunSQLメソッドを使用したソースコード例

'テーブル自体を削除する
Sub DropTableByDoCmd()
    DoCmd.RunSQL "DROP TABLE 商品リスト"
End Sub

3-1-2. ADOを使用したソースコード例

'テーブル自体を削除する
Sub DropTableByADO()
    Dim cn As New ADODB.Connection 'Connectionオブジェクトのインスタンスを生成
    Set cn = CurrentProject.Connection '現在のAccessDBに接続
    
    Dim cm As New ADODB.Command 'Commandオブジェクトのインスタンスを生成
    cm.ActiveConnection = cn '現在のAccessDBに接続の関連付けを行う
    cm.CommandText = "DROP TABLE 商品リスト" 'DROP TABLE文を実行
    cm.Execute
    
    Set cm = Nothing 'Commandオブジェクトの解放
    cn.Close: Set cn = Nothing 'Connectionオブジェクトの解放
End Sub

3-2. SQL文について

SQLでテーブル自体を削除するときは、DROP TABLE構文を使用します。

テーブル自体を削除する場合

＜構文＞
DROP TABLE テーブル名;

＜記載例＞
DROP TABLE 商品リスト;

4. 同名のテーブルが存在するかどうかを確認してテーブルを削除する方法（参考）

CREATE TABLE文で新たにテーブルを作成しようとする場合に、既に同名のテーブルが存在していた場合は、次のようなエラーが生じて処理が進みません。

このような場合は、処理を中断するか、既に存在しているテーブルを削除した上で新しいテーブルを作るなどの制御が必要になります。

ここでは参考として、既に存在する同名のテーブルを削除した上で、新しいテーブルを作成する例を紹介しておきます。

4-1. ADOXの使用

ADOXはADOの拡張機能です。
ここでは、「現在のデータベースのテーブル一覧」を取得するためにADOXを使用します。

これも先に参照設定を行っておきます。
次のように、VBEの参照設定画面から、Microsoft ADO Ext. 6.0 for DDL and Securityというライブラリを選択して、OKボタンを押します。

4-2. ソースコード例

以下は、CREATE TABLE文の実行の前に、既存のテーブルに同名のテーブルがあるかを確認して、同名のテーブルがあった場合は削除を行う制御を加えたソースコードです。
特に説明は加えませんので、細かいことはソースコードのコメント部分を参考にしてください。

'テーブルを作成する
Sub CreateTable()
    Dim cn As New ADODB.Connection 'Connectionオブジェクトのインスタンスを生成
    Set cn = CurrentProject.Connection '現在のAccessDBに接続
    
    Dim cm As New ADODB.Command 'Commandオブジェクトのインスタンスを生成
    cm.ActiveConnection = cn 'ActiveConnectionプロパティで接続の関連付けを行う
    
    Call DropDBTable("商品リスト") '同名のテーブルがあれば削除する
    
    cm.CommandText = "CREATE TABLE 商品リスト(商品ID COUNTER PRIMARY KEY, 商品名 TEXT(20), 単価 CURRENCY)" 'CommandTextプロパティにSQL文をセット
    cm.Execute 'ExecuteメソッドでSQL文を実行
    
    Set cm = Nothing 'Commandオブジェクトの解放
    cn.Close: Set cn = Nothing 'Connectionオブジェクトの解放
End Sub

'テーブルが存在するか否かを確認する（戻り値：True=存在する, False=存在しない）
Function IsExistTable(tblName As String) As Boolean
    Dim cn As New ADODB.Connection: Set cn = CurrentProject.Connection '現在のDBの接続を取得
    Dim ct As New ADOX.Catalog: ct.ActiveConnection = cn 'Catalogオブジェクトを現在のDBに関連付け
    Dim tbl As Table 'テーブルオブジェクトを格納する変数
    IsExistTable = False '初期値を明示（書かなくともFalse）
    
    For Each tbl In ct.Tables 'CatalogオブジェクトのTablesコレクションを1つずつ取得
        If tbl.Type = "Table" And tbl.Name = tblName Then 'TableオブジェクトのTypeプロパティNameプロパティで一致するかを確認
            IsExistTable = True
            Exit For
        End If
    Next
    
    Set ct = Nothing 'Catalogオブジェクトの解放
    cn.Close: Set cn = Nothing 'Connectionオブジェクトの解放
End Function

'テーブルが開いているか否かを確認する（戻り値：True=開いてる, False=閉じている）
Function IsOpenedTable(tblName As String) As Boolean
    IsOpenedTable = (SysCmd(acSysCmdGetObjectState, acTable, tblName) = acObjStateOpen)
End Function

'テーブルを削除する（テーブルが存在している場合のみ）
Sub DropDBTable(tblName As String)
    Dim cn As New ADODB.Connection: Set cn = CurrentProject.Connection
    Dim cm As New ADODB.Command: cm.ActiveConnection = cn
    
    If IsExistTable(tblName) Then
        If IsOpenedTable(tblName) Then DoCmd.Close acTable, tblName, acSaveYes 'テーブルが開いていたら閉じる
        cm.CommandText = "DROP TABLE " & tblName 'DROP TABLE文を実行
        cm.Execute
    End If
    
    Set cm = Nothing 'Commandオブジェクトの解放
    cn.Close: Set cn = Nothing 'Connectionオブジェクトの解放
End Sub

さいごに

Accessで CREATE TABLE文 が必要になることはあまりないかもしれませんが、備忘として残しておきました。
誰かの役に立つ日がくれば幸いのところです。