標題: [發問] [發問] 有關於SQL不存在則insert存在則update [打印本頁]

---

作者: taiwan16699    時間: 2019-11-17 18:12     標題: [發問] 有關於SQL不存在則insert存在則update

請問各位大大有關於SQL不存在則insert存在則update的問題

我要將Excel的Table Update到Access資料庫，當資料存在時即Update不存在時即insert
因為我非專業，故上網查詢一般人的寫法，以下三段語法分別(Select, Update, Insert)都能執行，但我該如何整合，才能達到呢?

1. Private baglan As Object, rs As Object
   
2. 
   
3. Sub baglanti()
   
4.     Set baglan = CreateObject("adodb.connection")
   
5.     #If VBA7 And Win64 Then
   
6.     baglan.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\SKYEYEDatabase.mdb"
   
7.     #Else
   
8.     baglan.Open "provider=microsoft.jet.oledb.4.0;data source=" & ThisWorkbook.Path & "\SKYEYEDatabase.mdb"
   
9.     #End If
   
10. End Sub
    
11. 
    
12. Sub FP_Database_Acess()
    
13.     Sheets("Menu-FP").Visible = True
    
14.     Sheets("Menu-FP").Select
    
15.     Dim shFC As Worksheet
    
16.     'Dim aArticle As String
    
17.     Set shFC = Sheets("Menu-FP")
    
18.     EndR = shFC.Range("A65536").End(xlUp).Row
    
19.    
    
20. 
    
21.     For R = 7 To EndR
    
22.         CustomerID = shFC.Cells(R, 1)
    
23.         Customer = "'" & shFC.Cells(R, 2) & "'"
    
24.         SPECID = shFC.Cells(R, 3)
    
25.         aArticle = "'" & shFC.Cells(R, 4) & "'"
    
26.         MD = "'" & shFC.Cells(R, 5) & "'"
    
27.         Qty = shFC.Cells(R, 6)
    
28.         Area = "'" & shFC.Cells(R, 7) & "'"
    
29.         Remark = "'" & shFC.Cells(R, 8) & "'"
    
30.         PNL = "'" & shFC.Cells(R, 9) & "'"
    
31.         Grade = "'" & shFC.Cells(R, 10) & "'"
    
32.         Package = "'" & shFC.Cells(R, 11) & "'"
    
33.         ICVID = shFC.Cells(R, 12)
    
34.         Path = "'" & shFC.Cells(R, 13) & "'"
    
35.         File = "'" & shFC.Cells(R, 14) & "'"
    
36.         
    
37.         Call baglanti
    
38.         
    
39.         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 & ")")
    
40. 
    
41.         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 & ")")
    
42. 
    
43.         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 & ")")
    
44.         
    
45.         Set baglan = Nothing: Set rs1 = Nothing: Set rs2 = Nothing: Set rs3 = Nothing:
    
46.    
    
47.     Next
    
48.     MsgBox ("DONE")
    
49. End Sub

複製代碼

---

作者: allenlin99    時間: 2019-11-25 21:56

供你參考，insert into 整表格作法，讓你有方向去思考。
https://reurl.cc/xDeqmZ

其他語法供你參考
1. 只新增不存在的
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.憑單"

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)