ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] [µo°Ý] ¦³Ãö©óSQL¤£¦s¦b«hinsert¦s¦b«hupdate

[µo°Ý] [µo°Ý] ¦³Ãö©óSQL¤£¦s¦b«hinsert¦s¦b«hupdate

½Ð°Ý¦U¦ì¤j¤j¦³Ãö©óSQL¤£¦s¦b«hinsert¦s¦b«hupdateªº°ÝÃD

§Ú­n±NExcelªºTable Update¨ìAccess¸ê®Æ®w¡A·í¸ê®Æ¦s¦b®É§YUpdate¤£¦s¦b®É§Yinsert
¦]¬°§Ú«D±M·~¡A¬G¤Wºô¬d¸ß¤@¯ë¤Hªº¼gªk¡A¥H¤U¤T¬q»yªk¤À§O(Select, Update, Insert)³£¯à°õ¦æ¡A¦ý§Ú¸Ó¦p¦ó¾ã¦X¡A¤~¯à¹F¨ì©O?
  1. Private baglan As Object, rs As Object

  2. Sub baglanti()
  3.     Set baglan = CreateObject("adodb.connection")
  4.     #If VBA7 And Win64 Then
  5.     baglan.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\SKYEYEDatabase.mdb"
  6.     #Else
  7.     baglan.Open "provider=microsoft.jet.oledb.4.0;data source=" & ThisWorkbook.Path & "\SKYEYEDatabase.mdb"
  8.     #End If
  9. End Sub

  10. Sub FP_Database_Acess()
  11.     Sheets("Menu-FP").Visible = True
  12.     Sheets("Menu-FP").Select
  13.     Dim shFC As Worksheet
  14.     'Dim aArticle As String
  15.     Set shFC = Sheets("Menu-FP")
  16.     EndR = shFC.Range("A65536").End(xlUp).Row
  17.    

  18.     For R = 7 To EndR
  19.         CustomerID = shFC.Cells(R, 1)
  20.         Customer = "'" & shFC.Cells(R, 2) & "'"
  21.         SPECID = shFC.Cells(R, 3)
  22.         aArticle = "'" & shFC.Cells(R, 4) & "'"
  23.         MD = "'" & shFC.Cells(R, 5) & "'"
  24.         Qty = shFC.Cells(R, 6)
  25.         Area = "'" & shFC.Cells(R, 7) & "'"
  26.         Remark = "'" & shFC.Cells(R, 8) & "'"
  27.         PNL = "'" & shFC.Cells(R, 9) & "'"
  28.         Grade = "'" & shFC.Cells(R, 10) & "'"
  29.         Package = "'" & shFC.Cells(R, 11) & "'"
  30.         ICVID = shFC.Cells(R, 12)
  31.         Path = "'" & shFC.Cells(R, 13) & "'"
  32.         File = "'" & shFC.Cells(R, 14) & "'"
  33.         
  34.         Call baglanti
  35.         
  36.         Set rs1 = baglan.Execute("(SELECT CustomerID=" & CustomerID & ",Customer= " & Customer & ", SPECID=" & SPECID & ", MaterialDescription=" & MD & ",Qty=" & Qty & ",Area=" & Area & ",Remark=" & Remark & ", ProductNameLIMS=" & PNL & ",Grade=" & Grade & ", Package=" & Package & ",ICVID=" & ICVID & ",Path=" & Path & ",File=" & File & "  FROM [FC] WHERE Article= " & aArticle & ")")

  37.         Set rs2 = baglan.Execute("(UPDATE [FC] SET CustomerID=" & CustomerID & ",Customer= " & Customer & ", SPECID=" & SPECID & ", MaterialDescription=" & MD & ",Qty=" & Qty & ",Area=" & Area & ",Remark=" & Remark & ", ProductNameLIMS=" & PNL & ",Grade=" & Grade & ", Package=" & Package & ",ICVID=" & ICVID & ",Path=" & Path & ",File=" & File & " WHERE Article= " & aArticle & ")")

  38.         Set rs3 = baglan.Execute("INSERT INTO [FC] (CustomerID,Customer,SPECID,Article,MaterialDescription,Qty,Area,Remark,ProductNameLIMS,Grade,Package,ICVID,Path,File) Values (" & CustomerID & "," & Customer & "," & SPECID & "," & aArticle & "," & MD & "," & Qty & "," & Area & "," & Remark & "," & PNL & "," & Grade & "," & Package & "," & ICVID & "," & Path & "," & File & ")")
  39.         
  40.         Set baglan = Nothing: Set rs1 = Nothing: Set rs2 = Nothing: Set rs3 = Nothing:
  41.    
  42.     Next
  43.     MsgBox ("DONE")
  44. End Sub
½Æ»s¥N½X

¨Ñ§A°Ñ¦Ò¡Ainsert into ¾ãªí®æ§@ªk¡AÅý§A¦³¤è¦V¥h«ä¦Ò¡C
https://reurl.cc/xDeqmZ

¨ä¥L»yªk¨Ñ§A°Ñ¦Ò
1. ¥u·s¼W¤£¦s¦bªº
strCommandText = "INSERT INTO TABLE select a.* from [Excel 12.0;Database=" & ActiveWorkbook.FullName & "].[" & myTable & "$" & Sheets("TABLE ").Range("A1").CurrentRegion.Address(0, 0) & "] a " & _ "left join " & Up2Table & " b on a.¾Ì³æ=b.¾Ì³æ where b.¾Ì³æ is null"

2. UPDATE
strCommandText = "UPDATE " & Up2Table & " a " & _",[Excel 12.0;Imex=0;Database=" & ThisWorkbook.FullName & ";].[" & myTable & "$" & Sheets("svrOrdMaster").Range("A1").CurrentRegion.Address(0, 0) & "] b " & _"SET a.¾Ì³æ=b.¾Ì³æ " & _"WHERE a.¾Ì³æ=b.¾Ì³æ"

TOP

        ÀR«ä¦Û¦b : ¯à·F¤£·F¡A¤£¦p­W·F¹ê·F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD