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

2020/06/01

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=パスワード;"

'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

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

: Excelのセル幅に合わせて縦横比を維持したまま画像サイズを自動調整する方法

VBAで画像を挿入することができますが、単純にセル幅に合わせて画像サイズを変更すると、縦長や横長の画像として挿入されます。縦横比を維持したまま、画像を挿入する …

: [Windows]WSUSサーバの構築手順 6/6

Windows 2008 R2サーバにWSUSサーバを構築するクライアントPCがWSUSサーバから更新プログラムをダウンロードするにはグループポリシーを設定す …

: Emotetの感染チェックツール「EmoCheck」を定期的に自動実行する方法

Emotetの感染チェックツールEmoCheckをドメイン環境下のパソコンで定期的に自動実行する設定方法を記載します。自動実行の概要 ① EmoCheckを起 …

: 既存パソコンを有効活用した、Windows 10への移行方法を考える

企業や個人用など数多くのパソコンで稼働しているOS、Windows 7のサポート期間が2020年1月14日に終了します。サポート期間終了に向けてWindows …

: VBAでひらがな・カタカナの小文字(捨て仮名)を全角に変換する

VBAで半角文字から全角文字に変換したり、ひらがなをカタカナに変換するには、「StrConv」関数に引数を指定することで意図した文字に変換することができます。た …

: 【パソコン不要】HDDのデータをSSDに丸ごとコピーして交換する方法

近頃はSSDの記憶容量の増加、価格の値下がりが続き、HDDとの容量・価格差も縮まり購入しやすい状況となっています。HDDからSSDに交換することで読込・書込速度 …

: Excel VBAでOracleのデータを参照するマスター検索機能を作成する

エクセルのVBAでOracleに保存されているマスタを検索する機能を作成する機会がありましたので、サンプルプログラムを記載します。 Oracleへの接続設定 V …

: [Windows]Active ReportsでPDF出力時の注意事項

PDF出力時の「LicenseException」対応方法環境 ActiveReports for .NET 7.0J 言語:C# 設定 Active Rep …

: [Windows]WSUSサーバの構築手順 1/6

Windows 2008 R2サーバにWSUSサーバを構築する Windows Server Update Services(WSUS)とは Windows S …

: VBAで形式を指定して日付を取得する

VBAで西暦や和暦の日付形式に変換するには「FORMAT」関数に引数を指定することで意図した日付形式に変換することができます。日付を表示 Date関数で今日の …

PREV: アクセス(Access)を購入せずに無料のランタイムだけで使う方法
NEXT: Emotetの感染チェックツール「EmoCheck」を定期的に自動実行する方法

平凡なエンジニアがメモ代わりにプログラミング, インフラ, ネットワークを書き綴るブログ

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

参照設定

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

サンプルプログラム

関連記事

Excelのセル幅に合わせて縦横比を維持したまま画像サイズを自動調整する方法

[Windows]WSUSサーバの構築手順 6/6

Emotetの感染チェックツール「EmoCheck」を定期的に自動実行する方法

既存パソコンを有効活用した、Windows 10への移行方法を考える

VBAでひらがな・カタカナの小文字(捨て仮名)を全角に変換する

【パソコン不要】HDDのデータをSSDに丸ごとコピーして交換する方法

Excel VBAでOracleのデータを参照するマスター検索機能を作成する

[Windows]Active ReportsでPDF出力時の注意事項

[Windows]WSUSサーバの構築手順 1/6

VBAで形式を指定して日付を取得する

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

参照設定

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

サンプルプログラム

Google AdSense PC

Google AdSense PC

関連記事