適材適所

パソコンができることはパソコンに。

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

Excel VBAからAccessを操作する

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

Accessに接続するためには、Accessを操作するためのAPIを使います。

APIはMicrosoftが用意してくれている、DAOやADOと呼ばれるものを使うことができます。

(現在、DAOの開発は終了しているらしいので、ADOを使った方がよろしいかと思われます)

ADOを使と、Accessとの接続だけでなく、ExcelやCSVにも同じような手順で接続することができる点が挙げられます。

ADOを使ってコードを素直に書くと、可読性があまりよくないコードになることが多々あります。

メインのコードの中に環境ごとに変更すべき文字列が出てきたり(ドライバーの指定)、データベースのopenやcloseを意識してし行う必要があるなど、本当に注力したい業務ロジック部分が見えづらくなるデメリットがあります。

加えて、コード内で行う手続きは毎回同じであることから、これらの面倒な部分をクラス押し込めてやることで、このADOを透過的に扱おう!!というのが今回のメイントピックです。

これから紹介するクラスを使えば、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の操作をSQLで行うことができるようになります。

実際の例を見てみましょう。

実際に動かしてみる

適当なフォルダに空の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とExcelの連携

巷にはExcelとAccessの連携をするための情報はあふれています。

その中の一つとして、「こんな手法もあるのね」というくらいの目で見て頂ければと思います。

こんな感じで使うことで、AcceessとExcelの連携が楽にできるようになるかなぁと思って作り、自分でもシコシコ使っている次第です。

次回は、これらのクラスを使ってもう少し実践的なプログラムを紹介したいと思います。

ここまでお読みいただき、ありがとうございました。

www.tekizai.net