Solved

Record Update in Access

Posted on 2010-08-25
42
414 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
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
 

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 39

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 39

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 39

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 39

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 39

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 39

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 39

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 39

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

Expert Comment

by:als315
ID: 33529904
Yes
0
 
LVL 39

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 39

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 39

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 39

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 39

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 39

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 39

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 39

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 39

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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 …

911 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

20 Experts available now in Live!

Get 1:1 Help Now