Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24

    Unanswered: Loop through tables to find and replace

    I have a query set up to :
    UPDATE tSoftware
    SET tSoftware.LocationID = (ID4Replace)
    WHERE tSoftware.LocationID = (ID2Replace)

    I need to perform this on as many as 32 tables in the database. I figure that it's probably best to verify that the ID4Replace is in the table prior to running the query, but I'm not sure how to:
    1. Loop through the table
    2. Check for ID4Replace in the LocationID field of each table
    3. If it does NOT then GoTo the next table Else replace it with ID2Replace in the current table using an update query, then continue until all tables have been checked and updated.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    So let me get this straight... you have 32 tables that with an identicle structure in the same database?

    Or are you saying you need to "cascade update"?
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24

    Hmmm

    Cascading update? Possibly. None of the tables are the same, but they all have a LocationID field as it is the foreign key. So, I'm trying to loop through all of the tables if a record(s) exist with the incorrect LocationID, then I would liketo run the query to update then to the correct LocationID.

    So I need a little assistance with the Loop and the determination of whether the records exists and then as it's looping, I need to past the current table name to the query:
    If table!Locations!LoactionID = (ID2Replace) Then
    UPDATE tDynamicTableName
    SET tDynamicTableName.LocationID = (ID4Replace)
    WHERE tDynamicTableName.LocationID = (ID2Replace)
    Else
    ...

  4. #4
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24

    Unhappy This is the mess I'm trying to get to work

    Help Please. I need to figure out how to pass the FinalNo to the query (as it increments in the code). Then I need to figure out how to pass the one field in the query results to the TblNm Variable in my code for resue. I'm sure I need more help, anything would be helpful for now.

    If IsNull(ID2Replace) Then
    MsgBox "You must select the LocationID to be replaced", vbExclamation, "Missing Entry"
    Else
    If IsNull(ID4Replace) Then
    MsgBox "You must select the correct LocationID", vbExclamation, "Missing Entry"
    Else

    Dim dbITArchitectureData As DAO.Database
    Dim rst As DAO.Recordset
    Dim strCritera As String, strCritera2 As String
    Dim rst1 As DAO.Recordset
    Set dbITArchitectureData = CurrentDb
    Dim TblNm As String
    Dim FinalNo As Integer
    Dim SQL As String
    FinalNo = i

    Do Until FinalNo = 34
    DoCmd.OpenQuery "qSelectTableName"
    SQL = "SELECT tDataTableNames.TableName" & _
    "FROM tDataTableNames" & _
    "WHERE tDataTableNames.TableNumber = FinalNo"

    TblNm = TableName

    Set rst = dbITArchitectureData.OpenRecordset((TblNm), dbOpenDynaset)
    Do Until rst.EOF
    strCritera = (ID2Replace)
    rst1.FindFirst rst1!LocationID = strCritera
    Do Until rst1.NoMatch
    If rst1!LocationID = strCritera Then
    SQL = "UPDATE TblNm " & _
    "SET TblNm.LocationID = (ID4Replace)" & _
    "WHERE TblNm.LocationID = (ID2Replace)"
    DoCmd.RunSQL SQL
    Else
    End If
    rst1.FindNext rst1!LocationID = strCritera
    Loop
    rst.MoveNext
    Loop
    rst.Close
    rst1.Close
    FinalNo = FinalNo + 1
    Loop
    End If
    End If
    Last edited by Adrianna; 08-26-04 at 11:58.

  5. #5
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24

    PLease

    Will someone please look at this and make a suggestion. I have been trying so many other ways of doing this and I keep getting held up where I need to the query to pass the one value that it gets back to the code so that I can have the dynamic table name assigned.

    If nothing else, please help me figure out how to get the TableName from the qSelectTableNames back into my code. I can see it on my form, but I it's not selected. It just displays in the list box based on the initial run of the query.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    sorry, been out for a while.

    Check out the relationships between your tables. You should be able to enable "cascade" changes which would take care of this issue for you.
    oh yeah... documentation... I have heard of that.

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

  7. #7
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24

    Cascading would be nice

    Teddy,
    Cascading would be nice, but the ID correction is being made to the primary key where I have to change two or more incorrect IDs into one correct one
    Maybe it would be possible to create the relationships and then change both the correct and incorrect into a new ID number? And then I guess we are hoping that the waterfall affect will just propagate the new ID down through all of the child tables? I thought about that once already...I can't remember what the problem with it was I guess I could try it again though.
    Until then...if you have any tips on the code, please share! Thanks

  8. #8
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24

    Question Cascading won't work

    Okay, thought it through alittle more and you were right that cascading could affectively change all of the foreign keys in the table if I were to change the primary key, BUT I'm trying to merge data connected to two primary keys and then delete the one key. So, there is avtually no way to avoid looping through the data
    So my biggest challenge is still trying to figure out how to loop through the tables. I have a table containing TableName names that I'm trying to pass as a variable, but that's not working. Maybe there is an easier way.

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    what difficulties are you having with looping through myTableName tablenames in a table tblTable?

    should be simple:

    dim dabT as dao.database
    dim recT as dao.recordset
    set dabT = currentdb
    set recT = dabT.openrecordset("SELECT myTableName FROM tblTable;")
    with recT
    do while not .EOF
    '
    '
    '
    'and here sits your update
    strSQL = "UPDATE blah blah blah;"
    'concatenating in "blah blah " & !myTableName & " blah blah;"
    'wherever you need to refer to the (current) table
    '
    '
    '

    loop
    endwith
    set recT = nothing
    set dabT = nothing


    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24

    Figured it out.

    Thanks Izy.
    The issue was that I didn't know MyTableName as it is dynamic. So, I'm selecting a table name from a table of table and that table name itself changes in the outter look and then inside the loop I'm searching for records that match the incorrect foreign key and changing those to the correct foreign key. Fun hu? Well, my former Sr. Programmer took a moment to help me out and we avoided the query all together by performing an .edit on the current recordset! It was a great idea. I sure miss working with him

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    For reference, you could have used the AllTables collection to take care of that query.
    oh yeah... documentation... I have heard of that.

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

Posting Permissions

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