Solved

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

Posted on 2016-11-18
12
23 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
  • 6
  • 3
  • 3
12 Comments
 
LVL 34

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 34

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
 
LVL 119

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
ID: 41893187
@Conernesto

before you try the code I posted, create a BACK UP copy of your db.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 34

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 119

Expert Comment

by:Rey Obrero
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now