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

# はじめに

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

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

コードが書きやすいように、先にADOの参照設定をしておきます（参照設定をしない方法は、[こちら](https://qiita.com/t-yama-3/items/e79056079ffdcdb79391#1-1-1-%E5%8F%82%E7%85%A7%E8%A8%AD%E5%AE%9A%E3%82%92%E3%81%97%E3%81%AA%E3%81%84%E5%A0%B4%E5%90%88)を参考にしてください）。
VBEの参照設定の画面で、次のように**Microsoft ActiveX Data Object X.X Library**をチェックしてOKボタンを押せば完了です
![2021-02-06 023235.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/551412/bb64f190-0061-eeea-9946-33afa9cd7c75.png)

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

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

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

テーブルを作成する基本的なコードは次のとおりです。
最低限の記載ですが、これでテーブルの作成ができます。
同名のテーブルが既にあるとエラーが起きますが、この点については[後述](#4-同名のテーブルが存在するかどうかを確認してテーブルを削除する方法参考)します。

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

[DoCmdオブジェクト](https://docs.microsoft.com/ja-jp/office/vba/api/access.docmd)の[RunSQLメソッド](https://docs.microsoft.com/ja-jp/office/vba/api/access.docmd.runsql)を使用する場合は次のように書いてSQL文を実行します。

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

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

[ADO](https://qiita.com/t-yama-3/items/e79056079ffdcdb79391)で接続する場合は次のようにしてSQL文を実行します。

```vb
'テーブルを作成する
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オブジェクト](https://docs.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/recordset-object-ado)を使用してデータ処理ができるので、普段はADOを使用することが多いです（私の場合）。

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

```vb
    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のナビゲーションウィンドウには反映されませんのでご注意ください（ファイルを開き直すなどすれば見れます）。

テーブルのデザインビューを見てみると、次のようになっています。
![2021-02-07 131435.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/551412/fdd9cce8-088f-b5e1-1500-2d860db39c2f.png)
商品IDを主キー（PRIMARY KEY）に設定しているので、`鍵のマーク`が付いています。
なお、プロパティを見ると、`値要求`が「はい」、`インデックス`が「はい（重複なし）」となっています。
この点は、「[1-2-2. PRIMARY KEY制約（主キーの設定）](#1-2-2-primary-key制約主キーの設定)」で説明します。

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

以下は、ADOに関する説明になります。
まず、**[Connectionオブジェクト](https://docs.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/connection-object-ado)**を使用して、現在のDBとの接続（`CurrentProject.Connection`）を取得します。
次に、**[Command オブジェクト](https://docs.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/command-object-ado)**を使用して、次の操作を実行します。
①[ActiveConnectionプロパティ](https://docs.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/activeconnection-property-ado)を使用して、現在DBとの接続（`cn`）を関連付ける。
②[CommandTextプロパティ](https://docs.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/commandtext-property-ado)を使用して、コマンド文字列（SQL文）をセットする。
③[Executeメソッド](https://docs.microsoft.com/ja-jp/office/vba/access/concepts/miscellaneous/execute-method-ado-command)を使用して、SQL文を実行する。

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

＜**テーブル作成に関する参考サイト**＞
・[テーブルの作成（アプリケーションとしてのVBA）](http://www.k1simplify.com/vba/practical/database04.html)
・[クエリからSQL（CREATE TABLE文）を実行してAccessのテーブルを作成する](https://www.nishi2002.com/3963.html)

## 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文)](https://www.dbonline.jp/mysql/table/index1.html)」というサイトが参考になります。

### 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を使用してテーブル間のリレーションシップを定義する](https://docs.microsoft.com/ja-jp/office/vba/access/concepts/structured-query-language/define-relationships-between-tables-using-access-sql)）。


```:FOREIGN_KEY制約構文
CREATE TABLE テーブル名(カラム名 データ型 CONSTRAINT 外部キー名 REFERENCES 親テーブル名 (親カラム名), …… );
```

＜**ソースコード例**＞

```vb
'外部キーのあるテーブルを作成する
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
```

実行してリレーションシップを確認すると、一対多の関係で結合されていることが分かります。
![2021-02-07 150444.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/551412/b6d3071f-bb00-5e8f-ce00-e949b13e5186.png)
この外部キー制約は、[MySQLの場合](https://www.dbonline.jp/mysql/table/index11.html)などとは、結構異なる書き方になっていますのでご注意ください。

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

調べた範囲では、Access SQLでは「CREATE TABLE文中でインデックスを設定する方法」が見つかりませんでした（[MySQL](https://www.dbonline.jp/mysql/table/index10.html)は可能です）。
インデックスの作成を実現するには、既に作成されたテーブルに、インデックスを追加する方法を使用します（[Access SQLを使用してテーブルとインデックスを作成および削除する](https://docs.microsoft.com/ja-jp/office/vba/access/concepts/structured-query-language/create-and-delete-tables-and-indexes-using-access-sql)）。

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

＜**ソースコード例**＞

```vb
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型](https://e-words.jp/w/VARCHAR.html)の指定をしているのと同じになります。

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

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

＜**データ型に関する参考サイト**＞
・[Microsoft Access データ型](https://docs.microsoft.com/ja-jp/sql/odbc/microsoft/microsoft-access-data-types?view=sql-server-ver15)
・[OfficePro データ型の種類](https://www.officepro.jp/access/table/index4.html)
・[Access フィールドのデータ型一覧：データ型とは、説明とサイズ](https://www.microsoft-access.net/start/table-2.html)
・[オートナンバー型フィールドが上限値を超えた場合はどうなる？](http://www.ruriplus.com/msaccess/exp/exp0901.htm)
・[新しいbigintフィールド用のMicrosoftAccess DDL](https://stackoverflow.com/questions/54365337/microsoft-access-ddl-for-new-bigint-fields)
・[MySQLのデータ型](https://www.dbonline.jp/mysql/type/)

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

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

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

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

[DoCmdオブジェクト](https://docs.microsoft.com/ja-jp/office/vba/api/access.docmd)の[RunSQLメソッド](https://docs.microsoft.com/ja-jp/office/vba/api/access.docmd.runsql)を使用する場合は、テーブルの生成（CREATE TABLE構文）の場合と同様に次のように書きます。

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

しかし、このままだと、実行した際にシステムメッセージが表示されます。
![2021-02-07 175128.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/551412/fea3eadd-3f95-55c4-a7b3-a7fc989b13a3.png)
このメッセージを出さないようにするには、次のようにDoCmdオブジェクトの[SetWarningsメソッド](https://docs.microsoft.com/ja-jp/office/vba/api/access.docmd.setwarnings)でシステムメッセージの表示を制御します。

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

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

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

```vb
'テーブルのデータを削除する
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メソッドを使用したソースコード例

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

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

```vb
'テーブル自体を削除する
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文で新たにテーブルを作成しようとする場合に、既に同名のテーブルが存在していた場合は、次のようなエラーが生じて処理が進みません。
![2021-02-07 230422.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/551412/b621f79b-603c-1dba-a797-8bbf29ccd6fa.png)
このような場合は、処理を中断するか、既に存在しているテーブルを削除した上で新しいテーブルを作るなどの制御が必要になります。

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

## 4-1. ADOXの使用

[ADOX](https://docs.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/adox-api-reference)はADOの拡張機能です。
ここでは、「現在のデータベースのテーブル一覧」を取得するためにADOXを使用します。

これも先に参照設定を行っておきます。
次のように、VBEの参照設定画面から、`Microsoft ADO Ext. 6.0 for DDL and Security`というライブラリを選択して、OKボタンを押します。
![2021-02-06 184032.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/551412/cbd7515e-3312-915f-4077-f6eb4d251dfb.png)

## 4-2. ソースコード例

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

```vb
'テーブルを作成する
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
```

```vb
'テーブルが存在するか否かを確認する（戻り値：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
```

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

```vb
'テーブルを削除する（テーブルが存在している場合のみ）
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文 が必要になることはあまりないかもしれませんが、備忘として残しておきました。
誰かの役に立つ日がくれば幸いのところです。