データベースに接続/切断

公開日：2019-11-23　最終更新日：2020-03-27

データベースに接続/切断

VBAでSQL発行するにあたり、まずはSQLを実行するデータベースに接続する必要があります。
このSQL入門では、DB接続にはADOを使用します。

SQLの発行とSQLの実行という言い方の違い
クライアントでSQLを発行し、それを受け取ったDBサーバーがSQLを実行します。
記事内では、時に区別なく使ってしまう事も出てくるかもしれませんが、適宜ご読み分けてください。

SQLiteにADOで接続するVBA記述について解説します。
あわせて他のDBへ接続する場合も紹介しておきます。

ADOとは

ADOは、Microsoftが提供するデータベースアクセスのためのソフトウェア部品です。
Microsoft ActiveX Data Objects ライブラリを使用します。
OLE DBをActiveXコントロールの形で使えるようにしたプログラミングインターフェースになります。
扱えるデータベースに特段の制限はありません。

CSV
Excel
Access
SQLServer
Oracle
MySql
SQLite
・・・

対応するドライバー、プロバイダが使用可能であれば、いろいろなDBに接続できます。
ADOの扱い方については、以下で詳しく解説しています。

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

・データベースの種類・SQL（SQL:Structured Query Language）・ADOを使う準備・ADOでのDB接続方法・ADODB.Recordsetの取得方法・ADODBのレコードセットの扱い方・ADODBのトランザクション処理・ADODB.Commandの使い方・VBA100本ノックでの実践例・最後に注意点等

ADOを使う準備

VBEで参照設定します。
ツール→参照
ここで、
Microsoft ActiveX Data Objects 2.X Library
または
Microsoft ActiveX Data Objects 6.1 Library
これを参照設定に追加して下さい。
このように事前に参照設定することを、事前バインディングといいます。

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

SQL入門では、事前バインディングを使ってマクロVBAを作成します。
入力候補に、オブシェクトのメンバーが表示されますので、マクロVBAを書く際に便利です。
CreateObject関数を使う場合の記述は適宜併記していきます。

SQLiteに接続/切断


  
    
      Sub SQLite接続1()

　　Dim adoCon As New ADODB.Connection

      　　adoCon.Open "DRIVER=SQLite3 ODBC Driver;Database=C:\SQLite3\sample.db"

      　　'ここでSQLを発行します。

　　adoCon.Close

　　Set adoCon = Nothing

End Sub

これだけで接続できてしまいます。
"DRIVER=SQLite3 ODBC Driver;Database=C:\SQLite3\sample.db"
これがDBに接続する文字列で、間違っている場合はエラーになります。

ただしSQLiteの場合、フォルダが無ければエラーになりますが、
ファイルが存在しない場合は自動的に作成されてしまいます。
これはSQLiteだけの特種な挙動になります。
Accessであればファイルが無ければエラーになります。

上記VBAは以下のような記述もできます。


  
    
      Sub SQLite接続2()

　　Dim adoCon As ADODB.Connection

      　　Set adoCon = New ADODB.Connection

      　　adoCon.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=C:\SQLite3\sample.db"

      　　adoCon.Open
　　'ここでSQLを発行します。

　　adoCon.Close

　　Set adoCon = Nothing

End Sub

NewとConnectionStringの書き方の違いです。
VBAの動作としてはほぼ同じですので、どちらでも構いません。

参照設定しない実行時バインディングであれば、


  
    
      Sub SQLite接続3()

      　　Dim adoCon As Object

      　　Set adoCon = CreateObject("ADODB.Connection")

      　　adoCon.Open "DRIVER=SQLite3 ODBC Driver;Database=C:\SQLite3\sample.db"

      　　'ここでSQLを発行します。

      　　adoCon.Close

      　　Set adoCon = Nothing

End Sub

上記いずれの書き方を組み合わせても構いません。

データベースに接続できたかの確認

.Openでエラーにならなければ普通は正しく接続できていますが、
ちゃんと接続できているかどうか判定したい場合も出てきます。
それには、ADODB.ConnectionのStateプロパティを参照します。
Stateプロパティの値

定数	Value	説明
adStateClosed	0	オブジェクトが閉じられたことを示します。
adStateOpen	1	オブジェクトが開かれていることを示します。
adStateConnecting	2	オブジェクトが接続することを示します。
adStateExecuting	4	オブジェクトのコマンドが実行されていることを示します。
adStateFetching	8	オブジェクトの行が取得されていることを示します。


  
    
      Sub SQLite接続4()

　　Dim adoCon As New ADODB.Connection

　　adoCon.Open "DRIVER=SQLite3 ODBC Driver;Database=C:\SQLite3\sample.db"

　　Debug.Print adoCon.State

　　adoCon.Close

　　Debug.Print adoCon.State

　　Set adoCon = Nothing

End Sub

この実行結果は、
1
0
となります。
上記Stateプロパティの値と見比べてください。

他のデータベースに接続する場合の指定

他のデータベースに接続する場合の接続文字列の例を紹介しておきます。

Access


  
    
       "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=フルパス\sample.accdb"

