[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Update data from a database to another

Posted on 2004-03-23
14
Medium Priority
?
295 Views
Last Modified: 2012-06-27
I want a command button to do the following :

I have several tables with the same structure.
One is the "mother" one with the full database  in one table called "MASTER"
The other is the "son" one with just a few records, so that user can work on its specific module - records are from table called "MASTER"

After updating information, user is resending me its "son" database

I want the button to update the records in my mother database from a command button from the "son" database, knowing that just the field "comments"  from table "MASTER" is updated.

I cannot work it out.

Thanks for the help




0
Comment
Question by:pixie2003
[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
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 10659702
Hello pixie2003,

At the time you want to run the update are these tables in different databse files or part of the same one (I'm counting linked-to as being in the same database).

If they are different database files what are the file names?

Pete
0
 
LVL 2

Expert Comment

by:nibirkhan
ID: 10659709
I assume your data source is from the "Son" table, don't try to use a query making from both Mother and Son db, I also assume you have all the tables on the same db, other wise use Link table properties.

Now, create an append query that will append from "Son" to "Mother"

Run that  query from a command button.

Let me know if you need details...

-Nibirkhan
0
 

Author Comment

by:pixie2003
ID: 10659785
They are in different database

Name of Database Mother is "Centro 30"
Name of Database Son  is varying (Ej . : 5687-Agro)


0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:pixie2003
ID: 10659835
If I append the fields, I am going to have them twice , once with the field "comments" empty and once with the field "comments" filled up !
How can I avoid to have twice the records ?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 10660340
I would suggest putting a link to the mother table in each of the child databases.   Create an update query in the child database which includes the mother and child matching tables.  Join on the key.
Update the mother field value with the child field value.

If you want a centralised solution then you could have a table of all the child database names in the mother db.
You could have a procedure which dynamically assigns/re-assigns a link to each  child database table in turn. For each link run a query to update mother with child data.

It's too late in the day here for me to try to write code to do this though.

Pete

0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 10660383
For this to work, you need a table key on both Mother and Son.  A key is a number of fields (at least one, but can be only one) that together will uniquely identify a record in a table.  The key you use in Son will be the WHERE restriction in Mother.

UPDATE Mother SET [Comment]=Son.[Comment] FROM Mother, Son WHERE Mother.[UniqueKeyField] = Son.[UniqueKeyField]
0
 

Author Comment

by:pixie2003
ID: 10665157
OK, a bit complicated for me !
But can't just I delete from my SON DATABASE the Records IN the MOTHER DATABASE with a criteria  and then just do an append QUERY ?
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 400 total points
ID: 10673164
It's not really all that hard.  I'm putting a few examples in here for some common statements you might use.  These are just examples, and will probably need a little editing to conform to your database.  

As a general rule, don't delete data when you don't need to.  It's an average of 15 seconds between the time you confirm the delete and the time you suddenly realize you needed something you just tossed.  It's your call though.  I'll show that example below also.

One note: these queries may not work if your tables have different structures.  In this example, I assume Mother and Son have duplicate structures.  

Suppose your two tables listed patients for a particular doctor.  The Mother table would have all the records, and the Son would have just those sent to a particular specialist.  Both have a field called PatientID that is a key field.  Each entry in PatientID is unique from every other one.  The first records might be 10001,10002,10003, etc.

Now suppose that the Son table has been changed by whomever records the notes for the specialist.  They put in all the new comments and send you the table.  After importing the table into Access, you would run the following line of code to put all the comments from Son into Mother without changing anything else.
 
DoCmd.RunSQL "UPDATE Mother SET [Comment]=Son.[Comment] FROM Mother,Son WHERE Mother.[PatientID]=Son.[PatientID]"

Now that you have all the information from the Son table, you need to delete all the records you just put into mother.  But Son could also have NEW patients with new PatientID values.  In that case, we need to import all the new records from Son.  You can do that with the following line of code.

DoCmd.RunSQL "INSERT INTO Mother FROM (SELECT Son.* FROM Son JOIN Mother ON Son.PatientID=Mother.PatientID WHERE Mother.PatientID IS NULL)"

Now you should have ALL the information from Son.  You can delete all the records in Son like this:

DoCmd.RunSQL "DELETE * FROM Son"

If you wanted to make sure the only records that were deleted were records already in your Mother table:

DoCmd.RunSQL "DELETE * FROM Son WHERE PatientID IN (SELECT PatientID FROM Mother)"

The final example is for the other method of deleting the records in Mother, and just appending the Son.  In that order, the code is:

DoCmd.RunSQL "DELETE * FROM Mother WHERE PatiendID IN (SELECT PatientID From Son)"
DoCmd.RunSQL "INSERT INTO Mother FROM (SELECT * FROM Son)"

If you feel your data integrity is good enough to use it, go ahead.  Can the people who edit the Son table ever remove a record?  Remember that it doesn't matter if they're supposed to or not.  Just having the ability is enough.  If so, that could result in data loss...the only copy of the record is in Mother, and you'll delete that before the import.
0
 

Author Comment

by:pixie2003
ID: 10674954
First, I wish to thank routinet for his explanation. Great. I will change the wy I did it then.
What I have done is the following.
I have linked the table of "Mother" in "Son", knowing that user using the "son" database are not linked to the network, so they cannot touch to the "mother" table.

Then I am doing this DoCmd.RunSQL to delete the selected records from the "mother" database (from the linked table)

Finally actua1 is an append query putting data from "son" database into "mother" database
:


DoCmd.RunSQL "DELETE * FROM Master1 WHERE Partida = [FORMULARIOS]![PANEL DE CONTROL]![LISTA132]"
DoCmd.OpenQuery "actua2"

Please comment.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 10677914
Looks great to me!  Does it work?
0
 

Author Comment

by:pixie2003
ID: 10678501
Yes, it does, but I think I can avoid the link in the "son" table using

INSERT INTO  xx IN xx FROM xx IN xx. Seems to work as well
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 10678658
I don't understand the statement.  What are the 'xx' supposed to represent?
0
 

Author Comment

by:pixie2003
ID: 10684873
xx i supposed to present the path of the statement :
DoCmd.RunSQL "INSERT INTO DSS0000001 IN '\\serverof\Calidad_import\sistema\comercial_bs.mdb'SELECT * FROM DSS0000001 IN '\\serverof\Calidad_import\comercial\db1.mdb' ;"
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 10692127
I am not familiar with that structure for an INSERT statement.  If it works for you, great!  I highly recommend you do a run with some test data to make sure it does what you think it does.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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