データベースに接続/切断
VBAでSQL発行するにあたり、まずはSQLを実行するデータベースに接続する必要があります。
このSQL入門では、DB接続にはADOを使用します。
クライアントでSQLを発行し、それを受け取ったDBサーバーがSQLを実行します。
記事内では、時に区別なく使ってしまう事も出てくるかもしれませんが、適宜ご読み分けてください。
SQLiteにADOで接続するVBA記述について解説します。
あわせて他のDBへ接続する場合も紹介しておきます。
ADOとは
Microsoft ActiveX Data Objects ライブラリを使用します。
OLE DBをActiveXコントロールの形で使えるようにしたプログラミングインターフェースになります。
扱えるデータベースに特段の制限はありません。
Excel
Access
SQLServer
Oracle
MySql
SQLite
・・・
ADOの扱い方については、以下で詳しく解説しています。
ADOを使う準備
ツール→参照
ここで、
Microsoft ActiveX Data Objects 2.X Library
または
Microsoft ActiveX Data Objects 6.1 Library
これを参照設定に追加して下さい。
このように事前に参照設定することを、事前バインディングといいます。
実行時バインディングまたは遅延バインディングといいます。
入力候補に、オブシェクトのメンバーが表示されますので、マクロ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だけの特種な挙動になります。
Accessであればファイルが無ければエラーになります。
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
上記いずれの書き方を組み合わせても構いません。
データベースに接続できたかの確認
ちゃんと接続できているかどうか判定したい場合も出てきます。
それには、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プロパティの値と見比べてください。
他のデータベースに接続する場合の指定
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=フルパス\sample.accdb"
Access2007以降の場合です。
2003以前の場合は、
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=フルパス\sample.accdb"
"Driver=MySQL ODBC x.x DRIVER; SERVER=サーバー名(or IPアドレス); DATABASE=DB名;
USER=ユーザーID; PASSWORD=パスワード;"
x.xはドライバーのバージョンになります。
PCにインストールされているドライバーを指定します。
"Provider=SQLOLEDB;Data Source=サーバー名(or IPアドレス);Initial Catalog=DB名;user
id=ユーザーID;password=パスワード"
"Provider=OraOLEDB.Oracle;Data Source=リスナー名;User ID=ユーザーID;Password=パスワード;"
オラクルの場合は、Oracleクライアントのインストールが必要です。
そして、リスナーの登録をしておいて下さい。
今後のためにクラス化しておく
DB接続部分については、別途SubまたはFunctionを用意しておきたいところです。
今回のSQL入門では、今後の事を考えてクラス化しておくことにしました。
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コードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。