SQL Update in Multiple DB's

[simonkue]

I am trying to run a SQL update command via my VB application.
The SQL uses a join to take a field from table B and put it into a field in table A. These tables could be in different databases.
When placing both tables in the same database and omitting the IN from the SQL, it works. When using the IN it doesn't.
Note that I have used the IN as shown below in other Select queries in the application and it works fine!

Here is the code:

'This doesn't work
xsSelect = "UPDATE " & xsInputTable & " IN '" & xgsDtaSrcLocal & "' " & _
"INNER JOIN BO_Data IN '" & xgsDtaSrcMaster & "' ON " & xsInputTable & ".AV_Cardno = BO_Data.BO_Cardno " & _
"SET " & xsInputTable & ".A1_Active = [BO_Data.BO_Active] "

'This works fine but assumes both tables in same db
' xsSelect = "UPDATE " & xsInputTable & " " & _
"INNER JOIN BO_Data ON " & xsInputTable & ".AV_Cardno = BO_Data.BO_Cardno " & _
"SET " & xsInputTable & ".A1_Active = [BO_Data.BO_Active] "

I'm using the connection.execute method to run the SQL.

Does anyone have any ideas please?

 

[BiggerBrother]

I think you need to use the syntax from MS SQL, where you prepend the db name before the table name. eg:
xsSelect = "UPDATE " xgsDtaSrcLocal & "." & xsInputTable & _
"INNER JOIN IN " & xgsDtaSrcMaster & ".BO_Data ON " & xsInputTable & ".AV_Cardno = BO_Data.BO_Cardno " & _
"SET " & xsInputTable & ".A1_Active = [BO_Data.BO_Active] "

BB

 

[simonkue]

Thanks BB. I'll try it!