ExcelマクロVBA技術解説 | ADO(ActiveX Data Objects)の使い方の要点 | Excelマクロの問題点と解決策、エクセルVBAの技術的解説



最終更新日:2016-10-30

ADO(ActiveX Data Objects)の使い方の要点

ADOはMicrosoftが提供するデータベースアクセスのためのソフトウェア部品です、

OLE DBをActiveXコントロールの形で使えるようにしたプログラミングインターフェースになります、

ここでは、ADOを使用したデータベースへの接続方法を解説します。


扱えるデータベースに特段の制限はありません。

対応するドライバーや、プロバイダが使用可能であれば、どんなDBでも扱えます。

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

CSV
Excel
Access
MySql
SQLServer
Oracle

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


SQL(SQL:Structured Query Language)

ADOには、
レコードの追加・削除・検索・抽出・並べ替えなどの基本的な操作が用意されています。
この操作を記述するのが、SQL文です。
VBAでのSQLの基礎(SQL:Structured Query Language)

SQL文を書くことで、
複数のテーブルから、必要なデータセットを自由に抽出、また更新、追加する事ができます。


ADOを使う準備

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

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

基本的には、事前バインディングを使ってマクロを作成します。
入力候補に、オブシェクトのメンバーが表示されますので、マクロを書く際に役に立ちます。


以下では、DBごとにサンプルコードを掲載します。
自身の備忘録も兼ねています。

CSV

Sub ReadCsv()
  Dim objCn As New ADODB.Connection
  Dim objRS As ADODB.Recordset
  Dim strSQL As String
  With objCn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Properties("Extended Properties") = "Text;HDR=NO"
    .Open ThisWorkbook.Path & "\"
  End With
  
  strSQL = ""
  strSQL = strSQL & " SELECT *"
  strSQL = strSQL & " FROM"
  strSQL = strSQL & " CSVTEST.csv"
  
  Set objRS = New ADODB.Recordset
  Set objRS = objCn.Execute(strSQL)
  
  With Worksheets("出力シート")
    .UsedRange.ClearContents
    .Range("A1").CopyFromRecordset objRS
  End With
  
  objCn.Close
  Set objRS = Nothing
  Set objCn = Nothing
End Sub


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


Excel

With objCn
  .Provider = "Microsoft.ACE.OLEDB.12.0"
  .Properties("Extended Properties") = "Excel 12.0"
  .Open ThisWorkbook.Path & "\TEST.xlsx"
End With


Access

strDB = ThisWorkbook.Path & "\sample.accdb"
With cn
  .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB
  .Open
End With


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

MySql

"Driver={MySQL ODBC 5.1 DRIVER}; SERVER=サーバー名(またはIPアドレス); DATABASE=DB名; USER=ユーザーID; PASSWORD=パスワード;"
ドライバーは、
"MySQL ODBC 3.51 DRIVER"
"MySQL ODBC 4.1 DRIVER"
"MySQL ODBC 4.2 DRIVER"
"MySQL ODBC 5.1 DRIVER"
"MySQL ODBC 5.2 DRIVER"
"MySQL ODBC 5.3 DRIVER"
等、PCにインストールされているドライバーを指定します。


SQLServer

"Provider=SQLOLEDB;Data Source=サーバー名(またはIPアドレス);Initial Catalog=DB名;user id=ユーザーID;password=パスワード"


Oracle

"Provider=OraOLEDB.Oracle;Data Source=リスナー名;User ID=ユーザーID;Password=パスワード;"

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


DBに依存しない方法としては、ODBCを使う事も出来ます。

ODBCを使う場合

"DSN=ODBC名;UID=ユーザーID;PWD=パスワード;"

ODBCを事前に作成しておきますが、ODBCの32bitと64bitに注意してください。
※OSが64bitのコントロールパネルにあるODBCは64bit用です。
 32bit用の設定は、C:\Windows\SysWOW64\odbcad32.exe
 VBAで使用する場合は、32bit用のODBCを使用してください。


コピペでここに貼付けているので、原文のコードによて大文字小文字が混在しています。
最後の;とかは、合っても無くても動きますが、書いておいた方が良いとは思います。

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

データを更新・追加する場合は、nullに注意してください。
Excelの空欄セルをデータとして使う時に、0バイト文字なのかnullなのか、DB仕様と合わせて記述に気を付けます。

更新・追加する場合は、トランザクション処理は必須だと思ってください。
さらに、データが多い時は、マルチプルインサートにすることを考えましょう。




同じテーマ「ExcelマクロVBA技術解説」の記事

Dictionary(ディクショナリー)連想配列の使い方について
Dictionary(ディクショナリー)のパフォーマンスについて
VBAでのInternetExplorer自動操作
VBAでのSQLの基礎(SQL:Structured Query Language)
VBAで正規表現を利用する(RegExp)
VBAでメール送信する(CDO:Microsoft Collaboration Data Objects)
VBAでのOutlook自動操作

新着記事 ・・・新着記事一覧を見る

SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)

アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
4.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
7.定数と型宣言文字(Const)|ExcelマクロVBA入門
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
10.CSVの読み込み方法|ExcelマクロVBAサンプル集



  • >
  • >
  • >
  • ADO(ActiveX Data Objects)の使い方の要点

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。

    ↑ PAGE TOP