update query with inner join access vba code

Question

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.

Run time error '3075' Sytaxe error (missing operator) in query expression — James Laguerre
– James Laguerre, Commented Nov 24, 2015 at 17:30

Andre · Accepted Answer · 2015-11-24 20:35:38Z

1

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.

edited Nov 24, 2015 at 20:35

answered Nov 24, 2015 at 17:30

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

James Laguerre Over a year ago

Thanks, but i still have the same error, maybe the reference to the form is incorrect : " & Me.txtNewFieldName & "

Andre Over a year ago

Please edit your question, and add the output of the Debug.Print. @JamesLaguerre

Andre Over a year ago

@JamesLaguerre Please. Debug.Print. Use it. I promise it will help.

Andre Over a year ago

@JamesLaguerre: see Edit.

James Laguerre Over a year ago

The VBA does not work, it gives error, but the SQL work fine after Debug.Print.

|

Collectives™ on Stack Overflow

update query with inner join access vba code

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related