[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Update a table in DB2 from Access

Posted on 2006-10-23
6
Medium Priority
?
258 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 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