Solved

Record Update in Access

Posted on 2010-08-25
42
409 Views
Last Modified: 2013-11-28
I need some kind of tool in access that will read one table and update another.

lets say I have a one table called LIST001 with the following fields:

ID
Date
Cost
Phase
Descriptrion
Color

The second table LIST002 has the following:

ID
Description
Phase

I need to read table LIST001 record by record and check for a coresponding ID in LIST002 and update the common fields in LIST001 with the Values from LIST002.
0
Comment
Question by:Matt Pinkston
  • 22
  • 19
42 Comments
 
LVL 39

Expert Comment

by:als315
Comment Utility
Can you make update query? Join ID fields in both tables, select fields for update from LIST002 and set update to fields from LIST 001
0
 

Author Comment

by:Matt Pinkston
Comment Utility
Not sure how to do that
0
 
LVL 4

Expert Comment

by:javaftper
Comment Utility
Unless I have misunderstood you are describing one of the fundamental aims of a relational database such as Microsoft Access. i.e.- eradicate data replication.
you may wish to read more database normalisation theory here - http://en.wikipedia.org/wiki/Database_normalization.
in Microsoft Access it would be better to create LIST002 as a query deriving the fields you require from LIST002.
post back if I have misunderstood.
0
 

Author Comment

by:Matt Pinkston
Comment Utility
I have access 2010
0
 

Author Comment

by:Matt Pinkston
Comment Utility
These tables are in access but they are created daily from sharepoint and excel feeds.
 
I just need a simple macro or something that can do this.
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
Check example
db5.accdb
0
 

Author Comment

by:Matt Pinkston
Comment Utility
looks cool but it gets an error, and I can't see what it is doing
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
What error your get?
0
 

Author Comment

by:Matt Pinkston
Comment Utility
The expression On Click you entered as the event property setting produced the following error: a problem occurred while Microsoft Office Access was communicating with the OLE server or ActiveX Control.
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
Please, check this example. If it will not work again, import all (tables, form and query) in clear DB, open Form1 and press button
Database4.zip
0
 

Author Comment

by:Matt Pinkston
Comment Utility
nope not working, I just need a simple way to do this.
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
use this string:
DoCmd.RunSQL ("UPDATE LIST001 INNER JOIN LIST002 ON LIST001.ID = LIST002.ID SET LIST001.Phase = [LIST002]![Phase], LIST001.Description = [LIST002]![Description];")

Query is in picture
query.png
0
 

Author Comment

by:Matt Pinkston
Comment Utility
so do i do with command or screens, not a huge access guy
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
Check again.
db5.zip
0
 

Author Comment

by:Matt Pinkston
Comment Utility
download and tried to run, no error but it does not update.

My access db name is Opportinity_Feeder

Table1 - Opportunities
Table2 - Opportunities_SolutionTrack

Opportunities
- Opportunity Identifier
- Opportunity Name
- Opportunity Description

Opportunities_SolutionTrack
- Opportunity ID
- Opportunity Name
- Opportunity Description
- field 4
- field 5
- field 6
- firld 7

I need a macro or something that will update the common variables in Opportunities_SolutionTrack with like fields from Opportunities when Opportunities.Opportunity IDentifier = Opportunities_SOlutionTrack.Opportunity ID
0
 

Author Comment

by:Matt Pinkston
Comment Utility
with what I posted above would this work?

DoCmd.RunSQL ("UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.Opportunity Id = Opportunities.Opportunity Identifier SET Opportunities_SolutionTrack.Sales Stage = [Opportunities]![Current Sales Stage Code]")

