VBからmdb, Mysql, Excel, Textへアクセスする方法

VBからJet Database Engineにアクセス

参照設定から「Microsoft ActiveX Data Objects 2.1 Library」 を追加。

ADOやODBCはJet Database Engine(MDB)を利用するためのミドルウェアです。

MS-Access 形式の MDB ファイルの実体は Jet Database Engine(Microsoft社が開発したリレーショナルデータベースエンジン)ファイル、つまり単なるデータ格納用のコンテナであって、GUI を提供する Microsoft Access アプリケーションとは、独立した別個のファイルです。

つまりMDB ファイルは、MS-Access がインストールされていない環境下でも使用することは可能であるということを意味します。

MS-AccessがインストールされていないPCでExcel VBAでADOを使ってテーブル操作したり、MSのサイトからRuntimeをダウンロードして開くことも可能、しかもライセンスの制限に抵触しません。

Runtimeとは「アプリケーションの開発・実行の両方の機能を備えたソフトウェアから、開発の機能を省き、実効の機能のみを取り出したプログラム」のことです。つまり「実行環境 without 開発環境」ですな。

メモ帳はテキストファイルを編集するためのユーティリティだけれど、別にメモ帳だけがテキストファイルを編集できる唯一のアプリケーションというわけではない、というのと全く同じ理屈です。

システムからDB(RDB)への接続はADO接続かODBC接続かのどちらかがほとんどですが両者の違いは

  • ADOはOLEDBをActiveXコントロールとして操作するためのインターフェース
  • ODBCはOLEDBのうちRDBを提供するインターフェース

OLEDBの中にODBCプロバイダがあってRDBへ接続してくれるが、開発者はADOのインターフェースでOLEDBにアクセスできる。

Cikarangまで「開発者⇒ODBC⇒OLE(RDB・Excelなど)」という下道で行くと3時間はかかるが、「開発者⇒ADO⇒OLE(RDB・Excelなど)」というTol(高速道路)に乗ると1時間半で着くのと同じですw。

ちなみに接続オブジェクトとレコードセットオブジェクトの生成方法だがどれでもいいと思います。

CreateObject関数を使って新規オブジェクト変数を生成し、Setでオブジェクトへの参照をオブジェクト変数に代入します。

Newを使ったオブジェクト参照の代入は、SetでNewを使って新しくオブジェクトを作成し、オブジェクト変数にオブジェクトの参照を代入します。

VBからMySQLにアクセス

PCがWindows 64bit版でも開発環境であるVB6は32bitなのでMySQL ODBC Connectorも32bit版を使用します。

ADODB接続の場合は関係ないですが、DSN接続をする場合に32bit版のODBCドライバを64bit版Winにインストールすると、コントロールパネルからODBC設定画面を起動してもインストールしたはずのMySQL5.1ドライバが表示されずあせります。よってコマンドプロンプトから以下のコマンドで32bit版のODBCデータソースアドミニストレーター画面を表示させDSN設定を行います。

  • %windir%\SysWOW64\odbcad32.exe

DSN接続の場合

ADODB接続の場合

VBからExcelにアクセス

参照設定からMicrosoft Excel 12.0 Object Libraryを追加。

ちなみにブックオブジェクト生成の際のファイル名(フルパス)を変数から代入する場合、拡張子を削除してあげないと何故か取得できません。ただし上例のように固定パスを文字列で代入する場合は問題ないです。

InStrRevでピリオドの位置を取得し拡張子を除外します。

ワークシート名の取得と追加は以下のとおり。

VBからテキストファイル(CSV)にアクセス

参照設定からMicrosoft Scripting Runtimeを追加。FileSystemObjectはWindowsでファイルシステムを簡単に扱えるようにするためのコンポーネントです。

OLE DB関連用語

DBアクセス部分はコピペ中心でプログラムを書いていますが、ADOはいいとしてもテキストファイルやExcelを操作する必要が出てきてそろそろ限界を感じてきたのでここでちょっとおさらい。DBアクセスするまでのミドルウェアやコンポーネントの関係を明確にする画期的例えを発見。DBアクセスはShipperからConsigneeに荷物を送る過程に似ていますな。

oledb

開発アプリケーションプログラム(Shipper)からRDBやExcel, Texfile(Consignee)にアクセスするにはOLE DBデータプロバイダーという仲介人(Forwarder)に手続きをお願いするのだが、直接OLE DBとやりとりするのはややこしい書類手続きなど煩わしいのでADO(カーゴ業者)にやってもらう。

OLE DB(Object Linking and Embedding DataBase)
Microsoft社によって開発された、データベースの種類によらず統一的な手法でデータベースにアクセスするためのプログラミングインターフェース。
つまりDB(Consignee)<< OLE DBはCOMコンポーネント(プログラム部品)の集合体として実現され、どんなプログラミング言語からでも利用することができるようになっている。

OLE DB は概念的に「コンシューマ (consumer)」と「プロバイダ(provider)」に分けられる。コンシューマとはデータにアクセスする必要のあるアプリケーションであり、プロバイダはインタフェースを実装したソフトウェアコンポーネントで、コンシューマにデータを提供する。

COMインターフェイス
COM (Component Object Model)で内部のデータや処理内容を公開する仕組みで、プラグイン(ActiveX DLLファイル)やオートメーションクライアント(Standard EXEファイル)からアプリケーションが公開している機能にアクセスしプラグインを開発・公開できるようになっている。

ADO(ActiveX Data Object)とはOLE DBプロバイダを介してデータベースにアクセスするためのコンポーネント。OLE DBをActiveXコントロールの形で使えるようにしたプログラミングインターフェース。ADOを利用してVBからテーブルレコードを直接操作できる。

ActiveXコントロールは従来OLEコントロールと呼ばれていた技術にインターネットに対応するための拡張を施したもの。ActiveXコントロールはネットワークを通じてWebサーバからダウンロードされ、同社のWebブラウザであるInternet Explorerに機能を追加する形で使用される。