Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    2

    Unanswered: Bulk update record

    Hi
    1] i am doing one project using vb6.0 with ms-access 2003 database. i have
    one table name [segment transaction] , its have more than 1000 records
    i want update one or two column's bulk records in this table. single record update its ok no problem but multiple records update is complicated to me. please help me
    i will expect your good solution.

    For example:

    Segment name segid quotation sorno status
    ------------- ----- --------- ----- ------
    AAAA 1 2349879 *** ***
    BBBB 19 3564788 *** ***
    CCCC 34 3243424 *** ***
    ............
    .......
    i want update more than 1000 records in [sorno(***) and status(***) ] columns based on "QUOTATION" from excel sheet.

    2] my above mentioned project running network(multiuser environment), some
    image file stored in server. i print this image through vb6.0 coding. but this image file is not save in my local machine, is it possible?
    please help me


    Thank you

    sathik
    srbatcha@yahoo.co.in
    Last edited by srbatcha; 01-18-10 at 10:05. Reason: please check my questions one more time, now is corrected easily understand for you.

  2. #2
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Try this:
    update [segment transaction] set Field1 = some value or addition of fields ie. (Field2 + Field3)

    Is that what you are looking for?

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    One approach would be to
    1. open the excel spreadsheet
    2. read the data in the spreadsheet
    3. construct an update statement based upon data from [2]
    4. Execute the update statement
    5. repeat steps [3] and [4] until there's are no more data
    6. close the app.

    For the Excel manipulation, you could use automation, but, this approach is rather slow, and it requires that the client machine have excel installed. Consider using ADO to connect to the excel file, as well as to the Access data file...

    When updating the target data file, you have a couple of approaches with ADO. You could, or course, simply issue SQL UPDATE statements against the target table using the connection object's Execute method. You would open the database connection, then build a SQL statement which looks similar to
    Code:
    UPDATE YourTableName
    Set FieldName2 = 'Value2', FieldName3 = 'Value3'
    Where FieldName2 = '12345'
    With ADO, another option could be to Open an ADO recordset in the Bulk edit mode, then issue a series of Filter statements against the recordset (one for each line of data in the Excel file.) Each time you update the filter value, the recordset now contains 'just' the records which match the filter condition, so you could iterate through the recordset, directly updating the fields. Then, set the filter property to adFilterNone and repeat the last group of statements until no more source data. Then, perform the recordset batch update.
    Last edited by loquin; 01-18-10 at 16:00.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Jan 2010
    Posts
    2
    Quote Originally Posted by loquin View Post
    One approach would be to
    1. open the excel spreadsheet
    2. read the data in the spreadsheet
    3. construct an update statement based upon data from [2]
    4. Execute the update statement
    5. repeat steps [3] and [4] until there's are no more data
    6. close the app.

    For the Excel manipulation, you could use automation, but, this approach is rather slow, and it requires that the client machine have excel installed. Consider using ADO to connect to the excel file, as well as to the Access data file...

    When updating the target data file, you have a couple of approaches with ADO. You could, or course, simply issue SQL UPDATE statements against the target table using the connection object's Execute method. You would open the database connection, then build a SQL statement which looks similar to
    Code:
    UPDATE YourTableName
    Set FieldName2 = 'Value2', FieldName3 = 'Value3'
    Where FieldName2 = '12345'
    With ADO, another option could be to Open an ADO recordset in the Bulk edit mode, then issue a series of Filter statements against the recordset (one for each line of data in the Excel file.) Each time you update the filter value, the recordset now contains 'just' the records which match the filter condition, so you could iterate through the recordset, directly updating the fields. Then, set the filter property to adFilterNone and repeat the last group of statements until no more source data. Then, perform the recordset batch update.
    Thanks for your reply

    but
    "
    UPDATE YourTableName
    Set FieldName2 = 'Value2', FieldName3 = 'Value3'
    Where FieldName2 = '12345'
    "
    this query use for update one record at a time
    i want more than 1000 related records in excel will update to the table

    i will expect your reply


    my table:

    segment_name quotation_no sor_no sor_date
    =========== ========== ======= ========
    AAAA 2390887 ? ?
    BBBB 2797779 ? ?
    ........ up to >1000 records

    i want update [ sor no and sor date ] columns based on quotation_no.

    note: quotation_no is primary key

    Regards
    sathik

Tags for this Thread

Posting Permissions

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