VBAからSQL Serverに接続する方法(Microsoft OLE DB Provider for SQL Server)

VBAから業務システムなどに使用しているデータベースに接続し、データを抽出することができます。

定期的に作成する資料などは、VBAでデータ取得処理を自動化し、ボタン一つで最新情報に更新することが可能になるため、資料作成にかかる時間を大幅に短縮することが期待できます。

この記事ではExcel VBAからSQL Serverに接続してデータを取得する方法を記載します。

参照設定

ADO(ActiveX Data Objects)を使用することで、エクセルなどのOfficeソフトからSQL Serverに接続することができます。

最初にADOを使用するための参照設定をExcelファイルに設定しましょう。

VBEを起動して[ツール]-[参照設定]の順でクリックします。

参照可能なライブラリファイルから「Microsoft ActiveX Data Objects X.X Library」を選択します。

※ADOのバージョンはOSなどの環境によって異なりますので、ご使用の環境に合わせて読みかえてください。

SQL Serverデータベースへの接続文字列

SQL Serverへの認証方法は、Windows認証とSQL Server認証の2種類があり、認証方法によって接続文字列が異なります。

'Windows認証
"Provider=SQLOLEDB;Data Source=サーバ名;Initial Catalog=データベース名;Trusted_Connection=Yes;"

'SQL Server認証
"Provider=SQLOLEDB;Data Source=サーバ名;Initial Catalog=データベース名;UID=ユーザID;PWD=パスワード;"

サンプルプログラム

SQL Serverに接続してデータを取得、切断をするサンプルプログラムを記載します。

Option Explicit

Private Const PROVIDER As String = "SQLOLEDB"
Private Const DATA_SOURCE As String	= "localhost"   'サーバ名
Private Const DATABASE As String = "dbname"         'データベース名

'SQL Server認証で接続する場合
Private Const USER_ID As String = "UID=user"        'ユーザID
Private Const PASSWORD As String = "password"       'ユーザパスワード

Sub sample()
    On Error GoTo ERR_HANDLER
    Dim strSQL As String
    Dim i As Long

    '--------------------------------
    ' データベース接続				
    '--------------------------------
    Dim cn As New ADODB.Connection

    'Windows認証で接続する場合
    cn.ConnectionString	= "Provider=" & PROVIDER _
                        & ";Data Source=" & DATA_SOURCE _
                        & ";Initial Catalog=" & DATABASE _
                        & ";Trusted_Connection=Yes"
	cn.Open

'   'SQL Server認証で接続する場合
'   cn.ConnectionString	= "Provider=" & PROVIDER _
'                       & ";Data Source=" & DATA_SOURCE _
'                       & ";Initial Catalog=" & DATABASE _
'						& ";UID=" & USER_ID _
'                       & ";PWD=" & PASSWORD
'   cn.Open

    '--------------------------------
    ' SQLの実行
    '--------------------------------
    Dim rs As New ADODB.Recordset
    strSQL = "select getdate()"
    rs.Open strSQL, cn

    '取得データをセルに一括出力
    rs.MoveFirst
    ActiveSheet.Range("A1").CopyFromRecordset rs

    '--------------------------------
    ' データベース切断
    '--------------------------------
    If Not rs Is Nothing Then
     	If rs.State = adStateOpen Then rs.Close
    	Set rs = Nothing
    	End If 
    If Not cn Is Nothing Then
    	If cn.State = adStateOpen Then cn.Close
    	Set cn = Nothing
    End If 

Exit Sub

ERR_HANDLER:

    'エラーメッセージ
    Debug.Print Err.Number & ")" & Err.Description

    '--------------------------------
    ' データベース切断
    '--------------------------------
    If Not rs Is Nothing Then
        If rs.State = adStateOpen Then rs.Close
        Set rs = Nothing
        End If 
    If Not cn Is Nothing Then
        If cn.State = adStateOpen Then cn.Close
        Set cn = Nothing
    End If
    
End Sub