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
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 WithAccess
Dim strDb As String
strDB = ThisWorkbook.Path & "\sample.accdb"
With objCn
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB
.Open
End Withここからは、
.ConnectionStringに入れる文字列の違いになります。
MySql
"Driver={MySQL ODBC 8.0 DRIVER}; SERVER=サーバー名(またはIPアドレス); DATABASE=DB名; USER=ユーザーID; PASSWORD=パスワード;"ドライバーは、
- “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
"Provider=SQLOLEDB;Data Source=サーバー名(またはIPアドレス);Initial Catalog=DB名;user id=ユーザーID;password=パスワード"Oracle
"Provider=OraOLEDB.Oracle;Data Source=リスナー名;User ID=ユーザーID;Password=パスワード;"
オラクルの場合は、Oracleクライアントのインストールが必要です。
そして、リスナーの登録をしておいて下さい。
ODBCを使う場合
"DSN=ODBC名;UID=ユーザーID;PWD=パスワード;"DSNを事前に作成しておきますが、ODBCの32bitと64bitに注意してください。
※自身のExcelのバージョンに合わせる必要があります。
設定方法は、今後記事に書いていきます。
全体を通しての注意点としては、
データに、‘シングルクォーテーションを含む場合は、
それをエスケープするために、‘シングルクォーテーションを2個重ねるようにします。
データを更新・追加する場合は、nullに注意してください。
Excelの空欄セルをデータとして使う時に、空文字なのかnullなのか明示的に指定してあげると良いでしょう。
業務ではDBのデータを利用することが多いと思います。
DBを便利に扱う方法、DBと連携して業務を効率化する方法を今後記事にしていきます。
