[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Update a table in DB2 from Access

Posted on 2006-10-23
6
Medium Priority
?
262 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

591 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