
この記事では
・Excel(VBA)からSQL Serverへ接続するサンプルプログラム
を記載します!

今回は
・認証方法に「Windows 認証」
・ADOを使用
・SQL Server Native Client(OLE DBドライバ)を使用
して接続する方法を記載します!
サンプルプログラムの概要
以下のテーブル「EMPLOYEE」に対し、
・INSERT文
・SELECT文
を実行します。

サンプルプログラム作成

Option Explicit
Sub execSqlToSqlServer()
'===============================
'接続文字列
'===============================
'プロバイダ
Const PROVIDER As String = "MSOLEDBSQL"
'サーバー名(サーバーのPC名\インスタンス名)
Const SERVER_NAME As String = "localhost\SQLEXPRESS"
'DB名
Const DB_NAME As String = "sampleDB"
'===============================
'テーブルの列
'===============================
Const ID_COLUMN As Integer = 0
Const NAME_COLUMN As Integer = 1
Const SEX_COLUMN As Integer = 2
Const SECTION_COLUMN As Integer = 3
Dim con As Object
Dim conStr As String
Dim selectSql As String
Dim recordset As Object
Dim recordStr As String
Dim insertSql As String
Dim command As Object
Dim errorMessage As String
On Error GoTo conError
'===============================
'SQL Serverへ接続
'===============================
'接続文字列の組み立て
conStr = "Provider=" & PROVIDER & ";" & _
"Data Source='" & SERVER_NAME & "';" & _
"Initial Catalog='" & DB_NAME & "';" & _
"Integrated Security=SSPI;" & _
"DataTypeCompatibility=80;"
'オープン
Set con = CreateObject("ADODB.Connection")
con.Open conStr
On Error GoTo insertError
'===============================
'INSERT文を実行
'===============================
Set command = CreateObject("ADODB.Command")
'実行するINSERT文を設定
insertSql = "INSERT EMPLOYEE (ID, NAME, SEX, SECTION) VALUES ('00003', '田中花子', '女', '総務課')"
'INSERT文を実行
With command
.ActiveConnection = con
.CommandText = insertSql
.Execute
End With
MsgBox "INSERT文を実行しました!"
On Error GoTo selectError
'===============================
'SELECT文を実行
'===============================
'実行するSELECT文を設定
selectSql = "SELECT TOP(5) ID, NAME, SEX, SECTION FROM EMPLOYEE"
'SELECT文を実行し結果を取得
Set recordset = CreateObject("ADODB.Recordset")
recordset.Open selectSql, con
'SELECT文で取得したレコード分繰り返し
Do Until recordset.EOF
' 取得した1レコードをカンマ区切りでString型の変数に設定
recordStr = recordset.Fields(ID_COLUMN).Value & "," & _
recordset.Fields(NAME_COLUMN).Value & "," & _
recordset.Fields(SEX_COLUMN).Value & "," & _
recordset.Fields(SECTION_COLUMN).Value
' 取得した1レコードをMsgBoxで出力
MsgBox recordStr
' 次のレコードへ進む
recordset.MoveNext
Loop
'===============================
'後片付け
'===============================
recordset.Close
con.Close
Set command = Nothing
Set recordset = Nothing
Set con = Nothing
Exit Sub
conError:
'===============================
'後片付け
'===============================
Set con = Nothing
errorMessage = "接続でエラーが発生しました。" & vbCrLf & vbCrLf & _
"●エラー番号" & vbCrLf & Err.Number & vbCrLf & vbCrLf & _
"●エラー内容" & vbCrLf & Err.Description & vbCrLf
MsgBox errorMessage, vbCritical
Exit Sub
insertError:
'===============================
'後片付け
'===============================
con.Close
Set command = Nothing
Set recordset = Nothing
Set con = Nothing
errorMessage = "INSERT文でエラーが発生しました。" & vbCrLf & vbCrLf & _
"●エラー番号" & vbCrLf & Err.Number & vbCrLf & vbCrLf & _
"●エラー内容" & vbCrLf & Err.Description & vbCrLf
MsgBox errorMessage, vbCritical
Exit Sub
selectError:
'===============================
'後片付け
'===============================
recordset.Close
con.Close
Set command = Nothing
Set recordset = Nothing
Set con = Nothing
errorMessage = "SELECT文でエラーが発生しました。" & vbCrLf & vbCrLf & _
"●エラー番号" & vbCrLf & Err.Number & vbCrLf & vbCrLf & _
"●エラー内容" & vbCrLf & Err.Description & vbCrLf
MsgBox errorMessage, vbCritical
Exit Sub
End Sub
結果(正常終了の場合)
INSERT文

SELECT文



結果(Insert文の実行でエラーの場合)

備考 実行するSQLについて
上記のサンプルプログラムは、SQLをVBA上に直接記載しています。
そうではなくて、SQLをシート上に記載してVBAから取得するようにしたほうが良いと思います。
SQLを変更することになった際に、プログラム(VBA)の修正が不要となるためです。
※プログラム(VBA)の単体テストが不要になる。

トランザクション管理をする手順を以下の記事に記載しました!
合わせてご確認ください!