Excel VBAとSQL Serverの連携を楽にしたい

#はじめに
Microsoft SQL Server Express Editionは、利用できるDBサイズやメモリ容量、プロセッサ数に制限はあるものの、Management StudioというGUIベースで使える管理・開発ツールもあり、一般のご家庭や個人が導入するにはかなり敷居の低いDBMSです。

Excelとの相性もよく、導入するとデータ管理とビジネスロジックはSQL Serverに、プレゼンテーションはExcelにと切り分けたくなります。

でもいちいち接続文字列とか書くのダルいですよね？というわけで、ExcelからSQL文を発行したりするのをもっと楽にしよう、というのがこの記事の趣旨です。

#結論:コンポーネント化しよう

SQL Serverの利用に必要な機能をコンポーネント化してアドインにしておき、アクセスする際はそのアドインを利用するようにしましょう。コンポーネントに作りこむメソッドは以下のとおりです

接続・切断とクエリ等の実行用に3つ

• connect()
• disconnect()
• execute()

トランザクション処理用に次の3つも作っておきます

• BeginTransaction()
• CommitTransaction()
• RollbackTransaction()

##Databaseクラス

Database.cls

Option Explicit

Private mCon As ADODB.Connection

' Connectionオブジェクトを生成
Public Sub connect()
    Dim cn As String
    
        '+ *** 接続文字列 ***
        ' サーバのIP/ホスト名とDBインスタンス名は適当に書き換えられたし
        cn = _
            "Provider=SQLOLEDB;" & _
            "Network Library=DBMSSOCN; " & _
            "Trusted_connection=yes; " & _
            "Data Source=127.0.0.1,1433; " & _
            "Server=127.0.0.1\SQLSERVER; "

    Set mCon = New ADODB.Connection
    
    mCon.CursorLocation = adUseClient
    mCon.Open cn
End Sub

' データベースへの接続を解除する
Public Sub disconnect()
    mCon.Close
    Set mCon = Nothing
End Sub

' 引数のSQL文を実行し、ADODB.Recordsetを返す
Public Function execute(sql As String) As ADODB.Recordset
    Dim rs As New ADODB.Recordset

    ' タイムアウト設定 (20分)
    mCon.CommandTimeout = 60 * 20

    ' 処理された行数を示すメッセージが結果セットの一部として返されないようにする
    mCon.execute ("SET NOCOUNT ON")

    ' 警告メッセージが結果セットの一部として返されないようにする
    mCon.execute ("SET ANSI_WARNINGS OFF")
    
    ' オーバーフローおよび0除算時にはNULLを返す
    mCon.execute ("SET ARITHABORT OFF")


    rs.Open sql, mCon, adOpenStatic, adLockBatchOptimistic

    Do
        ' レコードの操作ができるオブジェクト若しくは次のRecordSetがとれず、コネクションが空になった場合終了
        If rs.State = adStateOpen Or rs.ActiveConnection Is Nothing Then
            Exit Do
        End If
        Set rs = rs.NextRecordset()
    Loop
    
    Set execute = rs
    
    ' 設定OFF
    mCon.execute ("SET NOCOUNT OFF")
    mCon.execute ("SET ANSI_WARNINGS ON")
    mCon.execute ("SET ARITHABORT ON")    
End Function

' トランザクションを開始する
Public Sub BeginTransaction()
    mCon.BeginTrans
End Sub

' トランザクションをコミットする
Public Sub CommitTransaction()
    mCon.CommitTrans
End Sub

' トランザクションをロールバックする
Public Sub RollbackTransaction()
    mCon.RollbackTrans
End Sub

##connect()

接続文字列を使ってConnectionオブジェクトを生成するメソッドです。後続のexecute()などによる処理を行う前に、このメソッドで接続を開いておきます。

"Trusted_connection=yes"はDB認証にWindows認証を使う場合に使用します。クライアントマシンとサーバで同じIDでログインしている必要がありますが、難しい場合にはSQL Server認証として、独自のID/PWを設定し利用できるようにします。その場合"User ID=sa; Password=********; "などのようにします。

