Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009

    Unanswered: Cycle Through Table1 to copy into Table2

    Using Access 2003 (11...)
    I have 2 tables... call them "main" and "temp". "main" has all the records, and "temp" is updated daily with new records, but occasionally will have old records, and also old records that have been updated.

    Both tables have the exact same structure, with a primary key (realID).

    So the goal is to check realID in the temp table. If the realID isn't in the main table, then add that record to main. If it already exists, then check col2 and col3 to make sure the records match. If those two columns match in both tables, then replace the record in main with the record from temp (same thing as updating the record in main). If the columns don't match, copy the record into an "errors" table, or something of the sort.

    I've used SQL before but not sure how it integrates with Access or if there's an easier way to just loop through the tables.. ?


  2. #2
    Join Date
    Oct 2009
    ouch..."how sql integrates" and "loop through tables" - such phrases makes my db education of course in best db practices one shouldn't be doing what you ask to do....... be that as it may...

    you need to implement a Duplicates query to find the records that meet your criteria. Duplicates query is an embedded feature in the query design area. Check it out - and read up on this.

    then you need AppendQueries...again embedded feature...get to know it.

    and finally you may need a little bit of vb code to toggle between these queries and fire the correct one depending on the situation.

    I don't think a forum like this is intended to teach Access to this level - - but it is all do-able and hopefully as you inspect these features you will be able to implement what you need.....

  3. #3
    Join Date
    Dec 2004
    Madison, WI
    Yes. These are fun types of vba code to write for. What you'll need to do is essentially open up both recordsets in code (ie. identify one of them as rx and the other as ry.) You may also need to open up another recordset depending on how you set it up (there are several different approaches to how this is done.)

    Setting up the looping order and opening the recordsets correctly will be the tricky part (I've had to often do double-loops in these kind of situations where you need to add unmatched records to table A or B and vice versa.) Once the loops are setup correctly, keep in mind that you can also open up another recordset against the 2nd table (I usually create separate functions for these) which does a quick test to see if the matching record exists and if not, it then adds it. You may want to simply do your test/update in the separate function apart from the main loops. It's typically nice to keep these type of routines separate from your larger looping code but doesn't always work to your advantage.

    You also have to be VERY careful on knowing what recordsets are open and which ones need to be closed and when!! I can't stress this enough. It would be very nasty if you left recordsets open since you're probably going to be opening a few recordsets in the routine and must keep them open only as long as you need them and then close them. I would avoid opening multiple recordsets simultaneously against the same table as this can cause problems but I have done this successfully. Typically you have problems in code when doing this.

    Then you simply incorporate the logic into your looping code. You can utilize rx!SomeField = ry!SomeField and other code like that to compare the 2 values if you setup your looping code to cycle through both tables.

    An example of quickly opening the 2nd table to see if it has a matching ID record is a function such as this (ADO example):

    Function UpdateTableB(RecID as variant, vSomeValue1, vSomeValue2, vSomeValue3)
    if isnull(RecID) then exit function
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from myTableA where RecordID = " & RecID & "" strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    if rs.eof and rs.bof then
    end if
    rs!SomeField = vSomeValue1
    rs!SomeField2 = vSomeValue2
    rs!SomeField3 = vSomeValue3
    set rs = nothing
    end function

    Note though in the above, I'm passing the autonumber identifier and the values to update. If you need to do this with both recordsets open (ie. rs!SomeField = ry!SomeField), you'll need to put this kind of code in your looping routine. It depends on how you want to do it and how many variables you need to pass.
    Last edited by pkstormy; 11-17-09 at 22:41.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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