Go to Qiita Advent Calendar 2024 Top
LoginSignup
16
25

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@t-yama-3

VBAからMySQLに接続してデータベース操作する方法のまとめ

Last updated at Posted at 2021-02-25

はじめに

VBAからMySQLに接続する方法と各種の基本操作について、備忘のためにまとめておきます。

＜目次＞
1. 基本的なコードの記載
2. 接続方法の詳細
　2-1. Driver（ドライバ）の指定
　2-2. Server（サーバ名）の指定
　2-3. Port（ポート番号）の指定
　2-4. Database（データベース名）の指定
　2-5. ユーザー名とパスワードの指定
　2-6. Charset（文字セット）の指定
3. 主なSQL構文によるソースコード例
　3-1. SELECT文（レコードの取得）
　3-2. CREATE TABLE文（テーブルの作成）
　3-3. INSERT文（レコードの追加）
　3-4. UPDATE文（レコードの更新）
　3-5. DELETE文（レコードの削除）
　3-6. DROP TABLE文（テーブルの削除）
　3-7. テーブルが存在するか否かを確認する

1. 基本的なコードの記載

前提として、MySQLに作成した「test_db」というデータベース（ローカルホストに作成）に、次のような「商品リスト」というテーブルを作成している状態から始めます。
（MySQLの操作に慣れない方は、MySQLの使い方というサイトがお勧めです。）

コマンドプロンプト（MySQLのテーブル）
mysql> select * from 商品リスト;
+--------+--------+------+
| 商品ID | 商品名 | 単価 |
+--------+--------+------+
|      1 | ビール | 5000 |
|      2 | 焼き鳥 | 7000 |
|      3 | 柿ピー |  500 |
|      4 | ポテチ | 3000 |
+--------+--------+------+
4 rows in set (0.00 sec)
コマンドプロンプト（MySQLのテーブル作成時の定義）
mysql> create table 商品リスト (商品ID int primary key, 商品名 varchar(20), 単価 decimal(10));

上記のテーブルの内容を、VBAからSELECT文で取得するには次のようなコードを記述します。
なお、事前に参照設定（Microsoft ActiveX Data Object 6.1 Library）をしています。
ユーザー名とパスワードは、ご自身の設定内容に修正してください。

VBAからSELECT文を実行（最低限の記載内容）
'SELECT文の実行
Sub ConnectToMysqlDatabase_Select()
    'ADOを使用してMySQLに接続
    Dim cn As New ADODB.Connection
    cn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
                          "Server=localhost;" & _
                          "Database=test_db;" & _
                          "User=root;" & _
                          "Password=XXXXXXXX"
    cn.Open
    
    'SELECT文の実行（取得した内容の確認）
    Dim rs As New ADODB.Recordset
    rs.Open "SELECT * FROM 商品リスト", cn 'SQL文の実行
    Do Until rs.EOF
        Debug.Print rs("商品ID") & ", " & rs("商品名") & ", " & rs("単価")
        rs.MoveNext
    Loop

    'メモリの解放（無くとも構わない）
    rs.Close: Set rs = Nothing
    cn.Close: Set cn = Nothing
End Sub
イミディエイトウィンドウ
1, ビール, 5000
2, 焼き鳥, 7000
3, 柿ピー, 500
4, ポテチ, 3000

参考サイト（MySQLにADOで接続）
・ADO(ActiveX Data Objects)の使い方の要点
・データベース（MySQL）に接続する（ADO）
・C#からMySQLに接続する

2. 接続方法の詳細

ここでは、ConnectionStringプロパティについての説明を書いておきます。
ADOに関する基本的なことは、必要に応じてこちらの記事などを参照してください。

ConnectionStringプロパティの設定例
    cn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
                          "Server=localhost;" & _
                          "Port=3306;" & _
                          "Database=test_db;" & _
                          "User=root;" & _
                          "Password=XXXXXXXX;" & _
                          "Charset=sjis;"

＜ConnectionStringプロパティの設定内容一覧＞

キーワード 設定内容 指定例
Driver ODBCドライバを指定 Driver={MySQL ODBC 8.0 Unicode Driver}
Server ホスト名又はIPアドレスを指定 Server=192.168.3.5
Port サーバのポート番号を指定 Port=3306
Database データベース名を指定 Database=販売管理DB
User (UID) ユーザー名を指定 UID=taka
Password (PWD) パスワードを指定 PWD=12345678
Charset クライアント側の文字コードを指定 Charset=cp932

