Excel VBA 逆引き集 | 実務テンプレ完全版（超再利用部品） – DB連携テンプレ

Excel VBA Excel VBA
スポンサーリンク

ねらい：Excelを「DBのフロントエンド」にするための型を持つ

DB連携テンプレのゴールは、こうです。
「毎回ググりながらADOコードを書く」のをやめて、
接続・SELECT・INSERT/UPDATE・切断までを“いつも同じ型”で書けるようにすることです。

ここでは、いちばん現場で使いやすい「Excel VBA＋ADO」のテンプレを、
プログラミング初心者でも真似できる形でまとめます。

全体像：DB連携の基本フローと設計方針

DB連携の4ステップ

Excelから外部DB（SQL Server、Access、MySQLなど）に接続するとき、流れはいつも同じです。

  1. 接続オブジェクト（Connection）を作る
  2. 接続文字列（ConnectionString）を指定して DB に接続する
  3. SQL を実行して、結果を Recordset で受け取る（SELECT）
  4. 結果をシートに展開したり、更新系（INSERT/UPDATE/DELETE）を実行したりする

この「4ステップ」を、毎回コピペではなく“テンプレ関数”として持っておくのが、DB連携テンプレの狙いです。

設計方針：3つのレイヤーに分ける

DB連携コードは、次の3つに分けておくと、あとで圧倒的に楽になります。

接続まわり（共通の Connection ヘルパー）
SQL実行まわり（SELECT用・更新用の共通関数）
業務ロジック（「何を取りたいか」「どこに出すか」）

重要なのは、「業務ロジックの中に生の接続コードをベタ書きしない」ことです。
接続とSQL実行は“部品”、業務ロジックは“部品の組み合わせ”という意識を持つと、再利用性が一気に上がります。

コア部品1：接続ヘルパー（Connection）テンプレ

まずは「Accessに接続する」一番シンプルな例

例として、同じフォルダにある Sample.accdb（Access DB）に接続するテンプレからいきます。

' ModDb.bas
Option Explicit

Public Function OpenAccessConnection() As Object
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    Dim dbPath As String
    dbPath = ThisWorkbook.Path & "\Sample.accdb"
    
    Dim connStr As String
    connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & dbPath & ";"
    
    conn.Open connStr
    
    Set OpenAccessConnection = conn
End Function
VB

初心者向けにポイントをかみ砕きます。

CreateObject(“ADODB.Connection”) で「接続用の箱」を作る
ConnectionString に「どのDBに、どのプロバイダでつなぐか」を書く
Open で実際に接続する
最後に、その Connection を戻り値として返す

この関数を作っておけば、「Accessに接続する」という作業はどこからでも一行で呼べます。

Dim conn As Object
Set conn = OpenAccessConnection()
VB

SQL Server や他のDBでも、基本は同じで「ConnectionString だけが違う」と考えてOKです。

コア部品2：SELECT用テンプレ（Recordset→シート）

「SQLを投げて、結果をシートに出す」共通関数

次に、「SELECT文を実行して、結果を指定シートに貼る」テンプレを作ります。

Public Sub RunSelectToSheet( _
        ByVal sql As String, _
        ByVal targetSheetName As String, _
        Optional ByVal startCellAddress As String = "A1")
    
    Dim conn As Object
    Set conn = OpenAccessConnection()
    
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    
    rs.Open sql, conn, 1, 1   ' 1=adOpenKeyset, 1=adLockReadOnly（簡易指定）
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(targetSheetName)
    
    ws.Range(startCellAddress).CurrentRegion.Clear
    ws.Range(startCellAddress).CopyFromRecordset rs
    
    rs.Close
    conn.Close
End Sub
VB

ここでの重要ポイントは、次の通りです。

Recordset を使って SQL の結果を受け取る
CopyFromRecordset で、一気にシートに貼り付ける
終わったら rs.Close と conn.Close で必ず閉じる

この Sub を持っておけば、「SQLを書くだけでシートに結果を出せる」状態になります。

例題：顧客マスタを Excel に取り込む

Access 側に M_Customer テーブルがあるとします。
顧客コードと顧客名を Excel の Customer シートに取り込みたい場合は、こう書くだけです。

Sub LoadCustomerFromDb()
    Dim sql As String
    sql = "SELECT CustomerCode, CustomerName FROM M_Customer ORDER BY CustomerCode"
    
    RunSelectToSheet sql, "Customer", "A1"
