適材適所

パソコン作業の自動化・効率化のための情報を発信するブログ(VBA,PowerShellなど)

VBAでAccessへの接続をSQLで簡単に行う_その1

Excel VBAからAccessを操作する

Excel VBAを使ってAccessのデータを操作したい、という場合のネタです。

Accessに接続するためには、当たり前ですがAccessが対応しているAPIを使います。DAOやADOと呼ばれるものです。現在、DAOの開発は終了しているらしいので、ADOを使った方がよろしいかと思われます。ADOとはMicrosoft社から提供されている、データベースを操作するためのAPIみたいなものです。

ADOを使うメリットとしてAccessとの接続と同じような手順でExcelやCSVにも接続することができ、相手が何であるかをそれほど意識しなくてよいという点が挙げられます。

そのままADOを使ってプログラムを書いてもいいのですが、手順が煩雑、コードの可読性が下がります。

ドライバーを指定したり、データベースをopenしたり、closeしたり。。。結構面倒です。

面倒なので、それをクラス内にカプセル化して、もっと透過的にAccessと連携したい!!ということで自作のクラスを紹介したいと思います。

ADOによる接続をカプセル化する実装

 二つのクラスによる実装です。

  • ConnectionFactory(Accessとの通信を作る)
  • ConnectionWrapper(実際に通信を行う)
'ConnectionFactory.cls
Option Explicit
'--------------------------------------
'参照設定
'Microsoft ActiveX Data Object RecordSet
'Microsoft Scripting Runtime
'Microsoft ActiveX Data Object
'---------------------------------------
'環境に合わせて接続先のDBを変更してください。2007以降であればこのまま、それより前の場合は「Microsoft.Jet.OLEDB.4.0」を指定。
Const ACPROVIDER As String = "Provider=Microsoft.Ace.OLEDB.12.0;"
Const ACDATASOURCE As String = "Data Source="

'accdb,mdb形式のAccessデータベースへ接続するためのConnectionを生成する
Public Function getAcConnection(myFilePath As String) As ADODB.Connection
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.ConnectionString = ACPROVIDER & ACDATASOURCE & myFilePath & ";"
    Set getAcConnection = con
    Set con = Nothing
End Function
'ConnectionWrapper.cls
Option Explicit
    '-------------------------------------------------------
    'ADODB.Connectionを使ってデータソースを扱うためのクラス。
    '各種手続きをラッピングする。
    '-------------------------------------------------------
    '参照設定
    'Microsoft ActiveX Data Object RecordSet
    'Microsoft Scripting Runtime
    'Microsoft ActiveX Data Object
    '---------------------------------------
    Private myConnection_ As New ADODB.Connection
    Private isOpen_ As Boolean

Public Sub setConnection(mycon As ADODB.Connection)
    Set myConnection_ = mycon
End Sub

'このメソッドを呼んだ場合、必ずcloseConnectionを呼び出す必要があります。
Public Sub openConnection()
    
    myConnection_.Open
    myConnection_.CursorLocation = 3 ' クライアントサイドカーソルに変更

    isOpen_ = True
End Sub

Function execute(sql As String) As ADODB.Recordset
    On Error GoTo catch
    
    Dim myCommand As Command: Set myCommand = New Command
    'CommandTextプロパティで実行可能なテキスト(例えばSQL)を定義する
    myCommand.CommandText = sql
    If isOpen_ = False Then
        openConnection
    End If
    Set myCommand.ActiveConnection = myConnection_
    Set execute = myCommand.execute
    
    Exit Function
catch:
    Me.closeConnection
    Err.Raise 1000, , "クエリ要求中にエラーが発生したため処理を中止します。" & vbNewLine & "---------" & vbNewLine & Err.Description & vbNewLine & "-----------"
End Function

Public Sub closeConnection()
    If Not (myConnection_ Is Nothing) Then
        If myConnection_.State <> 0 Then
            myConnection_.Close
            isOpen_ = False
        End If
    End If
End Sub

Public Function isOpen() As Boolean
    isOpen = isOpen_
End Function

Public Function beginTransaction() As Long
    If isOpen_ = False Then
        openConnection
    End If
    beginTransaction = myConnection_.BeginTrans
End Function

Public Sub commitTransaction()
    myConnection_.CommitTrans
End Sub

Public Sub rollback()
    myConnection_.RollbackTrans
End Sub

Private Sub Class_Terminate()
    closeConnection
End Sub

これらのクラスを使うことで、Excel VBAでAccessの操作をクエリにて行うことができるようになります。

では実際に動かしてみます。適当なフォルダに空のAccessファイルとExcelファイルを用意します。今回は「ADOサンプル.xlsm」と「Database.accdb」とします。

「ADOサンプル.xlsm」に上記の二つのクラスを作成して、次のコードを標準モジュールに書いて実行してみます。Excel VBAからDatabase.accdbに適当なテーブルを作成するサンプルです。

Option Explicit

'Database.accdbに「sample」というテーブルを作成する
Sub createNewTableSample()
    Dim cf As ConnectionFactory: Set cf = New ConnectionFactory
    Dim cw As ConnectionWrapper: Set cw = New ConnectionWrapper
    cw.setConnection cf.getAcConnection(ThisWorkbook.Path & "\" & "Database.accdb")
    Dim sql As String
    sql = "create table sample(id counter primary key,name char(10))"
    cw.execute sql
    Set cw = Nothing
    Set cf = Nothing
End Sub

実行すると、Accessに新しくidと名前というフィールドをもったsampleというテーブルが作成されました。

二つのクラスを使ってAccessへの接続を単純化してみました。今度はこのクラスを使ってもう少し実践的なプログラムを紹介したいと思います。

www.tekizai.net