' Write by m_o_co Public Sub Do_Renzoku_KeyChg(KeyNam As String, DataStr As String, Optional Chg_Flg As Byte) '======================== ' 配列で受け取って投げる(雑設計) ' ' 0スタート、偶数番目がBefo_Data、続く奇数番目が、Chg_Data。 '========================
Public Sub Do_KeyChg(KeyName As String, Befo_Data As Variant, Chg_Data As Variant, Optional Chg_Flg As Byte) '======================== ' テーブル構成情報収集依存 ' ' Chg_Flg … Chg_Dataが既に置換前から存在していた場合 ' … 1 エラーとせず、そのまま置換する ' … 0 エラーとして、中断する ' … 2 チェックのみで、置換しない '======================== Dim DB As DAO.Database, RS As DAO.Recordset Dim tgTBLn() As String, i As Long, j As Long Dim wkFlg As Boolean, wkPceSQL As String Dim Jump_Flg As Integer, wkStr As String
If Chg_Flg = 2 Then wkFlg = True
Set DB = CurrentDb() Set RS = DB.OpenRecordset("Select テーブル名 from テーブル構成情報収集 Where 項目名='" & KeyName & "'", dbOpenSnapshot)
Select Case TypeName(Chg_Data) Case "String" wkPceSQL = "'★_☆'" Case "Integer", "Double", "Boolean" wkPceSQL = "★_☆" End Select
Jump_Flg = 1 On Error GoTo Err_Me
For i = 0 To UBound(tgTBLn) Set RS = DB.OpenRecordset("Select * from " & tgTBLn(i) & " Where " & KeyName & "=" & Replace(wkPceSQL, "★_☆", Chg_Data), dbOpenSnapshot)
For i = 0 To UBound(tgTBLn) Set RS = DB.OpenRecordset("Select * from " & tgTBLn(i) & " Where " & KeyName & "=" & Replace(wkPceSQL, "★_☆", Befo_Data), dbOpenSnapshot)
If Not RS.EOF Then RS.MoveLast Debug.Print "-------- " & tgTBLn(i) & " " & RS.RecordCount & "件 --------" RS.MoveFirst
wkStr = "" For j = 0 To RS.Fields.Count - 1 If wkStr <> "" Then wkStr = wkStr & "," wkStr = wkStr & RS.Fields(j).Name Next Debug.Print wkStr
wkStr = "" Do Until RS.EOF For j = 0 To RS.Fields.Count - 1 If wkStr <> "" Then wkStr = wkStr & "," wkStr = wkStr & RS(j) Next Debug.Print wkStr wkStr = ""
RS.MoveNext Loop
End If
Go_Next_Table_2: Next
Jump_Flg = 3
Debug.Print "-------- 対象コードの現状確認 終了 --------"
If Not wkFlg Then Debug.Print _ "-----------------------------------------------------" & vbCrLf & _ " 置換開始" & vbCrLf & _ "-----------------------------------------------------"
For i = 0 To UBound(tgTBLn) DB.Execute "Update " & tgTBLn(i) & " Set " & KeyName & "=" & Replace(wkPceSQL, "★_☆", Chg_Data) & " Where " & KeyName & "=" & Replace(wkPceSQL, "★_☆", Befo_Data) Go_Next_Table_3: Next
Jump_Flg = 4
Debug.Print "-------- 終了 --------"
Else
Select Case Chg_Flg Case 0: Debug.Print "-------- 現存あり、置換せず終了 --------" Case 2: Debug.Print "-------- チェック 終了 --------" End Select
Select Case Err.Number Case 3078 Resume Go_Next_Table Case 3061 If Sgn(tgTBLn) <> 0 And InStr(Err.Description, "1") > 0 Then Debug.Print "--------【Skip】 " & tgTBLn(i) & " ターゲットフィールドなし --------" Resume Go_Next_Table Else GoTo Other_Err End If