Power Query でデータベースからデータを取得することは可能ですが、データベース上のデータを編集することはできません。
Excel からデータベースを編集するには VBA でプログラムを組む必要があります。
この記事では VBA で SQL Server に接続してデータを編集する方法を、関連の基礎知識と共に解説します。
データベース編集には VBA のプログラムの中でデータベースのプログラム言語である SQL 言語 を使わないといけません。ですから、最初は SQL言語の基本から解説します。
では、早速いってみましょう！
SQL言語とデータベースの4つの基本操作
実はデータベースの基本的な操作は たった4つ しかありません。データを 作る・読み出す・更新する・削除する です。これは CRUD と呼ばれていて、Create・Read・Update・Delete の頭文字になっています。
このデータベースの操作はSQL言語で行います。それぞれの基本コマンドは以下の通りになっています。
データを作る（INSERT文）
INSERT INTO [テーブル名] ([列名1], [列名2],...) VALUES ([値1], [値2],...);データ作成にはデータ挿入を意味する INSERT というコマンドで行います。
指定したテーブルとその列名に対して、それぞれ値を登録するという内容です。
データを読み出す（SELECT文）
SELECT [列名1],[列名2],… FROM [テーブル名] WHERE [列名3]=[値3], [列名4] LIKE‘%ABC%’,…読み出しは「選択」を意味する SELECT というコマンドで行います。
SELECT文はテーブルの結合や、副問い合わせなど様々なテクニックがありますが、ここでは最もシンプルな絞り込みを紹介します。
WHERE というキーワードの後に絞り込み条件を指定しています。特に LIKE はワイルドカード（%）が使えるコマンドで、例では ABC を含むデータを検索します。
データを更新する（UPDATE文）
UPDATE [テーブル名] SET [列名1]=[値1],[列名2]=[値2],… WHERE [id]=3データの更新はそのままの意味の UPDATE というコマンドで行います。
このコマンドを使うには1点注意が必要です。例では WHERE で id が3のデータに絞っていますが、このように データを絞り込まないと全てのデータが同じ値に書き変わってしまいます のでご注意ください。
データを削除する
DELETE FROM [テーブル名] WHERE [列名1]=[値1],[列名2]=[値2],…データの削除はそのままの意味の DELETE というコマンドで行います。
こちらの例もUPDATEと同じように WHERE キーワードで絞り込みをかけてデータを削除しています。絞り込まないと全てのデータが消えてしまいます のでご注意下さい。
SQL Server への接続
SQL Server はユーザーからのアクセスを細かく管理できるようになっており、接続する場合もユーザーの認証が必要です。
ユーザーの認証には２通りの方法がありますので、それぞれの方法を解説します。
SQL Server 認証
SQL Server 認証 とは SQL Server上でユーザー名、パスワードを管理するものです。ユーザー管理を他に依存しないため、SQL Server上で管理が必要で手間がかかります。
一方で、プログラムで扱う場合はユーザー名とパスワードを直接指定すれば認証ができるので単純で分かりやすいというメリットがあります。
この SQL Server 認証 での接続を VBA のコードで書くと以下のようになります。
Dim cn As Object
ServerName = "SERVER_NAME\SQLEXPRESS,1433" ' サーバー名／インスタンス名,ポート番号
dbName = "TEST_DB" ' データベース名
USER_ID = "test-user" ' ユーザーID
PASSWD = "foobaa" ' パスワード
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB" & _
";Data Source=" & ServerName & _
";Initial Catalog=" & dbName & _
";user id=" & USER_ID & _
";password=" & PASSWORD- サーバー名 はサーバーのコンピュータ名、もしくは IPアドレス を入力します。
- インスタンス名 は SQL Server 上で作成したものを指定します。Express版 をインストールした場合はデフォルトでは SQLEXPRESS になっています。
- ポート番号 はネットワーク環境によっては指定が必要です。SQL Server のデフォルトのポートは 1433 です。
Windows認証
Windows認証は会社などの組織のドメイン（Active Directory）で管理されているユーザー認証を使うものになります。
このWindows認証を使う場合、ユーザーはログインする際に既に認証が完了しているので、ここで改めてユーザーIDやパスワードの確認をする必要が無く、スムーズに SQL Server に接続が可能です。
この Windows 認証 での接続を VBA のコードで書くと以下のようになります。
Dim cn As Object
ServerName = "SERVER_NAME\SQLEXPRESS,1433" ' サーバー名／インスタンス名,ポート番号
dbName = "TEST_DB" ' データベース名
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB" & _
";Data Source=" & ServerName & _
";Initial Catalog=" & dbName & _
";Integrated Security=SSPI "- ユーザーIDやパスワードの記入が不要でコードがシンプルになっています。
- 最後の行の Integrated Security=SSPI の部分で Windows認証を指定しています。
SQL Server への接続まとめ
以上で SQL Server 認証 や Windows 認証 で SQL Server との接続ができるようになりました。
コードの中で cn というオブジェクトが実際に SQL Server と接続したオブジェクトになります。
続いてこの cn オブジェクト を使って SQL Server に SQLのコマンドを送信します。
SQL Server にコマンドを送信
SQL Server のデータ編集には先ほど紹介した SQL 言語 のコマンドのうち、INSERT, UPDATE, DELETE を使います。
コードは単純で、以下のサンプルのようにSQLのコマンドの文字列を作成してそれを cn オブジェクト から SQL Server に送信するだけでOKです。
sqlcom = "insert into Table_1 ([日付], [店舗名], [売上]) values ('2023-01-01', 'Tokyo', '100')" ' SQLのコマンドの文字列を作成
cn.Execute(sqlcom) ' SQL Server にコマンドを送信SQL コマンドの処理速度を向上させる方法
上のサンプルでは１つの SQLのコマンドを送信するのみでしたが、送信するデータが 1000件 以上 と多くなってくると動作が遅くなるという問題が発生します。
この問題の解決策は、１度に多くの SQL コマンドを送信する という方法です。
下の図のように、何回も SQL コマンドを送信すると処理スピードが落ちてしまいますが、１度に多くの SQL コマンドを送信するとすんなり処理が終わります。
しかし、１度に送信する SQL コマンドの数には限界があるので、上限を 500個 くらいに設定しておくと安全のようです。
SQL コマンドをまとめるには次のように ;（セミコロン） をコマンドの区切りに入れて文字列をつなげていきます。
VBA でプログラムを組む場合は、繰り返し処理に入れて文字列を次々と繋いでいく処理をすると良いでしょう。更に、コマンドが 500個 たまったら cn オブジェクト から SQL Server にコマンドを送信するようにしておく感じです。
insert into Table_1 ([日付], [店舗名], [売上]) values ('2023-01-01', 'Tokyo', '100');
insert into Table_1 ([日付], [店舗名], [売上]) values ('2023-02-02', 'Nagoya', '200');
insert into Table_1 ([日付], [店舗名], [売上]) values ('2023-04-03', 'Osaka', '330');
insert into Table_1 ([日付], [店舗名], [売上]) values ('2023-06-04', 'Kyoto', '440');
insert into Table_1 ([日付], [店舗名], [売上]) values ('2023-09-05', 'Hiroshima', '560');
...INSERT文の場合の別解
INSERT文の場合は、先の例のように何度も INSERT文 を作らずに、値の部分だけを連続して繋ぐという構文もありますので、こちらの方が更に速くなります。
この場合の SQL コマンドは次のようになります。更なるスピードアップを求める場合には活用してみて下さい。
insert into Table_1 ([日付], [店舗名], [売上]) values
('2023-01-01', 'Tokyo', '100'), /* データ部分のみ繰り返し */
('2023-02-02', 'Nagoya', '200'),
('2023-04-03', 'Osaka', '330'),
('2023-06-04', 'Kyoto', '440'),
('2023-09-05', 'Hiroshima', '560'),
...まとめ
Excel の Power Query により データベースからデータを簡単に取得することはできますが、データベースをより活用していくためには Excel からデータベース上のデータを編集できるようにする必要があります。
この記事では VBA により SQL Server のデータを編集する基本コードと処理速度を向上させる方法についてご紹介しました。
Excel と SQL Server を組み合わせて活用することでより高度なデータ処理の仕組みを構築することが可能です。
今回紹介したデータベース上のデータ編集の方法を活用して、Excel と SQL Server との間で双方向のデータのやりとりができるようにしてデータ処理の仕組み構築にトライしてみて下さい。
このブログではデータ処理に関するノウハウを色々とご紹介していますので、他の記事も興味があれば是非ご覧になって下さい。