CursorLocationとは、カーソル（クエリの結果集合を一時的に蓄えておくための仮想的な作業領域）をどこに置くかというオプションで、adUseServerとadUseClientの2種類があります。それぞれの特徴は以下の通りですが、通常はadUseClientで良いと思います。

• adUseServer ... サーバ側にカーソルを置いて作業します。サーバ上でテーブルを占有しながら作業するようなイメージになるため、作業中のレコードに対する他ユーザーの更新を検知できたり、クライアントとのレコード送受信がないので大量(百万行～)データの処理に強いという利点がありますが、サーバとの接続が生まれるためサーバ負荷の原因になる、サーバ上で大量のレコードがロックされるとパフォーマンスが落ちるといった欠点もあります。なおADO.NETではサーバカーソル自体無くなった（らしい）
• adUseClient ... サーバから取得したカーソルをクライアントマシンに置いて作業します。サーバとの接続がないためサーバ負荷が少なく、レコードセットに対する操作がリッチ（フィルタや並べ替えなどができる）という利点があります。

##disconnect()

データベースへの接続を解除します。ConnectionオブジェクトをCloseして破棄しているだけです。

##execute()

任意のSQL文を実行しRecordsetを返します。タイムアウト設定等、クエリ実行の際のパラメータもここで合わせて設定します。

rs.Openの部分でRecordsetを開いていますが、CursorLocationがadUseClientの場合はCursorTypeは強制的にadOpenStaticになります。
LockTypeは、CursorLocation = adUseClientでは全部で3つのLockTypeを利用することができます。例ではRecordset経由で更新可能なadBatchOptimisticを使用していますが、業務アプリなんかでユーザにRecordsetを触らせるときはadLockReadOnlyにしておいた方がよいかもしれません。
なお、このへんの情報は下のblogがとても参考になります。
ADO 時代の非接続型データアクセス

• adLockReadOnly ... 読み取り専用（Recordset経由での更新ができない）
• adLockOptimistic ... 更新時にレコード単位で共有ロックされる
• adLockBatchOptimistic ... 更新時に複数レコードをバッチ処理する

##トランザクション関連メソッド

トランザクションを開始したい時点でBeginTransaction()、コミットしたい時点でCommitTransaction()を呼びます。RollbackTransaction()はエラー処理の部分に仕込んでおくと良いと思います。

##インスタンス生成用のFactoryメソッド

DatabaseクラスのInstancingは外部から利用可能にするためPublicNotCreatbleにしておきます。そしてインスタンス生成のために標準モジュールでcreateメソッドを作っておきます。インスタンスがほしい時はcreate()からもらいます。

DatabaseFactory.bas

Option Explicit

' インスタンスを生成する
Public Function create() As DBAccessComponent.Database
    Set create = New DBAccessComponent.Database
End Function

#使ってみる

作ったアドインをDBAccessComponent.xlamという名前で保存したとします。他のブックから利用するためにはアドインとADOコンポーネントを参照設定する必要があります。ADOコンポーネントは「Microsoft ActiveX Data Object x.x」という名前で、バージョンは別になんでもいいようです。（2.8とか使ってます）

sample.bas

Option Explicit

Sub test()
    Dim db As DBAccessComponent.Database
    Dim rs As ADODB.Recordset
    Dim sql As String
    
    Set db = DBAccessComponent.DatabaseFactory.Create
    
    sql = "SELECT * FROM tablename"
    
    db.Connect
    
    Set rs = db.Execute(sql)
    Range("A1").CopyFromRecordset rs

    rs.Close
    Set rs = Nothing
    db.Disconnect
    Set db = Nothing

End Sub

といった感じでSQL Serverに接続してSQL文を発行することができるようになります。受け取ったRecordsetにAddNewしてUpdateBatchとかするとExcel経由で大量データの更新も速くできます。

GitHub: sawadyrr5/DBAccessComponent