End Sub
VB

業務ロジック側は「どのテーブルから、どの列を、どんな条件で取るか」だけに集中できて、
接続やRecordsetの細かい処理はテンプレに隠せます。

コア部品3：INSERT/UPDATE/DELETE用テンプレ（更新系）

「件数だけ返す」更新系テンプレ

SELECT 以外（INSERT/UPDATE/DELETE）は、Recordset は不要で、
「何件影響したか」だけ分かれば十分なことが多いです。

Public Function ExecuteNonQuery(ByVal sql As String) As Long
    Dim conn As Object
    Set conn = OpenAccessConnection()
    
    Dim affected As Long
    conn.Execute sql, affected
    
    conn.Close
    ExecuteNonQuery = affected
End Function
VB

ポイントは、Connection.Execute の第2引数に「影響行数」が返ってくることです。
これをそのまま戻り値にしておけば、「何件更新されたか」を呼び出し側で確認できます。

例題：ExcelのデータをDBにINSERTする

例えば、Excel の Upload シートに、A列：顧客コード、B列：顧客名 が入っていて、
それを M_Customer テーブルにINSERTしたいとします。

Sub UploadCustomerToDb()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Upload")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    If lastRow < 2 Then Exit Sub
    
    Dim i As Long
    For i = 2 To lastRow
        Dim code As String
        Dim name As String
        
        code = Replace(CStr(ws.Cells(i, 1).Value), "'", "''")
        name = Replace(CStr(ws.Cells(i, 2).Value), "'", "''")
        
        Dim sql As String
        sql = "INSERT INTO M_Customer (CustomerCode, CustomerName) " & _
              "VALUES ('" & code & "', '" & name & "')"
        
        ExecuteNonQuery sql
    Next i
End Sub
VB

ここではシンプルさを優先して「1行ずつINSERT」していますが、
実務ではトランザクションや一括INSERTを検討しても良い場面です。

重要なのは、「更新系も ExecuteNonQuery に隠す」という型を持つことです。

コア部品4：設定シート方式と組み合わせる（DB版）

接続情報を「設定シート」に逃がす

実務では、接続文字列の中に「サーバー名」「DB名」「ユーザー名」「パスワード」などが入ります。
これをコードにベタ書きすると、環境が変わるたびにVBAを開いて修正することになり、危険でもあります。

そこで、「ConfigDb」シートを用意して、こんな感じで持たせます。

A列：キー名（Server, Database, User, Password, Provider など）
B列：値

そして、OpenAccessConnection の中で、ConfigDb から値を読むようにします。

Private Function GetDbConfig(ByVal keyName As String) As String
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("ConfigDb")
    
    Dim rng As Range
    Set rng = ws.Range("A:A").Find(What:=keyName, LookAt:=xlWhole)
    
    If rng Is Nothing Then
        GetDbConfig = ""
    Else
        GetDbConfig = CStr(rng.Offset(0, 1).Value)
    End If
End Function

Public Function OpenSqlServerConnection() As Object
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    Dim connStr As String
    connStr = "Provider=" & GetDbConfig("Provider") & ";" & _
              "Data Source=" & GetDbConfig("Server") & ";" & _
              "Initial Catalog=" & GetDbConfig("Database") & ";" & _
              "User ID=" & GetDbConfig("User") & ";" & _
              "Password=" & GetDbConfig("Password") & ";"
    
    conn.Open connStr
    Set OpenSqlServerConnection = conn
End Function
VB

こうしておけば、「接続先が変わったときは ConfigDb の値だけ変えればいい」という状態になります。
DB連携テンプレを“実務で回る仕組み”にするうえで、ここはかなり重要なポイントです。

例題：DBから取得→Excelで加工→DBに戻す一連の流れ

シナリオ

SQL Server 上の T_Sales テーブルから売上明細を取得し、
Excelで集計やクリーニングを行い、
結果を T_SalesSummary テーブルに書き戻す——という一連の流れを、テンプレで組み立ててみます。

1. 明細を取得してシートに展開

Sub LoadSalesFromDb()
    Dim sql As String
    sql = "SELECT SalesDate, CustomerCode, Amount " & _
          "FROM T_Sales " & _
          "WHERE SalesDate BETWEEN '2025-01-01' AND '2025-01-31'"
    
    RunSelectToSheet sql, "SalesRaw", "A1"
