VBA

【VBAでDB接続】

ADOはMicrosoftが提供するデータベースアクセスのためのソフトウェア部品です。
OLE DBをActiveXコントロールの形で使えるようにしたプログラミングインターフェースになります。
ここでは、ADOを使用したデータベースへの接続方法を解説します。

扱えるデータベースに特段の制限はありません。
対応するドライバーや、プロバイダが使用可能であれば、どんなDBでも扱えます。

データベースの種類

本ページで記載するデータベースは、

  • CSV
  • Excel
  • Access
  • MySql
  • SQLServer
  • Oracle

以上になります。
CSVもExcelもデータベースとして扱えます。
ただし、データがデータベース形式になっていることが前提です。
(各列に見出しがあり、列見出しの下に1行1件のデータが並んでいること)

SQL(SQL:Structured Query Language)

ADOには、
レコードの追加・削除・検索・抽出・並べ替えなどの基本的な操作が用意されています。
この操作を記述するのが、SQL文です。
SQL(StructuredQueryLanguage:構造化問い合わせ言語)は、データベースの定義や表の操作を行う言語です。データ定義言語であるDDL(datadescriptionlanguage)と データ操作言語であるDML(datamanipulationlanguage)に分けられます。
SQL文を書くことで、
複数のテーブルから、必要なデータセットを自由に抽出、また更新、追加する事ができます。

ADOを使う準備

VBE(VBAの編集画面)で、
ツール→参照
ここで、
Microsoft ActiveX Data Objects 2.X Library
または
Microsoft ActiveX Data Objects 6.1 Library
を追加して下さい。
オブジェクトのバージョンは各種ありますが、現在なら2.8が無難です。
このように、事前に参照設定することを、事前バインディングとして、

事前に参照設定をしない方法もあります。
CreateObject関数を使います。
この方法は、
実行時バインディングまたは遅延バインディングといいます。

どちらが良いかは場合によりけりなので、割愛しますが、環境に依存しないという意味では、CreateObject関数を使用
速度を気にするなら、事前バインディングが良いと思います。

DBごとにサンプルコードを掲載します。
私の備忘録も兼ねていますので、少し読みにくいかも知れませんが、ご了承ください。

CSV


以下、
With objCn
  この部分の違いだけですので、ここだけを掲載します。
End With


Excel

Access

ここからは、
.ConnectionStringに入れる文字列の違いになります。

MySql

ドライバーは、

  • “MySQL ODBC 5.51 DRIVER”
  • “MySQL ODBC 6.1 DRIVER”
  • “MySQL ODBC 6.2 DRIVER”
  • “MySQL ODBC 7.1 DRIVER”
  • “MySQL ODBC 7.2 DRIVER”
  • “MySQL ODBC 8.0 DRIVER”


等、PCにインストールされているドライバーを指定します。

会社で複数人が使用するブックの場合、使用するPC全て同じドライバーが必要です。

SQLServer

Oracle


オラクルの場合は、Oracleクライアントのインストールが必要です。
そして、リスナーの登録をしておいて下さい。

ODBCを使う場合

DSNを事前に作成しておきますが、ODBCの32bitと64bitに注意してください。
※自身のExcelのバージョンに合わせる必要があります。
設定方法は、今後記事に書いていきます。

まとめ

全体を通しての注意点としては、
データに、‘シングルクォーテーションを含む場合は、
それをエスケープするために、‘シングルクォーテーションを2個重ねるようにします。

データを更新・追加する場合は、nullに注意してください。
Excelの空欄セルをデータとして使う時に、空文字なのかnullなのか明示的に指定してあげると良いでしょう。

業務ではDBのデータを利用することが多いと思います。
DBを便利に扱う方法、DBと連携して業務を効率化する方法を今後記事にしていきます。