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]WSUSサーバの構築手順 5/6

Windows 2008 R2サーバにWSUSサーバを構築する WSUS(Windows Server Update Service)の設定 [スタート]-[す …

microsoft
VBAからOracleのストアドプロシージャを実行する方法

VBAからOracleのストアドプロシージャを実行する方法について記載します。 VBAからOracleに接続する方法は以前の記事を参考にしてください。 ストアド …

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

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

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

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

microsoft
[Windows]「このリモートコンピューターのIDを識別できません。接続しますか?」の対応方法

警告の原因と対応方法 リモートデスクトップ接続をしたときに表示されるこのメッセージ 「このリモートコンピューターのIDを識別できません。接続しますか?」 「はい …

アクセス(Access)を購入せずに無料のランタイムだけで使う方法

Accessは、マイクロソフトが提供しているリレーショナルデータベースソフトです。 データベースにデータを蓄積することで、条件に一致するデータを抽出することやデ …

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

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

Microsoft TeamsのアカウントがないユーザをWEB会議に招待する方法

Microsoft Teamsは、Microsoftが提供しているコミュニケーションツールです。Teamsの機能として、チャットや音声通話・WEB会議などを利用 …

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

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

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

Windows 2008 R2サーバにWSUSサーバを構築する WSUS(Windows Server Update Service)のインストール [スタート …