1. 外部データベース上のデータを変更する
次のサンプル モジュールでは、データソースとの接続を確立後、指定したレコードの
変更を行っています。
サンプル 1-1 (SQLServer)
  Sub UpdateSQL()
      Dim con As Variant
      Dim sql As String
      'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
      '以下の 2 行は改行せずにコードを入力してください。
      con = SQLOpen("DSN=TstSQL;SERVER=serversql1;UID=xx;DATABASE=pubs;
      PWD=Excel", , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'SQL 文定義
      'テーブル [売上] で [書店番号] が 7067 の [数量] を 100 に変更します
      sql = "UPDATE 売上 SET 数量=100 WHERE 書店番号='7067'"
      'クエリーの実行
      SQLExecQuery con, sql
      'データソースとの接続を切断
      SQLClose con
  End Sub
サンプル 1-2 (ORACLE)
  Sub UpdateORA ()
      Dim con As Variant
      Dim sql As String
      'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
      '以下の 2 行は改行せずにコードを入力してください。
      con = SQLOpen("DSN=TstORA;DBQ=p:serverora1;UID=SCOTT;PWD=Tiger",
      , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'SQL 文定義
      'テーブル [売上] で [書店番号] が 7067 の [数量] を 100 に変更します
      sql = "UPDATE 売上 SET 数量=100 WHERE 書店番号='7067'"
      'クエリーの実行
      SQLExecQuery con, sql
      'データソースとの接続を切断
      SQLClose con
  End Sub
2. 外部データベース上にデータを追加する
次のサンプル モジュールでは、データソースとの接続を確立後、レコードの追加を
行っています。
サンプル 2-1 (SQLServer)
  Sub InsertSQL()
      Dim con As Variant
      Dim sql As String
      'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
      '以下の 2 行は改行せずにコードを入力してください。
      con = SQLOpen("DSN=TstSQL;SERVER=serversql1;UID=xx;DATABASE=pubs;
      PWD=Excel", , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'SQL 文定義
      'テーブル [売上] にレコードを追加します
      '以下の 2 行は改行せずにコードを入力してください。
      sql = "INSERT INTO 売上 VALUES ('6380', '7229', '92/05/10', 50,
      'Net 60', 'PS2091')"
      '上の 2 行は改行せずにコードを入力してください。
      'クエリーの実行
      SQLExecQuery con, sql
      'データソースとの接続を切断
      SQLClose con
  End Sub
サンプル 2-2 (ORACLE)
  Sub InsertORA()
      Dim con As Variant
      Dim sql As String
      'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
      '以下の 2 行は改行せずにコードを入力してください。
      con = SQLOpen("DSN=TstORA;DBQ=p:serverora1;UID=SCOTT;PWD=Tiger",
      , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'SQL 文定義
      'テーブル [売上] にレコードを追加します
      '以下の 2 行は改行せずにコードを入力してください。
      sql = "INSERT INTO 売上 VALUES ('6380', '7229', {d '1994/10/03'},
      50, 'Net 60', 'PS2091')"
      '上の 2 行は改行せずにコードを入力してください。
      'クエリーの実行
      SQLExecQuery con, sql
      'データソースとの接続を切断
      SQLClose con
  End Sub
3. 外部データベース上のデータを削除する
次のサンプル モジュールでは、データソースとの接続を確立後、レコードを削除す
るクエリーを実行しています。
サンプル 3-1 (SQLServer)
  Sub DelSQL()
      Dim con As Variant
      Dim sql As String
      'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
      '以下の 2 行は改行せずにコードを入力してください。
      con = SQLOpen("DSN=TstSQL;SERVER=serversql1;UID=xx;DATABASE=pubs;
      PWD=Excel", , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'SQL 文定義
      '書店番号が 6380 のレコードを削除
      sql = "DELETE FROM 売上 WHERE 書店番号='6380'"
      'クエリーの実行
      SQLExecQuery con, sql
      'データソースとの接続を切断
      SQLClose con
  End Sub
サンプル 3-2 (ORACLE)
  Sub DelORA()
      Dim con As Variant
      Dim sql As String
      'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
      '以下の 2 行は改行せずにコードを入力してください。
      con = SQLOpen("DSN=TstORA;DBQ=p:serverora1;UID=SCOTT;PWD=Tiger",
      , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'SQL 文定義
      '書店番号が 6380 のレコードを削除
      sql = "DELETE FROM 売上 WHERE 書店番号='6380'"
      'クエリーの実行
      SQLExecQuery con, sql
      'データソースとの接続を切断
      SQLClose con
  End Sub
4. ワークシート上のデータを元に外部データベース上のデータを変更する
次のサンプル モジュールでは、テーブル [売上] の指定した [書店番号] を変更
しています。
サンプル 4-1 (SQLServer)
  Sub UpdateSQL()
      Dim con As Variant
      Dim sql As String
      Dim UpdateCell As Range
      Worksheets("Sheet1").Activate
      MsgBox "指定した書店番号の数量を変更します"
      '変更をかける [書店番号] のセルをクリックさせる InputBox を表示させます
      '指定した Range を変数 UpdateCell にセット
      Set UpdateCell = Application.InputBox(prompt:= _
      "変更をかける書店番号のセルをクリックして下さい", Title:="変更セル", _
       Type:=8)
      'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
      '以下の 2 行は改行せずにコードを入力してください。
      con = SQLOpen("DSN=TstSQL;SERVER=serversql1;UID=xx;DATABASE=pubs;
      PWD=Excel", , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'ワークシート上には [書店番号] [注文番号] [日付] [数量] [支払] [書籍番号]
      'の順でデータが並んでいます
      'SQL 文定義
      '以下の 2 行は改行せずにコードを入力してください。
      sql = "UPDATE 売上 SET 数量=" & UpdateCell.Offset(0, 3).Formula &
      " WHERE 書店番号='" & UpdateCell.Formula & "'"
      '上の 2 行は改行せずにコードを入力してください。
      'SQL 文確認
      MsgBox sql
      'クエリーの実行
      SQLExecQuery con, sql
      'データソースとの接続を切断
      SQLClose connection
  End Sub
サンプル 4-2 (ORACLE)
  Sub UpdateORA()
      Dim con As Variant
      Dim sql As String
      Dim UpdateCell As Range
      Worksheets("Sheet1").Activate
      MsgBox "指定した書店番号の数量を変更します"
      '変更をかける [書店番号] のセルをクリックさせる InputBox を表示させます
      '指定した Range を変数 UpdateCell にセット
      Set UpdateCell = Application.InputBox(prompt:= _
      "変更をかける書店番号のセルをクリックして下さい", Title:="変更セル", _
      Type:=8)
      'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
      '以下の 2 行は改行せずにコードを入力してください。
      con = SQLOpen("DSN=TstORA;DBQ=p:serverora1;UID=SCOTT;PWD=Tiger",
      , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'ワークシート上には [書店番号] [注文番号] [日付] [数量] [支払] [書籍番号]
      'の順でデータが並んでいます
      'SQL 文定義
      '以下の 2 行は改行せずにコードを入力してください。
      sql = "UPDATE 売上 SET 数量=" & UpdateCell.Offset(0, 3).Formula &
      " WHERE 書店番号='" & UpdateCell.Formula & "'"
      '上の 2 行は改行せずにコードを入力してください。
      'SQL 文確認
      MsgBox sql
      'クエリーの実行
      SQLExecQuery con, sql
      'データソースとの接続を切断
      SQLClose connection
  End Sub
5. ワークシート上のデータを元に外部データベース上のデータを追加する
次のサンプル モジュールでは、ワークシート "Sheet1" の セル "A1" から
 [書店番号] [注文番号] [日付] [数量] [支払] [書籍番号] の順にデータが
入力されており、 [データ] - [フィルタ] コマンドで外部データベースに
追加するレコードを抽出してから、そのデータを SQLExecQuery で追加して
います。
サンプル 5-1 (SQLServer)
  Sub InsertSQL()
  Dim con As Variant
  Dim 書店番号, 注文番号, 支払, 書籍番号 As String
  Dim vDate, 日付 As Date
  Dim 数量 As Integer
  Dim sql As String
      '追加するデータの日付を入力させ 変数 vDate にセット
      vDate = Application.InputBox(prompt:= _
      "追加するデータの日付を入力して下さい" & Chr(10) & "例:1994/10/03", _
      Type:=1)
      Worksheets("Sheet1").Activate
      'Sheet1!J2 に日付を書き込む (検索条件範囲の条件)
      Range("J2").Formula = Format(vDate, "yyyy/mm/dd")
      'データが入力されている 左上端 (Sheet1!A1) を選択
      Range("A1").End(xlDown).Select
      'データの入力されている最終行、列を選択
      ActiveCell.End(xlToRight).Select
      '[データ]-[フィルタ]-[フィルタオプションの設定] で [リスト範囲] をセル "A1"
      'から現在のセル位置までを設定、[検索条件範囲] をセル範囲 "H1:M2" まで設定
      Range("A1:" & ActiveCell.Address).AdvancedFilter Action:= _
      xlFilterInPlace, CriteriaRange:=Range("H1:M2"), Unique:=False
      'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
      '以下の 2 行は改行せずにコードを入力してください。
      con = SQLOpen("DSN=TstSQL;SERVER=serversql1;UID=xx;DATABASE=pubs;
      PWD=Excel", , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'フィルタで抽出されたレコードの行番号をそれぞれ取得し 1 行づつ SQL 文を
      '定義し実行
      '以下の 2 行は改行せずにコードを入力してください。
      For Each aa In Range("A1").CurrentRegion.Resize(, 1).
      SpecialCells(xlVisible)
      '上の 2 行は改行せずにコードを入力してください。
          If aa.Row <> 1 Then
              'データは A 列から [書店番号] [注文番号] [日付] [数量] [支払]
              '[書籍番号] の順に入っています
              書店番号 = Cells(aa.Row, 1).Formula
              注文番号 = Cells(aa.Row, 2).Formula
              日付 = Format(Cells(aa.Row, 3).Formula, "yyyy/mm/dd")
              数量 = Cells(aa.Row, 4).Formula
              支払 = Cells(aa.Row, 5).Formula
              書籍番号 = Cells(aa.Row, 6).Formula
              '以下の 3 行は改行せずにコードを入力してください。
              sql = "INSERT INTO 売上 VALUES ('" & 書店番号 & "', '" &
              注文番号 & "', '" & 日付 & "', " & 数量 & ", '" & 支払 &
              "', '" & 書籍番号 & "')"
              '上の 3 行は改行せずにコードを入力してください。
              'クエリーの実行
              SQLExecQuery con, sql
          End If
      Next
      'データソースとの接続を切断
      SQLClose con
  End Sub
サンプル 5-2 (ORACLE)
  Sub InsertORA()
  Dim con As Variant
  Dim 書店番号, 注文番号, 支払, 書籍番号 As String
  Dim vDate, 日付 As Date
  Dim 数量 As Integer
  Dim sql As String
      '追加するデータの日付を入力させ 変数 vDate にセット
      vDate = Application.InputBox(prompt:= _
      "追加するデータの日付を入力して下さい" & Chr(10) & "例:1994/10/03", _
      Type:=1)
      Worksheets("Sheet1").Activate
      'Sheet1!J2 に日付を書き込む (検索条件範囲の条件)
      Range("J2").Formula = Format(vDate, "yyyy/mm/dd")
      'データが入力されている 左上端 (Sheet1!A1) を選択
      Range("A1").End(xlDown).Select
      'データの入力されている最終行、列を選択
      ActiveCell.End(xlToRight).Select
      '[データ]-[フィルタ]-[フィルタオプションの設定] で [リスト範囲] をセル "A1"
      'から現在のセル位置までを設定、[検索条件範囲] をセル範囲 "H1:M2" まで設定
      Range("A1:" & ActiveCell.Address).AdvancedFilter Action:= _
      xlFilterInPlace, CriteriaRange:=Range("H1:M2"), Unique:=False
      'データソースとの接続を確立し SQLOpen のリターン値を変数 con にセット
      '以下の 2 行は改行せずにコードを入力してください。
      con = SQLOpen("DSN=TstORA;DBQ=p:serverora1;UID=SCOTT;PWD=tiger",
      , 2)
      '上の 2 行は改行せずにコードを入力してください。
      'フィルタで抽出されたレコードの行番号をそれぞれ取得し 1 行づつ SQL 文を
      '定義し実行
      '以下の 2 行は改行せずにコードを入力してください。
      For Each aa In Range("A1").CurrentRegion.Resize(, 1).
      SpecialCells(xlVisible)
      '上の 2 行は改行せずにコードを入力してください。
          If aa.Row <> 1 Then
              'データは A 列から [書店番号] [注文番号] [日付] [数量] [支払]
              '[書籍番号] の順に入っています
              書店番号 = Cells(aa.Row, 1).Formula
              注文番号 = Cells(aa.Row, 2).Formula
              日付 = Format(Cells(aa.Row, 3).Formula, "yyyy/mm/dd")
              数量 = Cells(aa.Row, 4).Formula
              支払 = Cells(aa.Row, 5).Formula
              書籍番号 = Cells(aa.Row, 6).Formula
              '以下の 2 行は改行せずにコードを入力してください。
              sql = "INSERT INTO 売上 VALUES ('" & 書店番号 & "', '" &
              注文番号 & "', {d '" & 日付 & "'}, " & 数量 & ", '" & 支払 &
              "', '" & 書籍番号 & "')"
              '上の 3 行は改行せずにコードを入力してください。
              'クエリーの実行
              SQLExecQuery con, sql
          End If
      Next
      'データソースとの接続を切断
      SQLClose con
  End Sub