・キーワードと値は等号=で結びつけます。キーワードと値のペアごとにセミコロン;で区切ります。
・キーワードは、大文字と小文字は区別されません。
・UserはUID、PasswordはPWDと書いても同じ結果となります。

参考サイト
・SqlConnection.ConnectionStringプロパティ

2-1. Driver（ドライバ）の指定

まず、Driverの指定についてです。
使用するドライバ名を波括弧で括って記述します。

Driver={MySQL ODBC 8.0 Unicode Driver}

このドライバ名は、PCにインストールされているODBCドライバのバージョンに基づいて記述することが必要です。
ドライバは、**ODBCデータソースアドミニストレーター**から確認できます。以下、確認方法について書いておきます。

2-1-1. ODBCデータソースアドミニストレーターの起動方法

ODBCデータソースアドミニストレーターは次のような画面で表示されます。
2021-02-21 225805.png
ODBCデータソースアドミニストレーターの起動方法には、次のような起動方法があります。

①スタートメニューから起動
Windows10では、スタートメニューから「ODBC データ ソース」と検索して起動することができます。

②コントロールパネルから起動
コントロールパネルからでも、「システムとセキュリティ」→「管理ツール」をクリックすれば、管理ツールのショートカット一覧が表示されますので、そこから使用する環境に合わせてODBC データ ソース (64 ビット)又はODBC Data Sources (32-bit)のどちらかを起動します。

③実行ファイルから直接起動
次のディレクトリにあるファイルから直接起動することもできます。

＜64ビット版＞
C:\Windows\System32\odbcad32.exe

＜32ビット版＞
C:\Windows\SysWOW64\odbcad32.exe

2-1-2. MySQLのODBCドライバーの確認

ODBCデータソースアドミニストレーターが起動できたら「ドライバ」タブを開きます。
すると、次のような一覧が表示されます。
2021-02-11 164523.png
既にドライバがインストールされていれば、次の2つのドライバを確認することができると思います。

ドライバ名 対応する文字コード
MySQL ODBC 8.0 ANSI Driver UTF-8
MySQL ODBC 8.0 Unicode Driver UTF-16(UCS-2)

対応する文字コードは、実際に入力して確認した結果（経験則）なので、もう少し正しい表現があるかもしれません。

2-1-2-1. MySQL ODBC 8.0 Unicode Driver (UTF-16の場合)

Windows VBAで使用されている文字コードはUnicode（UTF-16）なので、MySQL ODBC 8.0 Unicode Driverを選びます（ただし、VBAで入力できる文字コードはSHIFT_JISなので、ここはややこしいところ）。
何にしても、WindowsのVBAであれば迷わずMySQL ODBC 8.0 Unicode Driverを選んでください。

2-1-2-2. MySQL ODBC 8.0 ANSI Driver (UTF-8の場合)

一方、MySQL ODBC 8.0 ANSI Driverを使用するとデータベース名、テーブル名、カラム名がUTF-8で読み込まれるため、VBAでは文字化けして使用は困難となります。
例えば、「商品ID」と「商品名」というカラムがあると、UTF-8で取得された文字列を無理矢理SHIFT_JISで読もうとするので、次のような文字化けが起こります。

MySQLのカラム名 UTF-8文字コード ANSI Driverを介した表示（SHIFT_JISの文字コード）
商品ID e5 95 86 e5 93 81 49 44 蝠・刀ID（e5 95 / 〓 / 93 81 / 49 / 44）
商品名 e5 95 86 e5 93 81 e5 90 8d 蝠・刀蜷・（e5 95 / 〓 / 93 81 / e5 90 / 〓）

ただし、各レコードのデータ（データベースに格納する個々のデータ）は、ANSI Driverでも文字化けせずに読み込むことができます（各レコードのデータは、必要に応じてCharsetの設定で文字コードを指定することもできます）。
なので、データベース名、テーブル名、カラム名を全て英数字（正確にはASCII文字）にするなどの処置を行えば、ANSI Driverでも何とかなります（やる価値はないと思いますが）。

なお、Macの文字コードはUTF-8なので、MacでODBCドライバを使用する場合は、こちらのANSI Driverを使うのかもしれません（試していないのでわかりません）。

参考サイト
・ODBCドライバーのバージョンを確認する
・10.1.10 Unicode のサポート

2-1-3. MySQLのODBCドライバーが無い場合

