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
関連記事
-
-
[Windows]共有フォルダのアクセス権を設定する
アクセス権の種類 共有フォルダのアクセス権は[共有アクセス許可]と[NTFSアクセス許可]の2つの許可エントリのセットによって決定します。 [共有アクセス許可] …
-
-
Excelファイルの更新をメールで自動通知する方法【業務自動化】
ファイルを複数人で共有している時など、更新時にメンバーへ連絡することがルール化されている場合があります。 毎回、口頭や手動でメールで連絡していると、連絡漏れや連 …
-
-
VBAで形式を指定して日付を取得する
VBAで西暦や和暦の日付形式に変換するには「FORMAT」関数に引数を指定することで意図した日付形式に変換することができます。 日付を表示 Date関数で今日の …
-
-
WEB会議・WEB面接を快適にするおすすめ機器の紹介
新型コロナウイルスの流行拡大に伴い、WEB会議・WEB面接を導入する企業が増えてきました。 この記事では、これからWEB会議・WEB面接を導入する予定の企業や実 …
-
-
Outlookでメール送信中になったまま送受信が終了しない場合
開封確認のメールが正常に送信されず、送信中のまま送受信が終了しない場合があります。 原因となる開封確認メールは送信トレイに存在しないため、削除することができませ …
-
-
[Windows]指定サイズのファイルを作成する
ネットワークの通信速度などを調査する場合にサイズの大きなファイルを作成する必要があります。 この記事では「fsutil」コマンドを使用したダミーファイルの作成方 …
-
-
[Windows]IISセッション管理(状態サーバ)
IISのセッションをアウトプロセスで管理する方法 IISのセッションはインプロセスが既定設定となっておりワーカープロセスが再起動されるたびにセッション情報が破棄 …
-
-
[Windows]Windows Server 2008 R2 IISインストール手順
Windows Server 2008 R2にIISをインストールする 環境 OS:Windows Server 2008 R2 IIS:ver7.5 インスト …
-
-
[Windows]共有フォルダへのアクセスログを取得する方法
VVAULT AUDITを使用したログ管理 Windowsでは監査ポリシーを設定することにより共有フォルダやファイルへのアクセスログを記録し、インベントビューア …
-
-
[Oracle]VBAからSELECT文を実行しデータを取得する方法
VBAからOracleデータベースでSELECT文を実行してデータを取得するライブラリを作成しましたので記載します。 Oracle接続にはOracle Clie …