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
Solved

Record Update in Access

Posted on 2010-08-25
42
418 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 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 500 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

860 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