Solved

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

Posted on 2016-11-18
12
93 Views
Last Modified: 2016-11-18
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
Comment
Question by:Conernesto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
12 Comments
 
LVL 37

Expert Comment

by:PatHartman
ID: 41893106
Is this a one-time conversion?  Do you really not know what tables need to be changed?
0
 

Author Comment

by:Conernesto
ID: 41893121
This is a one time conversion. There are a lot of tables in my database with long names.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 41893164
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
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 41893171
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
 

Author Comment

by:Conernesto
ID: 41893180
There a list of old values that need to become new values. The values in LCHyperion are primary keys.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41893187
@Conernesto

before you try the code I posted, create a BACK UP copy of your db.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 41893192
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
 

Author Comment

by:Conernesto
ID: 41893212
I will do a back up and try running the code. I will let you know if OK.
0
 

Author Comment

by:Conernesto
ID: 41893294
Ray,

The Code worked. I just added the l to AOS_tbl

Thank you for making my day.

Conernesto.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41893572
don't forget to close the thread..
0
 

Author Closing Comment

by:Conernesto
ID: 41893580
Thank you very much.
0
 

Author Comment

by:Conernesto
ID: 41893581
Done.
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question