ExcelVBAとAccessの連携 番外2-1 プレースホルダを使ったSQL実行

ExcelとAccessの連携というテーマに関してはいままでに数多く書いてきました。その中で、そういえばこの話題についてはちゃんと書いたことがなかったなと思って。Access自体がデータベースとして特殊な部類に入るのでなかなか判断の難しい話題ではありますが、自分の言葉でまとめておくことにも意義があるかと思って、書いてみました！

関連記事

• 第1回 Excelからデータベースへの接続
• 第2回 テーブル設計とシート&コードの準備
• 第3回 SQLを使った読み書きの処理
• 第4回 条件を絞ってデータを読み込む
• 第5回 レコードの更新・削除
• 第6回 トランザクション処理
• 番外1 リファクタリングしたコード
• 番外2-1 プレースホルダを使ったSQL実行 ←NOW!
• 番外2-2 続・プレースホルダを使ったSQL実行（汎用化）

プレースホルダとは

プレースホルダとは、実際の内容を後から挿入するために、とりあえず仮に確保した場所のこと。また、そのことを示す標識などのこと。

出典:プレースホルダ（placeholder）とは – IT用語辞典 e-Words

データベースを使ったアプリケーションを作成する際、第三者の入力値をそのまま組み込むと悪意のあるSQL文を挿入される（SQLインジェクション）可能性があるため、入力値を一旦別の場所に確保（プレースホルダ）しておいて、実行のときは無害化して使う、というのが一般的な手段です。

SQLインジェクション（英: SQL Injection）とは、アプリケーションのセキュリティ上の不備を意図的に利用し、アプリケーションが想定しないSQL文を実行させることにより、データベースシステムを不正に操作する攻撃方法のこと。また、その攻撃を可能とする脆弱性のことである。

SQLに別のSQL文が「注入 (inject)」されることから、「ダイレクトSQLコマンドインジェクション」もしくは「SQL注入」と呼ばれることもある。

出典:SQLインジェクション – Wikipedia

Accessで対策は必要なのか

データベースと一言で言っても、その使われ方はさまざまで、不特定多数の人の目に触れたり、操作できたりする種類のデータベース（Webサービスや、このブログなんかもそうです）と、ある範囲の中にあり非公開で使われているデータベースがあります。後者をインハウス（企業内）データベースと呼ぶらしいですが、Accessはそのインハウスの中でもかなり限定的に使われることが前提のソフトウェアです。

一般的にデータベースシステムとは、専用のデータベースサーバーを建ててお金をかけて強固にして運用されていますが、Accessは単なるファイルで、1つのクローズドなパソコンで自分で構築して自分で使う、せいぜい5人くらいまでの人に触ってもらうこともあるかな、くらいのものでしょうか。SQLインジェクションの危険があるかといえば、可能性は非常に低いと思われます。バックアップを確実にとるほうが優先順位が高いでしょう。

とはいえ、まがりなりにもデータベースを扱っているので、そういった知識は持っているべきだと思います。

ExcelVBA→Accessからはじめたものの、将来的にExcelVBA→SQLサーバーになるかもしれませんし、知識は無駄にはならないです！　というわけでVBAでプレースホルダ（パラメータ）を使ったデータベース操作について以下に書いてゆきます。

参考

• Open メソッド (ADO Recordset) – ActiveX Data Objects (ADO) | Microsoft Docs
• Execute method (ADO)
• クエリにパラメータを渡して結果を取得する（ADO）Command オブジェクト・Execute メソッド | ExcelWork.info
• 「Recordsetのレコード数」（1） Database Expert － ＠IT

いろいろ勉強させていただきながら書きました。自分の使いやすいように結構アレンジしたので、しっくりこない方は上記のサイトをぜひ御覧ください！

なお、ここから先のコードは参照設定で「Microsoft ActiveX Data Objects *.* Library」にチェックを入れない状態で動くように書いたものです。

INSERT, UPDATE, DELETE

単一文

まずは一番シンプルな形。コマンドオブジェクトを使います。SQLのVALUES部分を?にしておいて、配列に入れたプレースホルダをパラメータとして渡して実行する、という形です。

Sub sample()
  '接続
  Dim cn As Object 'ADOコネクション用オブジェクト
  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & ThisWorkbook.Path & "\Data.accdb;" 'Accessファイルを指定してコネクションを開く
  
  'ADOコマンドオブジェクトの設定
  Dim cmd As Object
  Set cmd = CreateObject("ADODB.Command")
  cmd.ActiveConnection = cn
  
  '実行
  cmd.CommandText = "INSERT INTO table1(f1, f2, f3, f4) VALUES(?, ?, ?, ?);"
  cmd.Execute Parameters:=Array(1, Now, "a", False)
  
  'コマンドオブジェクトの後処理
  Set cmd = Nothing
  
  '接続解除
  cn.Close
  Set cn = Nothing
End Sub

複数文+トランザクション

さて、単一なら上記で良いのですが、実際は複数のSQLを実行して、トランザクション処理と併用して使いたい。ここで結構ハマったのですが、どうもパラメータの数やデータ型が途中で変わったり、パラメータを使ったり使わなかったり、というのを混ぜるとエラーになってしまって。試行錯誤した結果、コマンドオブジェクトを毎回作り直すことでいけそうです。

