CODEWITHSUNDEEP
sqlexceldatabasevbams-access
luisvv93
luisvv93

Reputation: 63

Determine if record exists when updating Access database using Excel VBA

I am trying to update records, or create records if the unique ID does not exist.

The code gives me an error telling me that it would create duplicate values.

I need to include this in my code "SQL: If Exists Update Else Insert".

Sub Upload_Excel_to_Access()

Dim wbpath As String

wbpath = Application.ActiveWorkbook.Path

Dim con As Object '' ADODB.Connection
Set con = CreateObject("ADODB.Connection") '' New ADODB.Connection
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data source=\\at\HRS SD Performance Data\Capacity DB.accdb;"
con.Execute _
"INSERT INTO AssigenedVol_tbl " & _
"SELECT * FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Users\luga\Desktop\Databasetest\DB Macro Test.xlsm].[rawdata$]"
con.Close
Set con = Nothing
End Sub

The table name is "AssigenedVol_tbl"

Fields are: Process_Identifier, Login, Volume, effDate, ID_Unique (This is the primary key in the database)

Upvotes: 6

Views: 983

Answers (1)

Eric Hofer
Eric Hofer

Reputation: 65

Modify the insert statement to check for the existence of the key. Given what you explained, that would be

Sub Upload_Excel_to_Access()

  Dim wbpath As String

  wbpath = Application.ActiveWorkbook.Path

  Dim con As Object '' ADODB.Connection
  Set con = CreateObject("ADODB.Connection") '' New ADODB.Connection
  con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data source=\\at\HRS SD Performance Data\Capacity DB.accdb;"
  con.Execute _
    "INSERT INTO AssigenedVol_tbl " & _
    "SELECT SRC.* FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Users\luga\Desktop\Databasetest\DB Macro Test.xlsm].[rawdata$] " _
     & " AS SRC " _
     & "WHERE NOT EXISTS (select 1 from AssigenedVol_Tbl CHK WHERE CHK.ID_Unique = SRC.ID_Unique)"
  con.Close
  Set con = Nothing
End Sub

Note, the &'s - were done just to focus on the fact that your SRC table is being labelled, and you're checking it as CHK.

Upvotes: 1

Related Questions