Skill Note

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

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

Google AdSense PC

Google AdSense PC

  関連記事

microsoft
[Windows]共有フォルダのアクセス権を設定する

アクセス権の種類 共有フォルダのアクセス権は[共有アクセス許可]と[NTFSアクセス許可]の2つの許可エントリのセットによって決定します。 [共有アクセス許可] …

Excelファイルの更新をメールで自動通知する方法【業務自動化】

ファイルを複数人で共有している時など、更新時にメンバーへ連絡することがルール化されている場合があります。 毎回、口頭や手動でメールで連絡していると、連絡漏れや連 …

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

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

microsoft
WEB会議・WEB面接を快適にするおすすめ機器の紹介

新型コロナウイルスの流行拡大に伴い、WEB会議・WEB面接を導入する企業が増えてきました。 この記事では、これからWEB会議・WEB面接を導入する予定の企業や実 …

microsoft
Outlookでメール送信中になったまま送受信が終了しない場合

開封確認のメールが正常に送信されず、送信中のまま送受信が終了しない場合があります。 原因となる開封確認メールは送信トレイに存在しないため、削除することができませ …

microsoft
[Windows]指定サイズのファイルを作成する

ネットワークの通信速度などを調査する場合にサイズの大きなファイルを作成する必要があります。 この記事では「fsutil」コマンドを使用したダミーファイルの作成方 …

microsoft
[Windows]IISセッション管理(状態サーバ)

IISのセッションをアウトプロセスで管理する方法 IISのセッションはインプロセスが既定設定となっておりワーカープロセスが再起動されるたびにセッション情報が破棄 …

microsoft
[Windows]Windows Server 2008 R2 IISインストール手順

Windows Server 2008 R2にIISをインストールする 環境 OS:Windows Server 2008 R2 IIS:ver7.5 インスト …

microsoft
[Windows]共有フォルダへのアクセスログを取得する方法

VVAULT AUDITを使用したログ管理 Windowsでは監査ポリシーを設定することにより共有フォルダやファイルへのアクセスログを記録し、インベントビューア …

microsoft
[Oracle]VBAからSELECT文を実行しデータを取得する方法

VBAからOracleデータベースでSELECT文を実行してデータを取得するライブラリを作成しましたので記載します。 Oracle接続にはOracle Clie …