私の手元の環境では、MySQLのインストール時にドライバも一緒にインストールされていました（おそらく）。
使用する環境にドライバがインストールされていない場合には、次のようなWEBサイトなどを参考にして、インストールを行ってください。

参考サイト
・MySQL の ODBC ドライバーをインストールする
・MySQL ODBC ドライバのインストール（Windows 上）
・MySQLへのODBC接続設定をする

2-2. Server（サーバ名）の指定

次に、Server（サーバ名）の指定についてです。
ここは、MySQLのデータベースを格納しているホスト名又はIPアドレスを指定します。

＜自分のローカルPC上のMySQLデータベースを参照する場合＞
Server=localhost

＜IPアドレスで指定する場合＞
Server=192.168.3.5

ローカルに作成しているMySQLのデータベースはlocalhostと指定すれば大丈夫です。

2-2-1. ホスト名で指定する場合

Serverをホスト名で指定する方法は、色々な場合では試していないので、確認した範囲で書いておきます。
ローカルPC上のMySQLデータベースのホスト名を確認すると、次のように自分のPC名が表示されます（参照：mysqlで接続しているDBサーバのホスト名を確認する方法）。

mysql> show variables like 'hostname';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| hostname      | DESKTOP-PC      |
+---------------+-----------------+
1 row in set, 1 warning (0.02 sec)

このホスト名「DESKTOP-PC」をServerに指定して接続をしてみます。

cn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=DESKTOP-PC;Database=test_db;UID=root;PWD=XXXXXXXX;"

しかし、次のようにアクセスが拒否されます。
2021-02-23 120818.png
これを無理矢理接続するために、次のようなユーザーを作成します（ユーザー作成方法の詳細は「MySQLの使い方 ユーザーの作成」を参照してください）。

mysql> create user 'yama'@'DESKTOP-PC' identified by '123456';
Query OK, 0 rows affected (0.53 sec)

mysql> grant select on test_db.* to 'yama'@'DESKTOP-PC';
Query OK, 0 rows affected (0.17 sec)

最初のCREATE USER文で、ユーザー名を「yama」、クライアントのホスト名を「DESKTOP-PC」（ローカルホスト名）、パスワードを「123456」とするユーザーを作成しています。
続くGRANT文で、作成したユーザーに、「test_db」データベースの全てのテーブルに対して、SELECT権限を与えています。
これにより、MySQLのサーバは、「yama@DESKTOP-PC」というユーザーに対してアクセス権を与えたことになります。

この作成したユーザーで、接続をすると無事にホスト名でAccessをすることができます。

Serverにホスト名を指定する
cn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=DESKTOP-PC;Database=test_db;UID=yama;PWD=123456;"

理屈としては、同じローカルホスト内であっても、ホスト名を指定して接続する以上は、アクセスするクライアントに対しても接続の許可を与えないとならないということでしょうか…（詳しくはわかりません）。
外部サーバに接続する場合は、このあたりを上手く設定すれば良さそうです。

参考サイト
・MySQL を外部接続できるようにする
・Host xxx is not allowed to connect to this MySQL server の対応

2-2-2. IPアドレスで指定する場合

自分のPCのIPアドレスは、ipconfigコマンドで確認することができます。

C:\>ipconfig
Windows IP 構成
（中略）
イーサネット アダプター イーサネット:
   接続固有の DNS サフィックス . . . . .:
   リンクローカル IPv6 アドレス. . . . .:
   IPv4 アドレス . . . . . . . . . . . .: 192.168.3.5
   サブネット マスク . . . . . . . . . .: 255.255.255.0
   デフォルト ゲートウェイ . . . . . . .: 192.168.3.1
（以下略）

上記のうち、IPv4 アドレスが自分のPCのIPアドレス（プライベートIPアドレス）になります。

確認できたIPアドレス「192.168.3.5」をServerに指定して接続してみますが、IPアドレスの場合も前例のホスト名の場合と同様に、先に、接続するためのユーザーを作成します。

mysql> create user 'tama'@'192.168.3.5' identified by '123456';
Query OK, 0 rows affected (0.28 sec)

mysql> grant all on *.* to 'tama'@'192.168.3.5';
Query OK, 0 rows affected (0.18 sec)

前例と基本的に同じですが、GRANT文においては、作成したユーザーに対して、全てのデータベースの全てのテーブル（*.*）に対して、全ての権限（grant all）を与えています。

ConnectionStringプロパティの部分には次のように記載します。これでうまく動作すると思います。

