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

Excel VBAからAccessを操作する

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

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

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

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

ADOは元々、アプリ間の連携をシームレスに行うことを目的に設計されているため、ADOを使うことで、Accessとの接続だけでなく、ExcelやCSVにも同じような手順で接続することができるメリットがあります。

しかし、ADOの機能をそのまま呼び出してコードを素直に書いてみると、私の経験上、あまり可読性のよくないコードになることが多々ありました。

ADOのコードとメインのコードがごちゃごちゃになりやすく、メインのロジックの中に環境ごとに変更すべき文字列が出てきたり（ドライバーの指定）、データベースのopenやcloseを特に意識してし行う必要があるなど、

本当に注力したい業務ロジック部分が見えづらくなり、保守性、可読性ともにに下がってしまうというデメリットがあります。

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

ということでADOのラッパークラスを作成しました。

ここで紹介するADOのラッパークラスを使えば、AccessとExcelの連携がさらに簡単になること請け合いです！

ADOによる接続をカプセル化(ラッピング)するクラス

以下の二つのクラスを実装しました。

• ConnectionFactory（Accessとの通信を作る）
• ConnectionWrapper（実際に通信を行う）

ConnectionFactoryでAccessへの接続をラッピングする

ConnectionFactoryクラスはプロパイダー文字列などの面倒な文字列の整形を担当します。

このクラスのgetAcConnectionメソッドはAccessへの接続文字列を生成し、ADODB.Connectionを返してくれます。

 

'ConnectionFactory.cls
'--------------------------------------
'参照設定
'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クラスでレコードの操作などカプセル(ラップ)する

Accessへ接続し、SQLの送信、ロールバックなどをカプセル化します。

 

'ConnectionWrapper.cls
    '-------------------------------------------------------
    '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で行うことができるようになります。

ExcelからAccessへ接続する

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

適当なフォルダに空のAccessファイルとExcelファイルを用意します。

今回は「ADOサンプル.xlsm」と「Database.accdb」とします。

「ADOサンプル.xlsm」に上記の二つのクラスを作成して、次のコードを標準モジュールに書いて実行してみます。

Excel VBAからDatabase.accdbに適当なテーブルを作成するサンプルです。

 

'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

コードを見てもらうと、ADO特有の長ったらしい接続文字列がなくなり、どのAccessに接続しているか、どんなSQLを投げているかが直感的にわかりやすくなりました。

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

レコードを追加する

「Database.accdb」に作ったsampleテーブルにレコードを追加するのサンプルコードです。

 

Option Explicit
Sub test()
    Dim cf As ConnectionFactory: Set cf = New ConnectionFactory
    Dim cw As ConnectionWrapper: Set cw = New ConnectionWrapper
    
    On Error GoTo catch
    '接続を確立
    cw.setConnection cf.getAcConnection(ThisWorkbook.Path & "\db.accdb")
    'トランザクションの開始
    cw.beginTransaction
    'レコード追加処理
    cw.execute "INSERT INTO sample (name) Values('tekizai')"
    'トランザクションのコミット
    cw.commitTransaction
    GoTo finally
catch:
    'ロールバック処理
    cw.rollback
finally:
    
End Sub

ちなみにidフィールドはcounterなので勝手に連番が入ります。

また、トランザクションを開始しないと、いきなりレコードが追加されます。

きっと普通にADOを使うよりコードはすっきりしている！！はず・・・。

終わりに

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

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

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