返回列表 上一主題 發帖

[發問] [發問] 有關於SQL不存在則insert存在則update

taiwan16699

帖子
6 
主題
3 
精華
0 
積分
10 
點名
0  
作業系統
Windows 7 
軟體版本
1 
閱讀權限
10 
註冊時間
2013-5-2 
最後登錄
2019-11-21 

論壇贊助勳章
1# 跳轉到 » 倒序看帖
打印
tT
發表於 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

  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

  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.    

  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 & ")")

  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 & ")")

  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

帖子
4 
主題
0 
精華
0 
積分
54 
點名
0  
作業系統
WIN7 
軟體版本
2010 
閱讀權限
20 
性別
男 
來自
彰化 
註冊時間
2014-3-22 
最後登錄
2020-1-14 

論壇贊助勳章
2#
發表於 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.憑單"

TOP

        靜思自在 : 小事不做、大事難成。
返回列表 上一主題