ServerにIPアドレスを指定する
cn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=192.168.3.5;Database=test_db;UID=tama;PWD=123456;"

2-3. Port（ポート番号）の指定

Port（ポート番号）の指定については次のように記載します。

Port=3306

MySQLのポート番号はデフォルトで3306です。
デフォルトのままであれば、Port番号の指定は無くとも構いません。

2-4. Database（データベース名）の指定

Database（データベース名）の指定については次のように記載します。これ以上何も言うことはありません。

Database=test_db

2-5. ユーザー名とパスワードの指定

User（ユーザー名）とPassword（パスワード）の指定については次のように記載します。

＜例1＞
User=tama
Password=123456

＜例2＞
UID=tama
PWD=123456

UserはUIDと書くこともでき、PasswordはPWDと書くこともできます。

2-6. Charset（文字セット）の指定

最後に、Charset（文字セット）の指定です。
次の例ではクライアント側の文字コードをSHIFT_JISと指定していることになります。
Windows VBAでは、sjis又はcp932としておけば間違いないところと思います。

Charset=sjis

参考とさせていただいたサイトによると、クライアント側の文字コードとデータベース側の文字コードが異なる場合に、クライアント側の文字コードを指定するとしています。

つまり、Charsetはあくまで、クライアント側の設定と言うことになります。
MySQLデータベースの文字コードがUTF-8（utf8mb4）であっても、SHIFT_JIS（cp932）であっても、Charset（文字セット）の指定には何ら影響はありません（次の表を見ればわかります）。

2-6-1. 文字コードの設定と読取り内容の確認

Driver及びCharsetの設定がどのように影響するかについて、確認した内容は次の表のとおりです。

MySQL DBの文字コード Driverの指定 Charsetの指定 カラム名読み込み結果 レコード読み込み結果 ADOXカラム名読み込み結果
utf8mb4 / cp932 Unicode Driver 指定なし 正常 正常 文字化け
utf8mb4 / cp932 Unicode Driver utf8mb4 正常 文字化け 文字化け
utf8mb4 / cp932 Unicode Driver utf8 正常 正常 文字化け
utf8mb4 / cp932 Unicode Driver sjis / cp932 正常 正常 正常
utf8mb4 / cp932 ANSI Driver 指定なし 文字化け 正常 正常
utf8mb4 / cp932 ANSI Driver utf8mb4 文字化け 文字化け 文字化け
utf8mb4 / cp932 ANSI Driver utf8 文字化け 文字化け 文字化け
utf8mb4 / cp932 ANSI Driver sjis / cp932 文字化け 正常 正常

MySQLのデータベースの文字コードは、utf8mb4で指定してもcp932で指定しても結果は変わらないのでまとめて記載しています。
赤字のところは予想外の結果です（何でだろう？）。
最後の「ADOXカラム名読み込み」は、ADOXの**Tablesコレクション**から得たカラム名の確認結果です（具体的なことは最後に取り上げます）。

2-6-2. 文字コードの設定による挙動のまとめ

①　MySQL DBにおいて設定している文字コードは、VBAの結果に影響を及ぼさない。
②　ANSI Driverを選択すると、DB名、テーブル名、カラム名の読取りに文字化けが生じる（データはUTF-8で取得されるがVBAでは読み込めないため）。
③　Unicode Driverを選択すると、DB名、テーブル名、カラム名の文字化けは生じない。
④　Charsetの指定に、**utf8mb4（UTF-8）を選択するとレコードに文字化けが生じる（ただし、Unicode DriverでUTF-8を指定すると何故か正常に読み取れる）。
⑤　ADOXを使用する際は、CharsetをSHIFT_JIS（cp932）**と指定する必要がある。

結論として、DriverはMySQL ODBC 8.0 Unicode Driverを選択して、Charsetは**sjis（cp932）**を選択すれば問題はないということになります。

参考サイト
ODBCドライバと各社RDBクライアント文字コードの関係
VBA Unicode 文字の入力や変換、読み込みについて

3. 主なSQL構文によるソースコード例

以下、テーブルの操作を中心に、主要なSQL文を書いておきます。

3-1. SELECT文（レコードの取得）

SELECT文の例は最初に挙げているので、少し形を変えて、カラム名も取得してExcelのシートに書き出すソースコードを貼っておきます。

