ホーム
Excelを使ったデータベース操作

SQLの基本的なコードパターン

2018.03.30

SQLの基本的なコードパターン　パラメータを必要とするパターン

主要なパターン以外に「パラメータを必要とするパターン」がある。
パラメータを必要とするクエリ実行の際に使用する。

VBAを使用してSQL文を作成する場合、わざわざパラメターの必要なSQLを作成しなくても、その都度必要なSQLを作成してしまって前述の2つのパターンを使用しても良い。
何度も条件を変えて実行するようなSQLであれば、パラメータの必要なSQLを作成して「名前つきコマンド」として運用した方が分かりやすい場合もある。 

Sub SQL実行パターン3_1()
Dim myCon As New ADODB.Connection, myCmd As New ADODB.Command
Dim myPmt As ADODB.Parameter
Dim mySrc As String

'接続先データベース
mySrc = ThisWorkbook.Path & "\mdb\4-sampleDB.mdb"

'接続
With myCon
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .Open mySrc
End With

'SQLを実行
With myCmd
  '元となるSQL
  .CommandText = "update アルバイト set 手当 = ? where 所属 = ?;"
  .CommandType = adCmdText
  .ActiveConnection = myCon
  'パラメータの初期値を設定し、追加
  Set myPmt = myCmd.CreateParameter("金額", adInteger, adParamInput, , 5000)
  .Parameters.Append myPmt
  Set myPmt = myCmd.CreateParameter("所属", adChar, adParamInput, 10, "キッチン")
  .Parameters.Append myPmt
  '設定値で処理を実行
  .Execute
  '新しいパラメータを設定し、再度実行
  .Parameters("金額").Value = 3000
  .Parameters("所属").Value = "ホール"
  .Execute
End With

Set myCmd = Nothing
myCon.Close: Set myCon = Nothing

End Sub

パラメータの必要なSQLを作成して「名前つきコマンド」として実行

Sub SQL実行パターン3_2()
Dim myCon As New ADODB.Connection, myCmd As New ADODB.Command
Dim myPmt As ADODB.Parameter
Dim mySrc As String

'接続先データベース
mySrc = ThisWorkbook.Path & "\mdb\4-sampleDB.mdb"

'接続
With myCon
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .Open mySrc
End With

'SQLを実行
With myCmd
  '元となるSQL
  .CommandText = "update アルバイト set 手当 = ? where 所属 = ?;"
  .CommandType = adCmdText
  'Commandオブジェクトに名前をつける
  .Name = "手当更新"
  .ActiveConnection = myCon
  'パラメータの初期値を設定し、追加
  Set myPmt = myCmd.CreateParameter("金額", adInteger, adParamInput, , 5000)
  .Parameters.Append myPmt
  Set myPmt = myCmd.CreateParameter("所属", adChar, adParamInput, 10, "キッチン")
  .Parameters.Append myPmt
End With

'名前付きコマンドを実行
myCon.手当更新
  
'新しいパラメータを設定
With myCmd
  .Parameters("金額").Value = 3000
  .Parameters("所属").Value = "ホール"
End With

'名前付きコマンドを実行
myCon.手当更新

Set myCmd = Nothing
myCon.Close: Set myCon = Nothing

End Sub

新しいパラメータを設定する箇所は以下のように記述しても実行可能。 

'新しいパラメータを設定
With myCmd
  .Parameters("金額").Value = 3000
  .Parameters("所属").Value = "ホール"
End With

'名前付きコマンドを実行
myCon.手当更新

↓

'名前付きコマンドを再度実行
myCon.手当更新 3000, "ホール"
前へ
123