LoginSignup
89

More than 5 years have passed since last update.

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

Posted at

はじめに

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
89