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自動操作

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

メモの挿入・削除と改行文字|Google Apps Script入門(12月6日)
リンクの挿入・編集・削除|Google Apps Script入門(12月6日)
セルに数式を入れる|Google Apps Script入門(12月1日)
セルのコピー&各種ペースト|Google Apps Script入門(11月22日)
Twitter Bot 作成|Google Apps Script応用(11月6日)
Excel流の最終行の取得|Google Apps Script応用(11月6日)
方眼紙Excelが楽に入力できるVBA|ExcelマクロVBAサンプル集(11月5日)
「ポケモンを確実に見つける方法」をExcelで数学してみた|エクセル雑感(11月4日)
スプレッドシート(ブック)の作成・名前変更|Google Apps Script入門(11月4日)
シートの挿入・削除・名前変更|Google Apps Script入門(11月3日)

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

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



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

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


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



    ↑ PAGE TOP