'SELECT文（レコード内容の取得）
Sub ConnectToMysqlDatabase_SELECT()
    'ADOを使用してMySQLに接続
    Dim cn As New ADODB.Connection 'Connectionオブジェクトのインスタンスを生成
    cn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
                          "Server=localhost;" & _
                          "Database=test_db;" & _
                          "User=yama3;" & _
                          "Password=123456"
    cn.Open
    
    'SELECT文の実行
    Dim rs As New ADODB.Recordset 'Recordsetオブジェクトのインスタンスを生成
    rs.Open "SELECT * FROM 商品リスト", cn
    
    '出力先のワークシートオブジェクトを取得
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    'カラム名をワークシートに書き込む
    Dim i As Long, j As Long
    i = 1
    For j = 1 To rs.Fields.count
        ws.Cells(i, j) = rs(j - 1).Name
    Next j
    
    'レコードをワークシートに書き込む
    Do Until rs.EOF
        i = i + 1
        For j = 1 To rs.Fields.count
            ws.Cells(i, j) = rs(rs(j - 1).Name)
        Next j
        rs.MoveNext
    Loop

    'メモリの解放
    rs.Close: Set rs = Nothing
    cn.Close: Set cn = Nothing
End Sub

出力結果は次のようになります。
2021-02-24 215735.png

上記のコードでは、カラム数を取得するためにFieldsコレクションを、カラム名を取得するためにFieldオブジェクトを使用しています。
ADOでよく使用するプロパティは次のあたりです。

名称 内容 備考
Countプロパティ Fieldsコレクション内のFieldオブジェクト数を示す Fieldsコレクションのプロパティ
Nameプロパティ フィールド名（カラム名）を示します Fieldオブジェクトのプロパティ
Typeプロパティ データ型をDataTypeEnumで示します Fieldオブジェクトのプロパティ

3-2. CREATE TABLE文（テーブルの作成）

テーブルの作成にはCREATE TABLE文を使用します。

'CREATE TABLE文（テーブル作成）
Sub ConnectToMysqlDatabase_CreateTable()
    'ADOを使用してMySQLに接続
    Dim cn As New ADODB.Connection 'Connectionオブジェクトのインスタンスを生成
    cn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
                          "Server=localhost;" & _
                          "Database=test_db;" & _
                          "User=yama3;" & _
                          "Password=123456;"
    cn.Open
    
    'CREATE TABLE文の実行
    Dim cm As New ADODB.Command 'Commandオブジェクトのインスタンスを生成
    cm.ActiveConnection = cn
    cm.CommandText = "CREATE TABLE 買物リスト(" & _
                        "ID INT PRIMARY KEY," & _
                        "品名 VARCHAR(20)," & _
                        "区分 VARCHAR(10)," & _
                        "単価 DECIMAL(10)," & _
                        "購入数 SMALLINT," & _
                        "購入日 DATE)"
    cm.Execute
    
    'メモリの解放
    Set cm = Nothing
    cn.Close: Set cn = Nothing
End Sub

実行結果をコマンドプロンプトから確認すると、次のようなテーブルが作成されていることが分かります。

コマンドプロンプト
mysql> show create table 買物リスト\G
*************************** 1. row ***************************
       Table: 買物リスト
Create Table: CREATE TABLE `買物リスト` (
  `ID` int NOT NULL,
  `品名` varchar(20) DEFAULT NULL,
  `区分` varchar(10) DEFAULT NULL,
  `単価` decimal(10,0) DEFAULT NULL,
  `購入数` smallint DEFAULT NULL,
  `購入日` date DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.07 sec)

PRIMARY KEY制約を付加すると、自動的にNOT NULL制約も付加されています。また、明示されていませんがUNIQUE制約も内包されています。

3-3. INSERT文（レコードの追加）

レコードの追加にはINSERT文を使用します。

3-3-1. レコードを1つ追加するINSERT文

まず、レコードを1つ追加する場合です。

'INSERT文（データの追加）
Sub ConnectToMysqlDatabase_InsertInto1()
    'ADOを使用してMySQLに接続
    Dim cn As New ADODB.Connection 'Connectionオブジェクトのインスタンスを生成
    cn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
                          "Server=localhost;" & _
                          "Database=test_db;" & _
                          "User=yama3;" & _
                          "Password=123456;"
    cn.Open
    
    'INSERT文の実行
    Dim cm As New ADODB.Command 'Commandオブジェクトのインスタンスを生成
    cm.ActiveConnection = cn
    cm.CommandText = "INSERT INTO 買物リスト VALUES (1, 'ほうれんそう', '野菜', 150, 2, '2021-2-25')"
    cm.Execute
    
    'メモリの解放
    Set cm = Nothing
    cn.Close: Set cn = Nothing