And how do I run it?
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
Your need form (if you want to automate process.
Import Form1 from my example to your DB. Press Button.
db5.zip
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
Sorry, phrase not finished:
(if you want to automate process - you can use macro)
0
 

Author Comment

by:Matt Pinkston
Comment Utility
My question is will the code below do what I am asking for:

DoCmd.RunSQL ("UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.Opportunity Id = Opportunities.Opportunity Identifier SET Opportunities_SolutionTrack.Sales Stage = [Opportunities]![Current Sales Stage Code]")

If so how do I execute it?
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
You can execute code from any event on form (button pressed, for example) or from macros. This event is made for button on a form in my example
0
 

Author Comment

by:Matt Pinkston
Comment Utility
So in my database I can create a form like this with a button and right click the button under properties ONCLICK modify the event procedure to be

DoCmd.RunSQL ("UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.Opportunity Id = Opportunities.Opportunity Identifier SET Opportunities_SolutionTrack.Sales Stage = [Opportunities]![Current Sales Stage Code]")
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 39

Expert Comment

by:als315
Comment Utility
Yes
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
You can also add before this string:
docmd.setwarnings False
and after:
docmd.setwarnings true

Then you will not get message ... records updated (OK, Cancel)
0
 

Author Comment

by:Matt Pinkston
Comment Utility
Now is there an easy way to test it first  
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
Change one string in source table and compare tables after update
0
 

Author Comment

by:Matt Pinkston
Comment Utility
Okay so when I build the form for onclick do I use
 
Macro Builder
Expression Builder
or
Code Builder

to put

DoCmd.RunSQL ("UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.Opportunity Id = Opportunities.Opportunity Identifier SET Opportunities_SolutionTrack.Sales Stage = [Opportunities]![Current Sales Stage Code]")

in?
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
Code Builder
0
 

Author Comment

by:Matt Pinkston
Comment Utility
I get a compile error when I click it

Here is what the code looks like

Private Sub Command0_Click()
DoCmd.RunSQL ("UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.Opportunity Id = Opportunities.Opportunity Identifier SET Opportunities_SolutionTrack.Sales Stage = [Opportunities]![Current Sales Stage Code]")
End Sub
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
All code should be in one string
0
 

Author Comment

by:Matt Pinkston
Comment Utility
here is the deck
testdb.accdb
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
For beginning I recommend you to use queries (you will not have errors with field names)
When query will do all what you need, you can use runSQL
testdb.zip
0
 

Author Comment

by:Matt Pinkston
Comment Utility
maybe I am missing something but it is doing nothing when I click the button
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
It is not asked you "Records will be updated?....."
Have you enabled content? (Yellow ribbon under standard ribbon in Access 2010)
0
 

Author Comment

by:Matt Pinkston
Comment Utility
that was the issue!
 
1. So now if I want to copy these pieces into my production DB how would I do that?
2. What do i modify to add additional fields to be updated?

Thanks
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
1. Import form and Query to you DB with import master (External data on ribbon). Open query in design mode and add all necessary fields, save, test and use
0
 

Author Comment

by:Matt Pinkston
Comment Utility
when I import the data the qry shows up in two places, just modify in one right?
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
It is by design of Access 2010. Select on top of navigation pane "Object Type"
0
 

Author Comment

by:Matt Pinkston
Comment Utility
so will this format be correct for multiple fields in the qry

UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.[Opportunity ID] = Opportunities.[Opportunity  Identifier] SET Opportunities_SolutionTrack.[Sales Stage] = [Opportunities]![Current Sales Stage], Opportunities_SolutionTrack.[Opprtunity Name] = [Opportunities]![Opportunity Name], Opportunities_SolutionTrack.[Opportunity Description] = [Opportunities]![Opportunity Description], Opportunities_SolutionTrack.[TCV] = [Opportunities]![Total Opportunity Value USD];
0
 

Author Comment

by:Matt Pinkston
Comment Utility
So I updated my production Access DB with the qry listed above, when I run I get the following:

First - You are about to run an update query that will modify data in your table "YES"
 
Second - You won't be able to undo the changes this action query is about to make to the data in a linked table or tables

The I get Run-Time error '3812':
You cannot update this field because the value your trying to apply is not valid or would break a data integrity rule

??????
0
 

Author Comment

by:Matt Pinkston
Comment Utility
Private Sub Command0_Click()

DoCmd.OpenQuery "Qry"
' OR
' docmd.runsql "UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.[Opportunity ID] = Opportunities.[Opportunity Identifier] SET Opportunities_SolutionTrack.[Sales Stage] = [Opportunities]![Current Sales Stage];"

End Sub
0
 

Author Comment

by:Matt Pinkston
Comment Utility
Should I be doing

Private Sub Command0_Click()

DoCmd.OpenQuery "Qry"
' OR
' docmd.runsql "UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.[Opportunity ID] = Opportunities.[Opportunity Identifier] SET Opportunities_SolutionTrack.[Sales Stage] = [Opportunities]![Current Sales Stage];"

End Sub
 
OR

Private Sub Command0_Click()

DoCmd.OpenQuery "Qry"
' OR
' docmd.runsql "UPDATE Opportunities_SolutionTrack INNER JOIN Opportunities ON Opportunities_SolutionTrack.[Opportunity ID] = Opportunities.[Opportunity Identifier] SET Opportunities_SolutionTrack.[Sales Stage] = Opportunities.[Current Sales Stage];"

End Sub
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
Comment Utility
"make to the data in a linked table or tables"
Have your tables linked to your DB? What kind of files is linked?
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 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

10 Experts available now in Live!

Get 1:1 Help Now