Sub sample()
  On Error GoTo Err_Handler 'エラーが起きたら「ErrorHandler」にジャンプする指示
  
  '接続
  Dim cn As Object 'ADOコネクション用オブジェクト
  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & ThisWorkbook.Path & "\Data.accdb;" 'Accessファイルを指定してコネクションを開く
  
  cn.BeginTrans 'トランザクション開始
  
  'ADOコマンドオブジェクトの設定
  Dim cmd As Object
  
  '1つめ
  Set cmd = CreateObject("ADODB.Command")
  cmd.ActiveConnection = cn
  cmd.CommandText = "INSERT INTO table1(f1, f2, f3, f4) VALUES(?, ?, ?, ?);"
  cmd.Execute Parameters:=Array(1, Now, "a", False) '←パラメーター4つ
  Set cmd = Nothing

  '2つめ
  Set cmd = CreateObject("ADODB.Command")
  cmd.ActiveConnection = cn
  cmd.CommandText = "INSERT INTO table1(f1, f2, f3, f4) VALUES(2, ?, 'b',?);"
  cmd.Execute Parameters:=Array(Now, True) '←パラメーター2つ
  Set cmd = Nothing

  '3つめ
  Set cmd = CreateObject("ADODB.Command")
  cmd.ActiveConnection = cn
  cmd.CommandText = "UPDATE table2 SET f2 = 'aaa' WHERE h1 = 1;"
  cmd.Execute'←パラメーターなし
  Set cmd = Nothing
  
  cn.CommitTrans '確定
  
  GoTo Finally '正常に終了したら最終処理へジャンプ
  
Err_Handler:   '例外処理（エラーが起きたらここへジャンプ）
  cn.RollbackTrans '元の状態へ戻す
  Dim msgTxt As String
  msgTxt = "Error #: " & Err.Number & vbNewLine & vbNewLine & Err.Description 'エラーメッセージ格納
  MsgBox msgTxt, vbOKOnly + vbCritical, "エラー" 'メッセージ出力
  
Finally: '最終処理
  'コマンドオブジェクトの処理
  If Not cmd Is Nothing Then
    Set cmd = Nothing
  End If
  
  '接続解除
  cn.Close
  Set cn = Nothing
End Sub

SELECT

レコードセットを取得してイミディエイトウィンドウへ出力

お次はSELECT文。上記でも使っていた実行時のcmd.Executeは関数になっていて、レコードセットオブジェクトを返すらしいので、そいつを受け取ります。（SELECTじゃない構文の場合は閉じた状態のレコードセットが返るみたい）

あとはレコードセットを繰り返して出力する感じ。以下では中身が空かとかそういうチェックはしていません。

Sub sample()
  '接続
  Dim cn As Object 'ADOコネクション用オブジェクト
  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & ThisWorkbook.Path & "\Data.accdb;" 'Accessファイルを指定してコネクションを開く
  
  'ADOコマンドオブジェクトの設定
  Dim cmd As Object
  Set cmd = CreateObject("ADODB.Command")
  cmd.ActiveConnection = cn
  
  'ADOレコードセットオブジェクトの設定
  Dim rs As Object
  Set rs = CreateObject("ADODB.RecordSet")
  
  '実行
  cmd.CommandText = "SELECT * FROM table1 WHERE f1 = ?"
  Set rs = cmd.Execute(Parameters:=Array("a"))

  Do Until rs.EOF 'レコードセットが終了するまで処理を繰り返す
    Dim fldNum As Long 'フィールドの繰り返し用変数
    For fldNum = 0 To rs.Fields.count - 1 'フィールドの数だけ繰り返す
      Debug.Print rs(fldNum), ; 'フィールドを改行せずイミディエイトウィンドウに出力
    Next fldNum '次のフィールドへ
    Debug.Print "" 'レコード切り替えでイミディエイトウィンドウを改行
    rs.MoveNext '次のレコードに移動する
  Loop

  'レコードセットオブジェクトの処理
  rs.Close 'レコードセットを閉じる
  Set rs = Nothing
  
  'コマンドオブジェクトの後処理
  Set cmd = Nothing
  
  '接続解除
  cn.Close
  Set cn = Nothing
End Sub

レコードセットカウントを取得するには

配列に入れたいとか、加工して使いたいときに最初にレコードセットの総数が欲しいときありますよね～！

そんなときはいったんレコードセットを受け取らずにcmd.Executeしたあと、カーソルタイプを指定してrs.Openするとrs.recordCountで取得できました！（cmd、rsは任意の変数名です。念のため。）

Sub sample()
  '接続
  Dim cn As Object 'ADOコネクション用オブジェクト
  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & ThisWorkbook.Path & "\Data.accdb;" 'Accessファイルを指定してコネクションを開く
  
  'ADOコマンドオブジェクトの設定
  Dim cmd As Object
  Set cmd = CreateObject("ADODB.Command")
  cmd.ActiveConnection = cn
  
  '実行
  cmd.CommandText = "SELECT * FROM table1 WHERE f1 = ?"
  cmd.Execute Parameters:=Array("a")

  'ADOレコードセットオブジェクトの設定
  Dim rs As Object
  Set rs = CreateObject("ADODB.RecordSet")
  rs.Open cmd, , 1 'adOpenKeyset=1でレコードセットを取得(adOpenStatic=3でもいいかも)
  
  Debug.Print rs.recordCount 'レコードセットカウントが取得できる

  '処理

  'レコードセットオブジェクトの処理
  rs.Close 'レコードセットを閉じる
  Set rs = Nothing
  
  'コマンドオブジェクトの後処理
  Set cmd = Nothing
  
  '接続解除
  cn.Close
  Set cn = Nothing
End Sub

次回に続きます

番外1と同様に、SQLとプレースホルダの値だけを受け取って処理する汎用モジュールを作ったら便利かなと思ったのですが、とても書ききれなくなったのでいったんここで切ります。需要はきっと恐ろしく少ないと思うのですが、それでこそ個人ブログだと思っているので書きますね！！！