End Sub

コマンドプロンプトから確認すると、次のようにレコードが追加されているのがわかります。

コマンドプロンプト
mysql> select * from 買物リスト;
+----+--------------+------+------+--------+------------+
| ID | 品名         | 区分 | 単価 | 購入数 | 購入日     |
+----+--------------+------+------+--------+------------+
|  1 | ほうれんそう | 野菜 |  150 |      2 | 2021-02-25 |
+----+--------------+------+------+--------+------------+
1 row in set (0.04 sec)

3-3-2. Excelシートからまとめてレコードを追加するINSERT文

次に、複数のレコードを一気に追加する場合のコードです。
ここでは、次のようなExcelシートの表を、MySQLデータベースに格納してみます。
2021-02-24 234850.png

コードは次のとおりです。

'INSERT文（データの追加）
Sub ConnectToMysqlDatabase_InsertInto2()
    
    'ADOを使用してExcelファイルに接続
    Dim xlCn As New ADODB.Connection
    xlCn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
                            "DBQ=" & ThisWorkbook.FullName & ";" & _
                            "ReadOnly=1"
    xlCn.Open
    
    'ExcelのシートをSELECT文で読み込んでINSERT文を作成
    Dim xlRs As New ADODB.Recordset
    xlRs.Open "SELECT * FROM [サンプルテーブル$]", xlCn
    Dim sqlStr As String: sqlStr = "INSERT INTO 買物リスト VALUES " 'これに続けてテーブル内容を列挙していく
    Dim comma As String: comma = ""
    Do Until xlRs.EOF
        sqlStr = sqlStr & comma & "(" & xlRs("ID") & ", '" & xlRs("品名") & "', '" & xlRs("区分") & "', " & xlRs("単価") & ", " & xlRs("購入数") & ", '" & xlRs("購入日") & "')"
        If comma = "" Then comma = ", "
        xlRs.MoveNext
    Loop
    
    'ADOを使用してMySQLに接続
    Dim cn As New ADODB.Connection 'Connectionオブジェクトのインスタンスを生成
    cn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
                          "Server=localhost;" & _
                          "Database=test_db;" & _
                          "User=yama3;" & _
                          "Password=123456;"
    cn.Open
    
    'INSERT文の実行
    Dim cm As New ADODB.Command 'Commandオブジェクトのインスタンスを生成
    cm.ActiveConnection = cn
    cm.CommandText = sqlStr
    cm.Execute
    
    'メモリの解放
    xlRs.Close: Set xlRs = Nothing
    xlCn.Close: Set xlCn = Nothing
    Set cm = Nothing
    cn.Close: Set cn = Nothing
End Sub

レコード1つにき1つのINSERT文を発行しても良いのですが、それだと処理に時間が掛かってしまうので、複数のデータをまとめて追加するINSERT文を使用して、一括でデータを追加しています。

コマンドプロンプトから確認すると、次のようにレコードが追加されています。

コマンドプロンプト
mysql> select * from 買物リスト;
+----+--------------+------+------+--------+------------+
| ID | 品名         | 区分 | 単価 | 購入数 | 購入日     |
+----+--------------+------+------+--------+------------+
|  1 | ほうれんそう | 野菜 |  150 |      2 | 2021-02-25 |
|  2 | にんじん     | 野菜 |   70 |      3 | 2021-02-09 |
|  3 | りんご       | 果物 |  150 |      2 | 2021-02-28 |
|  4 | みかん       | 果物 |   40 |     10 | 2021-02-01 |
|  5 | キャベツ     | 野菜 |  180 |      1 | 2021-02-28 |
|  6 | トマト       | 野菜 |   50 |      3 | 2021-02-28 |
|  7 | じゃがいも   | 野菜 |   50 |     15 | 2021-01-30 |
|  8 | バナナ       | 果物 |  300 |      2 | 2021-02-03 |
|  9 | メロン       | 果物 | 1500 |      1 | 2021-02-26 |
+----+--------------+------+------+--------+------------+
9 rows in set (0.00 sec)

3-4. UPDATE文（レコードの更新）

レコードの内容を更新するにはUPDATE文を使います。

