Due to the needs of the company, it is necessary to change the transfer mode of IPOS friendly terminal group and IPOS friendly terminal group

Because this operation is very troublesome and tedious, close-up this template, batch update and one click completion

excel table format

2 tables in total

At present, each store has 2 friendly terminal groups

1. All terminal permissions are horizontal adjustment

2. The permissions of the friendly terminal group are inventory query and horizontal adjustment

Upper code

First clear the three watches

Main table of IPOs ﹐ yhzdz friendly terminal group

List of IPOs ﹣ yhzmx friendly terminal group

IPOs dpyhzd detailed data of friendly terminal groups of each store, 2 lines of data for each store

I have all terminals with ID 9 here. This one doesn't need to be changed, so it's not deleted

'Set the following global variables 
Dim cn As Object
Dim rs As Object

 Sub Database connection mysql()
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.RecordSet")
    cn = "Driver={MySQL ODBC 5.3 Unicode Driver};Server=IP address;Port=3306;Database=Database name;User=root; Password=Database password;Option=3;"
      cn.Open
 End Sub

Sub Delete original()
Call Database connection mysql
sql = "delete from ipos_yhzdz where id <> 9 "
sql2 = "delete from ipos_yhzdzmx where pid <> 9 "
sql3 = "delete from ipos_dpyhzd"
cn.Execute (sql)
cn.Execute (sql2)
cn.Execute (sql3)
cn.Close

End Sub

Next, perform the modification

Upper code

Sub Query all stores in the area()
Application.ScreenUpdating = False   'Turn off screen refresh
Sheets("1").Select     'Select form 1 to work 
Call Delete original   
Call Database connection mysql
x = 17    'This customization depends on your ipos_yhzdz Inside table ID As many as you want, you can go to the next position. Avoid repetition. Doc no. of main table
For y = 1 To Sheets("sheet2").Cells(2, 256).End(1).Column    'Outer loop control column
d_name = ""    'Name initialization

  Sheets("1").Cells.Clear   'Clear sheet 1 data first
    For i = 2 To 20    'Let's see how many areas are included in each of your regions. Inner loop control line
    If Sheets("sheet2").Cells(i, y) = "" Then    'Determine whether there is a region name
    Else
    d_name = d_name & "," & Sheets("sheet2").Cells(i, y)    'Note name
    
       sql = "select id from com_base_kehu where qy_id in (select id from com_base_quyu where qymc='" & Sheets("sheet2").Cells(i, y) & "') and ty=0"   'Query the store corresponding to the area name ID
      If rs.State = 1 Then rs.Close
      rs.Open sql, cn, 1, 1
              If Range("a1") = "" Then
                Sheets("1").Range("A" & [A65536].End(xlUp).Row).CopyFromRecordset rs
              Else
                Sheets("1").Range("A" & [A65536].End(xlUp).Row + 1).CopyFromRecordset rs
              End If   'Worksheet 1 lists the queried ID
        End If
    Next i
time1 = (Now - 70 * 365 - 19) * 86400 - 8 * 3600    'time stamp
sql1 = "INSERT INTO ipos_yhzdz (Id, pid, tn_id, org_id, yhzddm, yhzdmc, bz, row_no, alterdate, lastchanged, yhzd_kzz) VALUES (" & x & ", NULL, '0', '1', 'zdz0000" & x & "', '" & d_name & "', '" & d_name & "', NULL, '" & time1 & "', '" & Now & "', '0')"  
cn.Execute (sql1)   'Insert main table document
    For t = 1 To Sheets("1").[A65536].End(xlUp).Row    'Start to update the list and store detail terminal group here
    sql3 = "INSERT INTO ipos_yhzdzmx (pid, tn_id, zd_id, bz, row_no, alterdate, lastchanged, jtzd, zdjs, zdtj) VALUES ( '" & x & "', '0', '" & Sheets("1").Cells(t, 1) & "', NULL, NULL, '" & time1 & "', '" & Now & "', '1', NULL, NULL)"  
    sql4 = "INSERT INTO ipos_dpyhzd(pid,zdz_id,hd,kc,xs,hdkz,hdthkz,kqyh, yhzd_kzz,ck_cus,mod_cus,ck_vip,mod_vip,ck_vcard,ck_czk)VALUES('" & Sheets("1").Cells(t, 1) & "','" & x & "','1','1','0','0','0','0','0','0','0','0','0','0','0')"   'Update query of the region
    sql5 = "INSERT INTO ipos_dpyhzd(pid,zdz_id,hd,kc,xs,hdkz,hdthkz,kqyh, yhzd_kzz,ck_cus,mod_cus,ck_vip,mod_vip,ck_vcard,ck_czk)VALUES('" & Sheets("1").Cells(t, 1) & "','9','1','0','0','0','0','0','0','0','0','0','0','0','0')"    'Update the horizontal adjustment of all terminals
    cn.Execute (sql3)
    cn.Execute (sql4)
    cn.Execute (sql5)
 Next t
x = x + 1    'Main table doc No+1
Next y
cn.Close
Sheets("sheet2").Select
Application.ScreenUpdating = True
MsgBox "Add complete!"
End Sub