Solved

Update a table in DB2 from Access

Posted on 2006-10-23
6
231 Views
Last Modified: 2008-03-17
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;UID=uuuuu;pwd=ppppp;DBALIAS=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.
0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17793527
In a shape?  (A data shape provider?  Don't see how that will help.)
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.
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 17793545
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
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17793562
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).
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 17793591
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
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 500 total points
ID: 17793603
You'd need to open a recordset on the local table - create the SQL statements iteratively - executing them on the server.
(Funnily enough I don't have a DB2 mainframe in my house - total air code, keeping it minimal and assuming DB2 has a pretty much ANSI standard syntax :-S)

Dim strSQL as String
Dim cnn as New ADO.Connection

strDBConnect = "Connection:=ODBC;DSN=PDB2;UID=uuuuu;pwd=ppppp;DBALIAS=PDB2"
cnn.Open strDBConnect

With CurrentProject.Connection.Execute("SELECT * FROM TableInAccess")
    Do Until .EOF
        strSQL = "UPDATE TableInDB2 SET Field2 = " & .Fields("Field2") & ", Field3 = " & .Fields("Field3") & " WHERE Field1 = " & .Fields("Field1")
        cnn.Execute strSQL
        .MoveNext
    Loop
    .Close
End With
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 17793625
Thank you.

Mike
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question