End Sub
VB

2. Excel側で集計（ここはこれまでの配列・辞書テンプレを使う）

ここは、すでに作った「辞書×配列の集計テンプレ」を使うイメージです。
顧客別合計を SalesAgg シートに出す、など。

3. 集計結果をDBにINSERT

Sub UploadSalesSummaryToDb()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("SalesAgg")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    If lastRow < 2 Then Exit Sub
    
    Dim i As Long
    For i = 2 To lastRow
        Dim cust As String
        Dim totalAmt As Double
        
        cust = Replace(CStr(ws.Cells(i, 1).Value), "'", "''")
        If IsNumeric(ws.Cells(i, 2).Value) Then
            totalAmt = CDbl(ws.Cells(i, 2).Value)
        Else
            totalAmt = 0
        End If
        
        Dim sql As String
        sql = "INSERT INTO T_SalesSummary (CustomerCode, TotalAmount) " & _
              "VALUES ('" & cust & "', " & CStr(totalAmt) & ")"
        
        ExecuteNonQuery sql
    Next i
End Sub
VB

このように、「DB→Excel→DB」の往復も、
接続・SELECT・更新をテンプレ化しておけば、業務ロジックだけに集中して組み立てられます。

重要ポイントの深掘り：DB連携テンプレを“怖くないもの”にする

例外（エラー）処理と切断を必ずセットにする

DB連携で一番やってはいけないのは、「エラーが出たときに接続を開きっぱなしにする」ことです。

理想は、共通のラッパを用意して、こういう形にしておくことです。

Public Sub SafeRunSelectToSheet( _
        ByVal sql As String, _
        ByVal targetSheetName As String, _
        Optional ByVal startCellAddress As String = "A1")
    
    On Error GoTo ErrHandler
    
    Dim conn As Object
    Set conn = OpenAccessConnection()
    
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    
    rs.Open sql, conn, 1, 1
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(targetSheetName)
    
    ws.Range(startCellAddress).CurrentRegion.Clear
    ws.Range(startCellAddress).CopyFromRecordset rs
    
    rs.Close
    conn.Close
    Exit Sub
    
ErrHandler:
    On Error Resume Next
    If Not rs Is Nothing Then
        If rs.State <> 0 Then rs.Close
    End If
    If Not conn Is Nothing Then
        If conn.State <> 0 Then conn.Close
    End If
    MsgBox "DB処理中にエラーが発生しました。", vbExclamation
End Sub
VB

「エラーが起きても必ず Close する」という型を一度作ってしまえば、
あとはこのラッパを使うだけで安全性がぐっと上がります。

SQLは「文字列を組み立てる」ことに慣れる

VBAからDBを触るとき、避けて通れないのが「SQL文字列の組み立て」です。

初心者がつまずきやすいポイントは、主にここです。

日付や文字列をシングルクォートで囲む
文字列中のシングルクォートを '' にエスケープする
WHERE句の条件を & でつなぐ

最初は少し面倒に感じますが、
「SQLはただの文字列」「VBAでその文字列を作っているだけ」と割り切ると、だんだん慣れてきます。

「Excelでやるべきこと」と「DBに任せるべきこと」を分ける

DB連携テンプレを使い始めると、「全部Excelでやる」か「全部DBでやる」か、極端になりがちです。
現実的には、こう分けるのがおすすめです。

大量データの絞り込み・集計・JOIN：できるだけDB側のSQLでやる
見せ方の調整・レイアウト・細かい加工：Excel側でやる

この線引きを意識すると、「DBはエンジン、Excelはフロント」という役割分担がはっきりして、
全体の設計がすっきりします。

まとめ：DB連携テンプレは「接続・SELECT・更新・設定シート」を型にすること

今日のDB連携テンプレを一言でまとめると、こうなります。

接続ヘルパー（Open○○Connection）を1つ作る
SELECT用の RunSelectToSheet（または SafeRunSelectToSheet）を持つ
更新用の ExecuteNonQuery を持つ
接続情報は ConfigDb シートに逃がす

この“4点セット”さえ一度作ってしまえば、
「このテーブルをExcelに持ってきたい」「この集計結果をDBに戻したい」といった要望に、
落ち着いて、同じパターンで応えられるようになります。

スポンサーリンク
タイトルとURLをコピーしました