Solved

Update a table in DB2 from Access

Posted on 2006-10-23
6
228 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 33

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 33

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 33

Author Comment

by:Mike Eghtebas
ID: 17793625
Thank you.

Mike
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

777 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