Update a table in DB2 from Access
I am trying to develope code to update a table in a DB2 database.
Dim sqlStr As String
not linked-----------v verify SQL
sqlStr = "Update TableInDB2 D Inner Join TableInAccess A On D.AcctNo = A.AcctNo Set D.Budget = A.Budget"
.
.
strDBConnect = "Connection:=ODBC;DSN=PDB2
Set cmd = New ADODB.Command
cmd.CommandText = sqlStr
cmd.ActiveConnection = strDBConnect
Set rst = cmd.Execute
.
.
Please put above code in a shape and list your assumptions.
Thank you.
Dim sqlStr As String
not linked-----------v verify SQL
sqlStr = "Update TableInDB2 D Inner Join TableInAccess A On D.AcctNo = A.AcctNo Set D.Budget = A.Budget"
.
.
strDBConnect = "Connection:=ODBC;DSN=PDB2
Set cmd = New ADODB.Command
cmd.CommandText = sqlStr
cmd.ActiveConnection = strDBConnect
Set rst = cmd.Execute
.
.
Please put above code in a shape and list your assumptions.
Thank you.
ASKER
TableInDB2 is in a main frame computer. Could be this done? Having an action query in DB2 could be helpe (to take data from Access and update a table in DB2)?
Mike
Mike
You could contruct an update statement for each row from the local table that you wanted to update.
And then execute those statements on the server.
If it supports SP's - then you could conceivably write such an SP to receive a string of update values for it to parse and update values there.
That's a bit of work though - and would require intimate knowledge of the tables and fields in question.
For simple results - updating the local Access db from passthrough data (fast enough more than likely).
Update the server tables through linked tables - executing a local Update statement joining the server and local tables (not fastest - easy to implement and would have to test and see about performance).
Beyond that you're into a series of statements being executed in code. (Rather than one statement).
And then execute those statements on the server.
If it supports SP's - then you could conceivably write such an SP to receive a string of update values for it to parse and update values there.
That's a bit of work though - and would require intimate knowledge of the tables and fields in question.
For simple results - updating the local Access db from passthrough data (fast enough more than likely).
Update the server tables through linked tables - executing a local Update statement joining the server and local tables (not fastest - easy to implement and would have to test and see about performance).
Beyond that you're into a series of statements being executed in code. (Rather than one statement).
ASKER
Linked table may be even not possible after all.
re:> You could contruct an update statement for each row from the local table that you wanted to update. And then execute those statements on the server
Taing this direction, please use the following tables to do the above:
TableInDB2
==============
Field1 (PK)
Field2
Field3
TableInAccess
==============
Field1 (PK/FK one to one)
Field2
Field3
Mike
re:> You could contruct an update statement for each row from the local table that you wanted to update. And then execute those statements on the server
Taing this direction, please use the following tables to do the above:
TableInDB2
==============
Field1 (PK)
Field2
Field3
TableInAccess
==============
Field1 (PK/FK one to one)
Field2
Field3
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.
Mike
Mike
Having one table local and updating it from an external source you'll have to use Access functionality for this (rather than ADO).
Linked tables - or ideally a passthrough.
Otherwise you're to open the data in a recordset - and iterate through that line by line updating your local table - which should rarely be the quickest method.