Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Unanswered: New VB programmer

    Is it possible in Visual Basic to open 2 tables at the same time and update table one from table two based on criteria? I have searched the internet looking for sample code that would show me how to do this and I have had no luck.

    If it is possible to do this, please point me to a website that has sample code that will show me how this is done.

    Thanks,

    SB

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What do you mean by Table? If you're referring to a table from an SQL database, then you would use an UPDATE sql statement. How you do that would depend on the database, the connection protocol you choose to use and overall architecture of your application.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    They are tables in an Oracle database.

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    this can help u

    You can use the oracle provider to do the connection.After that use two recordset to open two tables.Oterwise you can create a single recordset by opening two tables by a single sql statement - "select * from table1,table2".

    Using the relationship criteria you can easily update the two tables. I think there should be no problem to update two tables using the relationship critera.
    I hope this will solve uor problem.
    If u r not satisfied then plz state more details of the problem.
    Thanks!!
    Joydeep

  5. #5
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    I was trying to find examples of code that will do what I want to do so I can get the correct syntax. Here's what I have so far, but I don't know if the syntax is correct:

    Dim sqlstmt As String

    'Adds the To Dept, To Account charge and Event Type to the temp table from the Work Request table

    sqlstmt = "UPDATE Temp SET temp.to_dp_id = wr.dp_id, temp.to_sa_id = wr.jn_acct_id, temp.prob_type = wr.prob_type, temp.tr_id = wr.tr_id" +_
    "FROM wr WHERE temp.wr_id = wr.wr_id"
    ProjDb.ExecuteSQL sqlstmt

    ' Adds the Cost Center to the temp table from the dept table
    sqlstmt = "UPDATE Temp SET temp.to_cc_id = dp.cc_id FROM dp" +_
    "WHERE temp.to_dp_id = dp.dp_id"

    Does the UDPDATE statement look like the right way to do this?

    Thanks,

    SBR

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Looks fairly close. Have you defined ProjDb yet? What you posted looks a bit like DAO code. I'd be surprised if you're intentionally using DAO to hook into Oracle from straight VB. ADO seems like a more likely choice...

    Also, "&" is preferrable to "+" when concatenating strings. Use "+" when ADDING values as opposed to concatenating.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Oct 2005
    Posts
    178
    Quote Originally Posted by sbr7770
    Is it possible in Visual Basic to open 2 tables at the same time and update table one from table two based on criteria? I have searched the internet looking for sample code that would show me how to do this and I have had no luck.

    If it is possible to do this, please point me to a website that has sample code that will show me how this is done.

    Thanks,

    SB
    Ceate recordset for each SQL of your tables
    RS1 and RS2 for example

    then you can do this

    RS1.Fields("fld1") = RS2.Fields("fld1")

  8. #8
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    OKay great, thanks!

    SBR
    Last edited by sbr7770; 01-13-06 at 10:07.

  9. #9
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    Reply to Teddy:

    I'm looking at some existing code I have from Summit Basic that is not exactly like Visual Basic. I want to write my code in VB. Right now, I just want to get the correct syntax for the Update command that will update records in the Temp table from other tables. I will use the "&" instead of the "+" in the VB code...thanks for pointing that out.

    SBR

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •