こんにちは！ 健史です。
Excel-VBAにて、ExcelのセルにセットしたデータをORACLEデータの特定項目へ更新するプログラムを作成しました。
概要の説明
システム構築していると本稼働後の運用設計で、特定の設定項目を不定期、もしくは、定期的に変更しなければならない場合があります。
例えば、月度や年度などで、頻繁ではないけれど発生するものが。
しかし、その値だけを変更するメンテナンス用オンラインプログラムを作成するには、時間や費用がなかったり。
エンドユーザが、SQLDeveloperなどのデータベース管理ツールで、ＳＱＬでメンテナンスするにはリスクがあります。
結局「システム要員に依頼して、データベース管理ツールでメンテナンスする」ことにとなり、ずーっとその運用が続きます。
そのようなことにExcel-VBAで対応するプログラムを作成してみました。
設定事項シートは以下です。
プログラム
更新前データ抽出 SELECT
Sub TEST3()
'コンスタント
CNS_SELECT = "SELECT ＴＥＬ FROM 顧客マスタ WHERE コード = '00003'"
'Oracleアクセス用変数定義
Dim USER_ID, PASSWORD As String
Dim PROVIDER, DATA_SOURCE As String
'設定シートからセット
USER_ID = Sheets(1).Cells(2, 2)
PASSWORD = Sheets(1).Cells(3, 2)
PROVIDER = Sheets(1).Cells(4, 2)
DATA_SOURCE = Sheets(1).Cells(5, 2)
'インスタンス生成 データベース接続
Dim conn As New ADODB.Connection
'Oracleの場合
conn.ConnectionString = _
"PROVIDER=" & PROVIDER & ";" & _
"Data Source=" & DATA_SOURCE & ";" & _
"USER ID=" & USER_ID & ";" & _
"PASSWORD=" & PASSWORD & ";"
'ＤＢオープン
conn.Open
'インスタンス生成 データアクセス
Dim recset As New ADODB.Recordset
'ＤＢ 読み込み
recset.Source = CNS_SELECT
recset.ActiveConnection = conn
'接続開始
recset.Open
'データをＤＢからExcelにセット
Sheets(1).Cells(10, 2) = recset(0)
'接続終了
recset.Close
'ＤＢクローズ
conn.Close
Set recset = Nothing
Set conn = Nothing
'終了処理
Sheets(1).Cells(10, 3) = Format(Now(), "YYYYMMDD_HHMMSS")
MsgBox "処理終了！"
End Sub
更新 UPDATE
Sub TEST4()
'コンスタント定義
Const CNS_UPDATE1 = "UPDATE 顧客マスタ SET ＴＥＬ = '"
Const CNS_UPDATE2 = "' WHERE コード = '00003'"
Const CNS_COMMIT = 100
'変数定義
Dim ix1, ix1_max As Long
Dim cnt_update, cnt_commit As Long
'Oracleアクセス用変数定義
Dim USER_ID, PASSWORD As String
Dim PROVIDER, DATA_SOURCE As String
Dim UPDATE_DATA As String
Dim strSQL As String
'設定シートからセット
USER_ID = Sheets(1).Cells(2, 2)
PASSWORD = Sheets(1).Cells(3, 2)
PROVIDER = Sheets(1).Cells(4, 2)
DATA_SOURCE = Sheets(1).Cells(5, 2)
UPDATE_DATA = Sheets(1).Cells(11, 2)
'インスタンス生成 データベース接続
Dim conn As New ADODB.Connection
'Oraclenの場合
conn.ConnectionString = _
"Provider=" & PROVIDER & ";" & _
"Data Source=" & DATA_SOURCE & ";" & _
"USER ID=" & USER_ID & ";" & _
"Password=" & PASSWORD & ";"
'ＤＢオープン
conn.Open
'カウントの初期クリア
cnt_update = 0
cnt_commit = 1
'UPDATE処理
strSQL = CNS_UPDATE1 & UPDATE_DATA & CNS_UPDATE2
conn.Execute (strSQL)
' カウントアップ
cnt_update = cnt_update + 1
cnt_commit = cnt_commit + 1
' コミット
Select Case cnt_commit
Case Is > CNS_COMMIT
strSQL = "COMMIT"
conn.Execute (strSQL)
cnt_commit = 1
End Select
'コミット(最終)
strSQL = "COMMIT"
conn.Execute (strSQL)
'ＤＢクローズ
conn.Close
Set conn = Nothing
'後処理
Sheets(1).Cells(11, 3) = Format(Now(), "YYYYMMDD_HHMMSS")
MsgBox "更新件数：" & cnt_update & vbCrLf & "処理終了！"
'セーブ
ThisWorkbook.Save
End Sub
更新後データ抽出 SELECT
Sub TEST5()
'コンスタント
CNS_SELECT = "SELECT ＴＥＬ FROM 顧客マスタ WHERE コード = '00003'"
'Oracleアクセス用変数定義
Dim USER_ID, PASSWORD As String
Dim PROVIDER, DATA_SOURCE As String
'設定シートからセット
USER_ID = Sheets(1).Cells(2, 2)
PASSWORD = Sheets(1).Cells(3, 2)
PROVIDER = Sheets(1).Cells(4, 2)
DATA_SOURCE = Sheets(1).Cells(5, 2)
'インスタンス生成 データベース接続
Dim conn As New ADODB.Connection
'Oracleの場合
conn.ConnectionString = _
"PROVIDER=" & PROVIDER & ";" & _
"Data Source=" & DATA_SOURCE & ";" & _
"USER ID=" & USER_ID & ";" & _
"PASSWORD=" & PASSWORD & ";"
'ＤＢオープン
conn.Open
'インスタンス生成 データアクセス
Dim recset As New ADODB.Recordset
'ＤＢ 読み込み
recset.Source = CNS_SELECT
recset.ActiveConnection = conn
'接続開始
recset.Open
'データをＤＢからExcelにセット
Sheets(1).Cells(12, 2) = recset(0)
'接続終了
recset.Close
'ＤＢクローズ
conn.Close
Set recset = Nothing
Set conn = Nothing
'終了処理
Sheets(1).Cells(12, 3) = Format(Now(), "YYYYMMDD_HHMMSS")
MsgBox "処理終了！"
End Sub
補足
ボタンを３つ用意しておき、それぞれクリックして実行します。
１.変更前の値を確認するため、Excel上に値を表示する
２.更新する値を設定しておき、更新する
３.変更後の値を確認するため、Excel上に値を表示する
それぞれに実行した日付・時刻を表示することで、各処理の実行を認識できます。
また、例ではユーザＩＤ／パスワード／データベース設定情報も設定シートに記載しています。
セキュリティ上リスクの考慮し、プログラム内に定義しプログラムパスワードロックを掛けるといった対応であればリスク回避できます。
やり方はいろいろあると思いますが、一例ということでお含みおきいただければと思います。
データベースは以下です。
実行イメージ
実行結果は以下です。
コメント