Update Query not updating table

[papadega3000]

I have created a form that will allow a user to browse for a excel file and then import the excel spreadsheet into a tempTask table and then I want to do a update query on the main source table called Task. Both tables have the same number of columns and same field names but the update query doesn't change/update the records in the main source table Task with the values in TempTask.
Here is my VBA code that I am using.

Private Sub ImportfromExcel_Click()
Dim StrSQL  As String

    CurrentDb.Execute "DELETE * FROM TempTask"
    DoCmd.TransferSpreadsheet acImport, 8, "TempTask", [filepath].Value, True, ""
    
    'StrSQL is an Update String that will compare all of the Current Task in Task Table and
    'compare/update them to the currren values in TempTask
    
    StrSQL = " UPDATE Task INNER JOIN TempTask ON Task.Task = TempTask.Task SET Task.Task = [TempTask].[Task], Task.Description = [TempTask].[Description], Task.Points = [TempTask].[Points], Task.Verifier = [TempTask].[Verifier], Task.Attempted_Planned = [TempTask].[Attempted_Planned], Task.Completed_Planned = [TempTask].[Completed_Planned], Task.Verified_Planned = [TempTask].[Verified_Planned], Task.Attempted_Actual = [TempTask].[Attempted_Actual], Task.Completed_Actual = [TempTask].[Completed_Actual], Task.Verified_Actual = [TempTask].[Verified_Actual], Task.Deleted = [TempTask].[Deleted], Task.Notes = [TempTask].[Notes]"
    CurrentDb.Execute StrSQL
    
End Sub

Any Help would be appreciated thanks.

 

[DCrake]

Simple Software Solutions

Two possible approaches

1. put a breakpoint on your code StrSQL

do a Debug.Print StrSql
Copy the results into the buffer
Create a new query but don't pick and table or query
In the QDE click on the SQL button on the taskbar and paste in the strSQL code.
Next try and run the query in datasheet mode (view results only) if there is an error in your sysntax it will show up here. Debug and retry.

2.
Change the code from CurrentDb.Execute StrSQL

To DoCmd.RunSQL StrSQL


CodeMaster:

 

[papadega3000]

Hello Dcrake,

I tried what you suggested and then update query runs but after I hit yes to modify the rows another window pops up with a error message saying I have a 2 key violations. I am unsure of how to fix it. My guess is that its not happy that I have identical tables (one the source and one the temp) with the same fields and primary keys. But I tried altering the temp table to a autonumber primary key and I still get the same message.

If anyone has any info on dealing with key violations anything will help.

Thanks.

 

[jal]

Hello Dcrake,

I tried what you suggested and then update query runs but after I hit yes to modify the rows another window pops up with a error message saying I have a 2 key violations. I am unsure of how to fix it. My guess is that its not happy that I have identical tables (one the source and one the temp) with the same fields and primary keys. But I tried altering the temp table to a autonumber primary key and I still get the same message.

If anyone has any info on dealing with key violations anything will help.

Thanks.

First, you need to be sure where the primary key violation is occurring - is it in the tempTable (i.e. during the TransferSpreadsheet) or is it in the main table (i.e. during the Update).


Second, the logic of your query is questionable. A primary key is used to identify a particular record as distinct from other records. Therefore an Update should generally be using the key only to find a record, not to update the key. Yet here you join on Task.Task (which makes it seem to be the key) and then you update this key as well?

StrSQL = " UPDATE Task INNER JOIN TempTask ON Task.Task = TempTask.Task SET Task.Task = [TempTask].[Task]......

So this leaves me wondering - in your main table, what is your primary key?

Third, if you set a field to autonumber, let's say a field called ID, you might run into a problem if you try to import a sheet with a prepopulated ID column because in effect you would be trying to force-number an autonumber field. (I don't know if this is what you did, so I'm just warning you).

Having 2 identical tables shouldn't necessarily cause primary key violation on an Update. Violations are more likely to occur on an INSERT because you cannot insert the same primary key twice into a table. Therefore you should be able to get this Update query working.

 

[neileg]

You can't update a field that you are using in a join. There is no logical reason why you would doo that and Access just can;t do it.

This is the nonsense bit
SET Task.Task = [TempTask].[Task]
Task.Task already equals TempTask.Task otherwise it wouldn't exist in your dataset.

 

[papadega3000]

Hello,

Thanks for the replies. I can see both your points in using the update query. I will see what I came figure out. I don't think I need the join in the query. And now that I think of it the violation makes sense because the Task field in source table is the primary key and same for the temptable and since I cannot duplicate a primary key it yells at me.

I will re-work this query and see what I can come up with. I will re-post what I figure out.

Thanks for the help.