Update data from a database to another

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

Who is Participating?
Steve BinkConnect With a Mentor Commented:
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:


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)"

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.
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?

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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

pixie2003Author Commented:
They are in different database

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

pixie2003Author Commented:
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 ?
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.


Steve BinkCommented:
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]
pixie2003Author Commented:
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 ?
pixie2003Author Commented:
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.OpenQuery "actua2"

Please comment.
Steve BinkCommented:
Looks great to me!  Does it work?
pixie2003Author Commented:
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
Steve BinkCommented:
I don't understand the statement.  What are the 'xx' supposed to represent?
pixie2003Author Commented:
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' ;"
Steve BinkCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.