Access2007以降の場合です。
2003以前の場合は、


  
    
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=フルパス\sample.accdb"

MySql


  
    
      "Driver=MySQL ODBC x.x DRIVER; SERVER=サーバー名(or IPアドレス); DATABASE=DB名;
      USER=ユーザーID; PASSWORD=パスワード;"

x.xはドライバーのバージョンになります。
PCにインストールされているドライバーを指定します。

SQLServer


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

Oracle


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

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

今後のためにクラス化しておく

今後SQLを書いていく上で、DB接続を毎回気にするのは面倒です。
DB接続部分については、別途SubまたはFunctionを用意しておきたいところです。
今回のSQL入門では、今後の事を考えてクラス化しておくことにしました。

VBAのクラスとは（Class,Property,Get,Let,Set）

・オブジェクトとは・オブジェクト指向とは・カプセル化・オブジェクト指向とカプセル化とクラス・クラスの必要性と利点・一般的なクラスに関する説明・クラスの比喩的説明・クラスの使い方・クラスを体験してみる・クラスの使用例・クラス入門の最後に

クラスモジュール：clsSQLite


  
    
      Option Explicit


'DB接続情報の定数

Private Const defConStr As String = "DRIVER=【DRIVER】;Database=【DATABASE】"

Private Const defDriver As String = "SQLite3 ODBC Driver"


'DB接続情報

Private pAdoCon As ADODB.Connection

Private pConStr As String

Private pDataBase As String

Private pDriver As String


'DB接続設定プロパティ

Public Property Set adoCon(arg As ADODB.Connection)

　　Set pAdoCon = arg

End Property

Public Property Get adoCon() As ADODB.Connection

　　Set adoCon = pAdoCon

End Property


Public Property Let ConStr(arg As String)

　　pConStr = arg

End Property

Public Property Get ConStr() As String

　　ConStr = pConStr

End Property


Public Property Let DataBase(arg As String)

　　pDataBase = arg

End Property

Public Property Get DataBase() As String

　　DataBase = pDataBase

End Property


Public Property Let Driver(arg As String)

　　pDriver = arg

End Property

Public Property Get Driver() As String

　　Driver = pDriver

End Property


'DB接続

Public Function dbOpen() As Boolean

　　On Error GoTo Err_Exit

　　dbOpen = True

　　　　

　　'既に接続してたら無視

　　If Not Me.adoCon Is Nothing Then

　　　　If Me.adoCon.State = ObjectStateEnum.adStateOpen Then

　　　　　　Exit Function

　　　　End If

　　End If

　　

　　'SQLiteに接続

　　Set Me.adoCon = New ADODB.Connection

　　Me.adoCon.Open getConStr

　　

　　Exit Function

　　

Err_Exit: '接続エラー

　　dbOpen = False

　　MsgBox "SQLite接続エラー" & vbLf & vbLf & Err.Description

End Function


'DB切断

Public Function dbClose() As Boolean

　　On Error GoTo Err_Exit

　　dbClose = True

　　

　　'既に切断されていたら無視

　　If Me.adoCon Is Nothing Then Exit Function

　　If Me.adoCon.State = ObjectStateEnum.adStateClosed Then Exit Function

　　

　　'SQLiteを切断

　　Me.adoCon.Close

　　

　　Exit Function

　　

Err_Exit:

　　dbClose = False

　　MsgBox "SQLite切断エラー" & vbLf & vbLf & Err.Description

End Function


'初期処理

Private Sub Class_Initialize()

　　Me.Driver = defDriver

　　Me.ConStr = defConStr

End Sub


'終了処理

Private Sub Class_Terminate()

　　Call Me.dbClose

End Sub


'DB接続文字列

Private Function getConStr() As String

　　getConStr = Me.ConStr

　　getConStr = Replace(getConStr, "【DRIVER】", Me.Driver)

　　getConStr = Replace(getConStr, "【DATABASE】", Me.DataBase)

      End Function

一つ一つについては、難しいものはありません。
今後の拡張を考慮して、直ぐに使わないプロパティも入れています。

このクラスはまだ試作段階ですので、今後改良を重ねていきます。
現時点（接続/切断）で必要な最低限の機能と若干の追加機能をいれた程度のものになります。
例えば、
エラー時の処理については単純にメッセージを出しているだけですが、今後機能拡張していく際に内容を追加変更していく必要が出てきます。

標準モジュールでの使い方


  
    
      Sub sample()

　　Dim clsDB As New clsSQLite

　　

      　　clsDB.DataBase = "C:\SQLite3\sample.db"

　　If Not clsDB.dbOpen Then

　　　　'接続できなかった時の処理

　　　　Set clsDB = Nothing

　　　　Exit Sub

　　End If

　　

　　'ここでSQLを発行します。

　　

　　If Not clsDB.dbClose Then

　　　　'切断できなかった時の処理

　　　　Set clsDB = Nothing

　　　　Exit Sub

　　End If

　　

　　Set clsDB = Nothing

End Sub

実際に動かして確認してみましょう。
"sample.db"を違う名称にして実行した時、"C:\SQLite3\"に新たにファイルができることも確認しておきましょう。