【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を使う準備

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

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にインストールされているドライバーを指定します。

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のバージョンに合わせる必要があります。
設定方法は、今後記事に書いていきます。