SQL入門
データベースに接続/切断

SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説
公開日: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に接続する文字列で、間違っている場合はエラーになります。

VBA マクロ SQL ADO

ただし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\"に新たにファイルができることも確認しておきましょう。

データベースに接続/切断の最後に

今回は単純にデータベースに接続する部分のみとなっています。
SQL入門では、今後このクラスを使うことを前提にしたVBAサンプルを掲載していきます。
いろいろなSQL発行と結果の受け取り方によって、このクラスも機能拡張して行くことになります。



同じテーマ「SQL入門」の記事

SQLiteのインストール
データベースに接続/切断
テーブルの作成/削除(CREATE TABLE,DROP TABLE)
テーブル名変更と列追加(ALTER TABLE)とテーブル自動作成
データの挿入(INSERT)と全削除
VBAクラスの全コード:データの挿入
データの挿入:バルクインサート
データの取得:条件指定(SELECT,WHERE)
VBAクラスの全コード:データの取得
データの取得:集約集計、並べ替え(DISTINCT,GROUP,HAVING,ORDER)
SQL関数と演算子


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

TRIMRANGE関数(セル範囲をトリム:端の空白セルを除外)|エクセル入門(2024-08-30)
正規表現関数(REGEXTEST,REGEXREPLACE,REGEXEXTRACT)|エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.セルのクリア(Clear,ClearContents)|VBA入門
8.メッセージボックス(MsgBox関数)|VBA入門
9.条件分岐(Select Case)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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


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