?
Solved

Record Update in Access

Posted on 2010-08-25
42
Medium Priority
?
441 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
[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
  • 22
  • 19
42 Comments
 
LVL 40

Expert Comment

by:als315
ID: 33519668
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
ID: 33519697
Not sure how to do that
0
 
LVL 4

Expert Comment

by:javaftper
ID: 33519699
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:Matt Pinkston
ID: 33519741
I have access 2010
0
 

Author Comment

by:Matt Pinkston
ID: 33519752
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 40

Expert Comment

by:als315
ID: 33519870
Check example
db5.accdb
0
 

Author Comment

by:Matt Pinkston
ID: 33519908
looks cool but it gets an error, and I can't see what it is doing
0
 
LVL 40

Expert Comment

by:als315
ID: 33519915
What error your get?
0
 

Author Comment

by:Matt Pinkston
ID: 33521865
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 40

Expert Comment

by:als315
ID: 33523338
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
ID: 33523468
nope not working, I just need a simple way to do this.
0
 
LVL 40

Expert Comment

by:als315
ID: 33523993
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
ID: 33524507
so do i do with command or screens, not a huge access guy
0
 
LVL 40

Expert Comment

by:als315
ID: 33524923
Check again.
db5.zip
0
 

Author Comment

by:Matt Pinkston
ID: 33526695
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
ID: 33527210
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 40

Expert Comment

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

Expert Comment

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

Author Comment

by:Matt Pinkston
ID: 33529801
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 40

Expert Comment

by:als315
ID: 33529846
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
ID: 33529875
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
 
LVL 40

Expert Comment

by:als315
ID: 33529904
Yes
0
 
LVL 40

Expert Comment

by:als315
ID: 33529921
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
ID: 33529923
Now is there an easy way to test it first  
0
 
LVL 40

Expert Comment

by:als315
ID: 33530016
Change one string in source table and compare tables after update
0
 

Author Comment

by:Matt Pinkston
ID: 33530434
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 40

Expert Comment

by:als315
ID: 33530583
Code Builder
0
 

Author Comment

by:Matt Pinkston
ID: 33530646
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 40

Expert Comment

by:als315
ID: 33530807
All code should be in one string
0
 

Author Comment

by:Matt Pinkston
ID: 33530864
here is the deck
testdb.accdb
0
 
LVL 40

Expert Comment

by:als315
ID: 33531265
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
ID: 33531412
maybe I am missing something but it is doing nothing when I click the button
0
 
LVL 40

Expert Comment

by:als315
ID: 33531776
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
ID: 33531890
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 40

Expert Comment

by:als315
ID: 33532004
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
ID: 33534204
when I import the data the qry shows up in two places, just modify in one right?
0
 
LVL 40

Expert Comment

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

Author Comment

by:Matt Pinkston
ID: 33534527
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
ID: 33535964
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
ID: 33536159
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
ID: 33536352
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 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 33538952
"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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 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