Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Posts
    3

    Unanswered: Update table using vba

    Hello Friends !!

    I am trying to update a field in the table using vba recordset but have no clue as to go forward

    this is what i am trying to do

    Have two tables in the database

    two tables have common field "ID"

    a table consist of 58 records and B table consist of 35k records

    B table as 5 fields
    Field1,caldate,field3,Field4, ID

    I have to update a only field1 in the " b " table when a.ID=b.ID using criteria as max calcuated date(caldate)

    I want to insert new records in the table B when criteria matches and it should only update one field (Field1) and should keep all other fields the same

    Thanks in advance

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by SAM_DVLP View Post
    Hello Friends !!

    I am trying to update a field in the table using vba recordset I have to update a only field1 in the " b " table when a.ID=b.ID using criteria as max calcuated date(caldate)

    I want to insert new records in the table B when criteria matches and it should only update one field (Field1) and should keep all other fields the same

    Thanks in advance
    Is it insert new records or update one field (which implies altering existing records) ? And what do you have so far?

    Also, why do you have to use a VBA Recordset (DAO or ADO?) instead of, say, SQL?
    Have a nice day!

  3. #3
    Join Date
    Oct 2010
    Posts
    3

    add new records using vba

    Thanks for the reply

    I have very little knowledge about vba

    and i have read earlier is to make use of recordset ADO
    if we can use SQL then that should be okay as well

    I am able to get into the table but i can insert new records into the table using the criteria mentioned and its freezing the system if i ran the code i have

    Any suggestions would be of great help
    Thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You do not answer my question. If it's to update the contents of B.Field1 to some value when A.ID=B.ID, you can use:
    Code:
    Dim strSQL As String
    strSQL = "UPDATE A INNER JOIN B ON A.ID = B.ID SET B.Field1 = <Some value>"
    CurrentDb.Execute strSQL, DbFailOnError
    In your initial question however, you do not clearly explain to what value you want to update B.Field1. You also talk about a criteria (in supplement to A.ID=B.ID?), but you do not explain:
    ...criteria as max calcuated date(caldate)
    And, once more, I'm not sure whether you want to update the existing rows of table B or to insert new rows into that table:
    I want to insert new records in the table B when criteria matches and it should only update...
    Have a nice day!

  5. #5
    Join Date
    Oct 2010
    Posts
    3

    insert new records into the table

    I am sorry if i wasn't clear

    I have to insert new records into the table B

    The following things i am doing

    if A.ID=B.ID then insert new records into the table B.

    You have 5 fields in table B
    you have 3 fields in table A

    The following fields in table B are
    Field1,FIELD2(calculated DATE),Field3, Field4,ID
    The following fields in table A are
    Field1,Field2, ID

    Table B is the history table and will have more then 1 record for the same ID

    so using the latest date, only for that record we need to check the following coditions
    B.ID=A.ID where B (latest date)
    we have to insert value in field4 of Table B .and retaining the values of Field1,Field3

    so the result should look like a new record in table B as
    Field1,Field2(latest date), Field3,Field4(changed value),Field4,ID

    Thanks

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Maybe I'm a little slow but it's still not clear to me.
    1.
    if A.ID=B.ID then insert new records into the table B.
    On which criteria? As is you would insert into table B as many matching rows as there already is (provided that the condition is fullfilled) or no row at all (if not row in B already has a matching row in A). There must be a criteria to determine which rows of A determine the (new) rows to be inserted into B. Otherwise the number of rows in B will grow exponentially every time you perform the action. Let's suppose that the criteria A.ID=B.ID is true for 20 rows (i.e. there are 20 rows with the same ID in both tables). With such a query:
    Code:
    INSERT INTO B ( ID )
    SELECT A.ID
    FROM A INNER JOIN B ON A.ID = B.ID;
    You'll add 20 rows to Table B the first time you run it, 40 rows the second time, 80 rows the third time, etc.

    A RIGHT JOIN would yield the same result:
    Code:
    INSERT INTO B ( ID )
    SELECT A.ID
    FROM A RIGHT JOIN B ON A.ID = B.ID;
    20, 40, 80, etc.

    While using a LEFT JOIN would even be worse:
    Code:
    INSERT INTO B ( ID )
    SELECT A.ID
    FROM A LEFT JOIN B ON A.ID = B.ID;
    Now you'll add 12241 row the first time, 24522 the second time, etc.

    2. As far as I can understand, when you wrote:
    Code:
    ...so using the latest date, only for that record we need to check the following coditions
    B.ID=A.ID where B (latest date) 
    we have to insert value in field4 of Table B .and retaining the values of Field1,Field3
    
    so the result should look like a new record in table B as 
    Field1,Field2(latest date), Field3,Field4(changed value),Field4,ID
    The criteria determining which rows in table B to duplicate would be based on the date in column FIELD2 which appear to be a calculated date (with no correspondance in Table A), so we would have something like:
    Code:
    SELECT Max(B.FIELD2) AS MaxOfFIELD2, B.ID
    FROM B
    GROUP BY B.ID;
    Combining everything together, we come up with:
    Code:
    INSERT INTO B ( Field1, FIELD2, FIELD3, FIELD4, ID )
    SELECT B.Field1, Date() AS Today, B.FIELD3, Max(B.FIELD2) AS MaxOfFIELD2, B.ID
    FROM B INNER JOIN A ON B.ID = A.ID
    GROUP BY B.Field1, Date(), B.FIELD3, B.ID;
    Here, I presume that the new date (in FIELD2) would be today's date.

    Now the query appends 20 rows to Table B every time it is run, provided there are 20 rows in Table A with a matching ID.

    Is it what you're looking for?
    Have a nice day!

Posting Permissions

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