CODEWITHSUNDEEP
ms-accessvbainner-join
James Laguerre
James Laguerre

Reputation: 29

update query with inner join access vba code

I am trying to update a table from another one via a inner join query access vba. The field's name to be updated will be taken in a form.

I wrote this query, but it gives me error. 

Dim dbs As Database
Dim qdf As QueryDef

Set dbs = OpenDatabase("Dba_Savings.mdb")
dbs.Execute " UPDATE TblClient INNER JOIN TblImport ON TblClient.Account_Id = TblImport.Account_Id" _
& " SET TblClient." & Me.txtNewFieldName & " = TblImport." & Me.txtNewFieldName2 & ";"

dbs.Close

Can anyone help me please.

 Dim S As String
S = "UPDATE TblClient INNER JOIN TblImport ON TblClient.Account_Id = TblImport.Account_Id" _
    & " SET TblClient." & Me.txtNewFieldName & " = TblImport." & Me.txtNewFieldName2 & ";"
Debug.Print S
dbs.Execute S

UPDATE TblClient INNER JOIN TblImport ON TblClient.Account_Id = TblImport.Account_Id SET TblClient.S_B_Mar-14 = TblImport.S_B_Mar-14;

The SQL work perfectly and update the table, No error.

Upvotes: 1

Views: 3504

Answers (1)

Andre
Andre

Reputation: 27634

You are missing a space between these two:

ON TblClient.Account_Id = TblImport.Account_Id" _ 
& "SET TblClient.

When building SQL in VBA, always assign the SQL to a string variable, then Debug.Print this variable. In this case, the error would be obvious.

Also this part is unnecessary, since it's already in the INNER JOIN:

WHERE TblClient.Account_Id = TblImport.Account_Id

Edit

Instead of 

dbs.Execute " UPDATE TblClient INNER JOIN TblImport ON TblClient.Account_Id = TblImport.Account_Id" _ 
& " SET TblClient." & Me.txtNewFieldName & " = TblImport." & Me.txtNewFieldName2 & ";"

you do:

Dim S As String
S = "UPDATE TblClient INNER JOIN TblImport ON TblClient.Account_Id = TblImport.Account_Id" _ 
    & " SET TblClient." & Me.txtNewFieldName & " = TblImport." & Me.txtNewFieldName2 & ";"
Debug.Print S
dbs.Execute S

Ctrl+G opens the Immediate Window, this will show the output of Debug.Print. Please add this to your question.

Then, if it still gives an error, you can also create a new query, switch to SQL view, copy & paste the output, and try to run the query. You will probably get a better error message. Or at least highlight the problematic part.

Upvotes: 1

Related Questions