• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

How can I change the values of a field in Access tables with DLookup

I have an Access database with many tables. I have a table named AOS_tbl. This table has a field named LCHyperion and OSEntity. I want to replace the value on all other tables that have a field called LCHyperion with the OSEntity value.
I need code that will
1) go through each table
2) Find the field LCHyperion
3) Match the value from AOS_tbl field LCHyeprion and replace the value with the value in field OSEntity.

Example - On below example I have a record on my table named Entity_tbl  field LCHyperion with a value equal to "value1". This value matches what is on my AOS_tbl field LCHyperion. The code would change the value on my Entity_tbl field LCHyperion from "value1" to "newOS1".

Have

AOS_tb    Fields   LCHyperion   OSEntity                                Entity_tbl          Field  LCHyperion
                               value1             newOS1                                                                     value1

Want

AOS_tb    Fields   LCHyperion   OSEntity                                Entity_tbl          Field  LCHyperion
                               value1             newOS1                                                                     newOS1

Thank you.
0
Conernesto
Asked:
Conernesto
  • 6
  • 3
  • 3
1 Solution
 
PatHartmanCommented:
Is this a one-time conversion?  Do you really not know what tables need to be changed?
0
 
ConernestoAuthor Commented:
This is a one time conversion. There are a lot of tables in my database with long names.
0
 
PatHartmanCommented:
Is it just a single value to the same replacement value or is there a list of old values that need to become new values?  If it is the latter, just make a cross reference table - OldValue, NewValue.  Then create a query for each table.  Using the QBE,
- add the main table and the cross reference table
- draw the join line, pick the column you want to update
- change the query type to Update,
- In the update to, enter the column name for the NewValue from the xref table
- save
- run

If you are talking about less than about 20 tables, this will be faster and easier to test.  Since conversions typically need to run multiple times during testing and one final time for production, you can make a function that runs all the queries so you don't leave any out accidentally.

If this is too tedious, you can streamline the process of creating the discrete queries if the column names are identical in all the tables.  using the properties assign an alias to the main table name.  Save.  Switch to design view.  Now all you need to do is to copy the SQL String and change JUST the actual table name for each table you want to change.  Or, you can create a table of table names and in code, use the base query and change the table name using the Replace() function for each table in the table of table names.  This is probably the simplest combination of hard-coding and automation.  You identify the tables and the code takes care of updating each of the tables using the same base query.

BTW, I'm assuming that the schema is properly normalized and what you are changing is actually a foreign key rather than a data field that could be derived by simply joining to another table.  If the tables are not normalized and this is in fact duplicate data, I would expend the effort to fix that problem rather than cover it up.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Rey Obrero (Capricorn1)Commented:
try this codes
Sub FindTableAndChange()
Dim db As DAO.Database, td As DAO.TableDef, f As DAO.Field
Dim rs As DAO.Recordset, usql As String
Set db = CurrentDb
For Each td In db.TableDefs
    If Not td.Name Like "msys*" And Not td.Name = "AOS_tb" Then
    For Each f In td.Fields
        If f.Name = "LCHyperion" Then
        
        usql = "update [" & td.Name & "], [AOS_tb]" _
            & " set [" & td.Name & "].[" & f.Name & "] = AOS_tb.OSEntity" _
            & " where [" & td.Name & "].[" & f.Name & "]=AOS_tb.LCHyperion"
        CurrentDb.Execute usql
        End If
    Next
    End If
Next
End Sub

Open in new window

0
 
ConernestoAuthor Commented:
There a list of old values that need to become new values. The values in LCHyperion are primary keys.
0
 
Rey Obrero (Capricorn1)Commented:
@Conernesto

before you try the code I posted, create a BACK UP copy of your db.
0
 
PatHartmanCommented:
Create the query I described that joins to the crossreference and does the update.  Make sure that the main table is aliased.  Then use that query in the code loop that Rey so kindly wrote for you and just change the table name.
0
 
ConernestoAuthor Commented:
I will do a back up and try running the code. I will let you know if OK.
0
 
ConernestoAuthor Commented:
Ray,

The Code worked. I just added the l to AOS_tbl

Thank you for making my day.

Conernesto.
0
 
Rey Obrero (Capricorn1)Commented:
don't forget to close the thread..
0
 
ConernestoAuthor Commented:
Thank you very much.
0
 
ConernestoAuthor Commented:
Done.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now