Solved

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

Posted on 2016-11-18
12
51 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Change AD password via MS Access DB 2 20
Run Time Error 3071 26 41
Format Meeting Request through VBA 5 22
field number type in access tabledefs fields 5 20
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…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

863 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

26 Experts available now in Live!

Get 1:1 Help Now