Update table in as400 using table in excel worksheet

[yudia]

I want to update table in as400 using table in excel worksheet like this :
Table1 [as400]

REFNO      I        QTY
1          I        10
2          I        12 
3          I        20   

Table2 [excel worksheet]

REFNO      I       QTY
1          I       13
2          I       15 
3          I       22

Qty in Table1 updated from Table2 using VBA so the ouput will be like this :

Table1 [as400]
REFNO      I       QTY
1          I      13
2          I       15 
3          I       22

I have used inner join code like this :

Dim s1 As Worksheet
Dim BRDa As ADODB.Connection
Dim RS As ADODB.Recordset
Dim b As Integer
 
Set RS = New ADODB.Recordset
Set BRDa = New ADODB.Connection
BRDa.ConnectionString = "provider=ABC; Data source=XYZ; user id=OK1; password=OK2"
BRDa.Open
 
On Error Resume Next 
Dim QRY1, QRY2 As String
QRY1 = "SELECT * FROM TABLE1 INNER JOIN S1.TABLE2 ON TABLE1.REFNO=TABLE2.REFNO"
RS.Open QRY1, BRDa, adOpenDynamic, adLockOptimistic
 
b = 0
 
Do While Not RS.EOF
QRY2 = "UPDATE TABLE1 SET TABLE1.QTY = TABLE2.QTY WHERE TABLE1.REFNO=TABLE2.REFNO"
BRDa.Execute QRY2, dbFailOnError
 
b = b + 1
RS.MoveNext
Loop
TextBox1.Text = b
RS.Close
 
BRDa.Close
End If
End Sub

But it doesn't work. Could someone help me pleasee... T__T!! Thank you..

[NeoPa]

I'm sure someone will help you, but they deserve a little more than "My code doesn't work".

Does it compile even?
Where does it fail?
What's the error message and number?

Without these basic items of a question I suspect many will look elsewhere to find something to help with.

[yudia]

Yes, I have compile my codes but it was failed and the error message : Table2 couldn't not be found in as400 database. In my opinion, since I have used inner join codes, it couldn't be placed at the same sub macro with as400 data base. Other words, combine as400 with excel data couldn't be joined by inner join codes. I need helps, whether anyone have better solutions :)

[NeoPa]

From that error message I'd say that the JOIN type isn't the issue. It doesn't even see that [Table2] exists. Also, it wants to find it from the AS400 rather than the Excel source.

I think you probably need to set up linked tables in your database and run the SQL off that. Running it against the ADODB connection will cause that SQL to be run against that connection. No good if it wants data from Excel.

[yudia]

In MSSQL programming, there is a code : INNER JOIN OPENROWSET to join table in excel worksheet with MSSQL database.

My question is What codes in VBA Programming that have the same function as INNER JOIN OPENROWSET in MSSQL but the function is joining table in excel worksheet with AS400 database. Thanks.

[NeoPa]

There is only one question per thread. I've already answered it. At the very least you'll have to work with a database or connection that is able to see and reference all the data you need in your query. If the AS400 can do that, then fine. I doubt that's the case but I'm no expert on your setup. The only sensible solution I can see is as in the previous response, which is to link the tables in an Access database.

If your interest is finding an equivalent of some T-SQL then you'll need to post that separately. If so, please make sure the question makes sense. It needs to include which type of SQL from and to. Asking about converting from T-SQL to VBA makes no sense as they're not comparable. One's a data manipulation language and the other's a programming language. They are not remotely compatible.