Improve company productivity with a Business Account.Sign Up

x
?
Solved

Update a table in DB2 from Access

Posted on 2006-10-23
6
Medium Priority
?
263 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

606 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