'UPDATE文（レコードの更新）
Sub ConnectToMysqlDatabase_UPDATE()
    'ADOを使用してMySQLに接続
    Dim cn As New ADODB.Connection 'Connectionオブジェクトのインスタンスを生成
    cn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
                          "Server=localhost;" & _
                          "Database=test_db;" & _
                          "User=yama3;" & _
                          "Password=123456;"
    cn.Open
    
    'UPDATE文の実行
    Dim cm As New ADODB.Command 'Commandオブジェクトのインスタンスを生成
    cm.ActiveConnection = cn
    cm.CommandText = "UPDATE 買物リスト SET 購入数 = 20 WHERE ID = 7"
    cm.Execute
    
    'メモリの解放
    Set cm = Nothing
    cn.Close: Set cn = Nothing
End Sub

コマンドプロンプトで確認すると、IDが7のレコードの購入数が15から20に更新されていることがわかります。

コマンドプロンプト
mysql> select * from 買物リスト;
+----+--------------+------+------+--------+------------+
| ID | 品名         | 区分 | 単価 | 購入数 | 購入日     |
+----+--------------+------+------+--------+------------+
|  1 | ほうれんそう | 野菜 |  150 |      2 | 2021-02-25 |
|  2 | にんじん     | 野菜 |   70 |      3 | 2021-02-09 |
|  3 | りんご       | 果物 |  150 |      2 | 2021-02-28 |
|  4 | みかん       | 果物 |   40 |     10 | 2021-02-01 |
|  5 | キャベツ     | 野菜 |  180 |      1 | 2021-02-28 |
|  6 | トマト       | 野菜 |   50 |      3 | 2021-02-28 |
|  7 | じゃがいも   | 野菜 |   50 |     20 | 2021-01-30 |
|  8 | バナナ       | 果物 |  300 |      2 | 2021-02-03 |
|  9 | メロン       | 果物 | 1500 |      1 | 2021-02-26 |
+----+--------------+------+------+--------+------------+
9 rows in set (0.01 sec)

3-5. DELETE文（レコードの削除）

レコードの削除にはDELETE文を使います。

3-5-1. レコードの一部を削除するDELETE文

'DELETE文（レコードの一部削除）
Sub ConnectToMysqlDatabase_Delete1()
    'ADOを使用してMySQLに接続
    Dim cn As New ADODB.Connection 'Connectionオブジェクトのインスタンスを生成
    cn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
                          "Server=localhost;" & _
                          "Database=test_db;" & _
                          "User=yama3;" & _
                          "Password=123456;"
    cn.Open
    
    'DELETE文の実行
    Dim cm As New ADODB.Command 'Commandオブジェクトのインスタンスを生成
    cm.ActiveConnection = cn
    cm.CommandText = "DELETE FROM 買物リスト WHERE 区分 = '野菜'"
    cm.Execute
    
    'メモリの解放
    Set cm = Nothing
    cn.Close: Set cn = Nothing
End Sub

実行結果をコマンドプロンプトで確認すると、次のとおりです。

コマンドプロンプト
mysql> select * from 買物リスト;
+----+--------+------+------+--------+------------+
| ID | 品名   | 区分 | 単価 | 購入数 | 購入日     |
+----+--------+------+------+--------+------------+
|  3 | りんご | 果物 |  150 |      2 | 2021-02-28 |
|  4 | みかん | 果物 |   40 |     10 | 2021-02-01 |
|  8 | バナナ | 果物 |  300 |      2 | 2021-02-03 |
|  9 | メロン | 果物 | 1500 |      1 | 2021-02-26 |
+----+--------+------+------+--------+------------+
4 rows in set (0.02 sec)

3-5-2. レコードの全部を削除するDELETE文

'DELETE文（レコードの全部削除）
Sub ConnectToMysqlDatabase_Delete2()
    'ADOを使用してMySQLに接続
    Dim cn As New ADODB.Connection 'Connectionオブジェクトのインスタンスを生成
    cn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
                          "Server=localhost;" & _
                          "Database=test_db;" & _
                          "User=yama3;" & _
                          "Password=123456;"
    cn.Open
    
    'DELETE文の実行
    Dim cm As New ADODB.Command 'Commandオブジェクトのインスタンスを生成
    cm.ActiveConnection = cn
    cm.CommandText = "DELETE FROM 買物リスト"
    cm.Execute
    
    'メモリの解放
    Set cm = Nothing
    cn.Close: Set cn = Nothing
End Sub

実行結果をコマンドプロンプトで確認すると、次のとおりテーブルの中身が空（Empty）であることがわかります。

コマンドプロンプト
mysql> select * from 買物リスト;
Empty set (0.00 sec)

