Updating Access through Excel using SQL in a macro

[bl4met]

I have been trying different ways to make this work and I wasn't 100% sure if this needed to go in the Excel or the Access forums.

I have a CSV that I have a excel macro created to do some formatting and then export the data into Access. that part works. I also have it checking for duplicate entries and skipping them. The problem that i run into is that not all my duplicates are truly duplicate, there have been updates and now i need to update Access. I have 2 fields, my primary key which is what I'm primarily matching on and then I'm checking if its been updated by checking another field "Last Changed" If this field doesn't match then I need to update the record.

At this point I'm trying to use a SQL UPDATE and I now believe that i have it formatted correct but when it gets to a record that needs to be updated I get an error: Run-time error '-2147217887 (80040e21)': The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. I'm not sure what I'm doing wrong. the only other thing i could think to do would be to check each field to see if it has changed and just run the query against that but I feel like i would run into the same issue.
This is how I'm connecting to Access if that helps

strTableName = "TableName"
strDBLocation = "\\xxxx\userhome\xxxx\xxxx\xxxxDatabase\Database Backend.accdb"
strConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strDBLocation & ";"

Set cn = New ADODB.Connection
cn.Open strConnect
Set rs = New ADODB.Recordset
rs.Open strTableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable

And this is the Query I'm using:

strSQL = "UPDATE " & strTableName & " "
strSQL = strSQL & "SET [Work Order ID] = '" & Range("B" & r).Value & "', "
strSQL = strSQL & "[JOBSTATUS] = '" & Range("C" & r).Value & "', "
strSQL = strSQL & "[SUBSTATUS] = '" & Range("D" & r).Value & "', "
strSQL = strSQL & "[Job Status] = '" & Range("E" & r).Value & "', "
strSQL = strSQL & "[STATUSDATETIME] = #" & Range("F" & r).Value & "#, "
strSQL = strSQL & "[Schedule Date] = #" & Range("G" & r).Value & "#, "
strSQL = strSQL & "[TZI] = '" & Range("H" & r).Value & "', "
strSQL = strSQL & "[Slot Start] = " & Range("I" & r).Value & ", "
strSQL = strSQL & "[Slot End] = " & Range("J" & r).Value & ", "
strSQL = strSQL & "[PRIORITY] = '" & Range("K" & r).Value & "', "
strSQL = strSQL & "[SMS Tech ID] = '" & Range("L" & r).Value & "', "
strSQL = strSQL & "[FSRUSERUID] = " & Range("M" & r).Value & ", "
strSQL = strSQL & "[Job ID] = '" & Range("N" & r).Value & "', "
strSQL = strSQL & "[DISPATCHERID] = '" & Range("O" & r).Value & "', "
strSQL = strSQL & "[KEY1] = '" & Range("P" & r).Value & "', "
strSQL = strSQL & "[Order Type] = '" & Range("Q" & r).Value & "', "
strSQL = strSQL & "[KEY2] = '" & Range("R" & r).Value & "', "
strSQL = strSQL & "[WO Reason Code] = '" & Range("S" & r).Value & "', "
strSQL = strSQL & "[Order Date/Time] = #" & Range("T" & r).Value & "#, "
strSQL = strSQL & "[ESTSTDATETIME] = #" & Range("U" & r).Value & "#, "
strSQL = strSQL & "[TECHCOMPLETED] = '" & Range("V" & r).Value & "', "
strSQL = strSQL & "[ROUTINGAREACODE] = '" & Range("W" & r).Value & "', "
strSQL = strSQL & "[Routing Area] = '" & Range("X" & r).Value & "', "
strSQL = strSQL & "[Last Changed] = #" & Range("Y" & r).Value & "#, "
strSQL = strSQL & "[Account ID] = '" & Range("Z" & r).Value & "', "
strSQL = strSQL & "[First Name] = '" & Range("AA" & r).Value & "', "
strSQL = strSQL & "[Last Name] = '" & Range("AB" & r).Value & "', "
strSQL = strSQL & "[Phone 1] = '" & Range("AC" & r).Value & "', "
strSQL = strSQL & "[Phone 2] = '" & Range("AD" & r).Value & "', "
strSQL = strSQL & "[CUSTOMERID1] = '" & Range("AE" & r).Value & "', "
strSQL = strSQL & "[CUSTOMERID2] = '" & Range("AF" & r).Value & "', "
strSQL = strSQL & "[Street Address] = '" & Range("AG" & r).Value & "', "
strSQL = strSQL & "[City] = '" & Range("AH" & r).Value & "', "
strSQL = strSQL & "[STATE] = '" & Range("AI" & r).Value & "', "
strSQL = strSQL & "[ZIP Code] = " & Range("AJ" & r).Value & ", "
strSQL = strSQL & "[TIMESLOTDESC] = '" & Range("AK" & r).Value & "', "
strSQL = strSQL & "[ORDERINGOPERATORID] = '" & Range("AL" & r).Value & "', "
strSQL = strSQL & "[CONTACTNAME] = '" & Range("AM" & r).Value & "', "
strSQL = strSQL & "[CONTACTPHONE1] = " & Range("AN" & r).Value & ", "
strSQL = strSQL & "[CONTACTPHONE2] = '" & Range("AO" & r).Value & "', "
strSQL = strSQL & "[SMS Job Number] = " & Range("AP" & r).Value & ", "
strSQL = strSQL & "[SPA] = " & Range("AQ" & r).Value & " "
strSQL = strSQL & "WHERE [WORKORDERUID] = " & Range("A" & r).Value & ";"
cn.Execute strSQL

The SQL code sits in a loop with an IF so it shouldn't execute every time and I plan on once i get this working to revisit this code and try to optimize it but that is not a priority currently.

I'm using both Excel 2007 and Access 2007 on XP
Any help would be much appreciated.

 

[Trebor76]

Each primary key record must be unique in the Access table.

The data you're trying to upload violates this rule - either because there's duplicates in the column being used for the primary key field or the value in the column already exists in the primary key field within the relevant Access database table.

 

[bl4met]

I realize that the primary key must be unique, I'm not trying to duplicate it, I'm trying to update/modify the existing record or at least the parts of it that I have receive updated information on.

 

[Trebor76]

Hi bl4met,

This is rough, but if you suppress the error message the code will update the record (will it did on a test database for me anyways). So try this at the end of your code:

On Error Resume Next
        cn.Execute strSQL
    On Error GoTo 0

What worries me is that this is no way fixes the "error" - it just stops the message from appearing so I'm not sure why the Access record is actually getting updated. Like I said, it's rough solution.

Another way you may consider is that if you're updating every field you could delete the record and then import the updated record if it errors out.

Good luck,

Robert

 

[Trebor76]

See if this thread helps also

 

[bl4met]

I thought about deleting the record and re-adding it but there are a couple fields that aren't being imported that i would lose though i suppose i could move them to a new table

 

[bl4met]

I believe i just figured out my issue, turns out that I was checking for duplicates and when it found on that needed to be updated, it would update and then hand back to the original function which didn't know it was a dupe because i forgot to code it that it was so the original function would then try to add the record resulting in the error. I found this by added the "On Error Resume Next" to the the dupe function and i still got the error.

Thanks for all your help.

 

[Trebor76]

I'm glad it all worked out

My next suggestion was to check if the record already existed in via using the EOF (End Of Field) function and if it did only update the desired fields (not the primary key) or else create a whole new record with the data.

Regards,

Robert