3-6. DROP TABLE文（テーブルの削除）

テーブルそのものを削除する場合はDROP TABLE文を使います。

'DROP TABLE文（テーブル削除）
Sub ConnectToMysqlDatabase_DropTable()
    'ADOを使用してMySQLに接続
    Dim cn As New ADODB.Connection 'Connectionオブジェクトのインスタンスを生成
    cn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
                          "Server=localhost;" & _
                          "Database=test_db;" & _
                          "User=yama3;" & _
                          "Password=123456;"
    cn.Open
    
    'DROP TABLE文の実行
    Dim cm As New ADODB.Command 'Commandオブジェクトのインスタンスを生成
    cm.ActiveConnection = cn
    cm.CommandText = "DROP TABLE 買物リスト"
    cm.Execute
    
    'メモリの解放
    Set cm = Nothing
    cn.Close: Set cn = Nothing
End Sub

実行結果をコマンドプロンプトで確認すると、次のように「買物リスト」テーブルが存在しないことが確認できます。

コマンドプロンプト
mysql> select * from 買物リスト;
ERROR 1146 (42S02): Table 'test_db.買物リスト' doesn't exist

3-7. テーブルが存在するか否かを確認する

少し方向性が異なりますが、最後に、データベース内にテーブルが存在するか否かの確認を行うコードを書いておきます。
ここでは、データベースのテーブル一覧を取得するためにADOXオブジェクトを使用します。
以下の例では、事前に、Microsoft ADO Ext. 6.0 for DDL and Securityというライブラリの参照設定をしています。

'テーブルが存在するかの確認を実行
Sub IsExistMysqlTableTest()
    Debug.Print IsExistMysqlTable("test_db", "買物リスト")
End Sub

'テーブルが存在するか否かを確認する関数（戻り値：True=存在する, False=存在しない）
Function IsExistMysqlTable(dbName As String, tblName As String) As Boolean
    Dim cn As New ADODB.Connection 'Connectionオブジェクトのインスタンスを生成
    cn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
                          "Server=localhost;" & _
                          "Database=" & dbName & ";" & _
                          "User=yama3;" & _
                          "Password=123456;" & _
                          "charset=cp932;" 'クライアント側の文字コードを指定
    cn.Open
    
    Dim ct As New ADOX.Catalog: ct.ActiveConnection = cn 'Catalogオブジェクトを現在のDBに関連付け
    Dim tbl As Table 'テーブルオブジェクトを格納する変数
    IsExistMysqlTable = False '初期値を明示（書かなくともFalse）

    For Each tbl In ct.Tables 'CatalogオブジェクトのTablesコレクションを1つずつ取得
        If tbl.Type = "TABLE" And StrComp(tbl.Name, tblName, vbTextCompare) = 0 Then 'TableオブジェクトのTypeプロパティとNameプロパティで一致するかを確認
            IsExistMysqlTable = True
            Exit For
        End If
    Next
    
    Set ct = Nothing 'Catalogオブジェクトの解放
    cn.Close: Set cn = Nothing 'Connectionオブジェクトの解放
End Function

3-7-1. ADOXのTableオブジェクトについて

ADOXのCatalogオブジェクトには、データベースのコレクション (テーブル、ビュー、ユーザーなど) が格納されます。
ここでは、TablesコレクションのTableオブジェクトが持っている次のプロパティを使用して、テーブルの存否を確認しています。

プロパティ名 内容 備考
Typeプロパティ テーブルの種類を指定する文字列値を返す "TABLE"、"SYSTEM TABLE"、"GLOBAL TEMPORARY"など
Nameプロパティ テーブル名を示す （大文字と小文字の区別がされていないようです）

3-7-2. 取得されるテーブル名の文字コードについて

上記ソースコードのConnectionStringプロパティでは、charset=cp932と指定しています（charset=sjisでもよい）。
これを指定しないと、テーブル名が文字化けして、正しく読み取ることができません。

なお取得される文字コードは、使用するドライバ（MySQL ODBC 8.0 Unicode Driver又はMySQL ODBC 8.0 ANSI Driver）によっても若干異なります。
この挙動については、前掲「2-6-1. 文字コードの設定と読取り内容の確認」のとおりです。

さいごに

ドライバの選択や、文字コードについては調べても分からないことが多かったので、手元で確認してみた結果を書いておきました。
本記事は個人的な備忘にすぎませんが、何らかのお役に立つことがあれば幸いです。

16
25
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
16
25

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?