Update data from a database to another

Posted on 2004-03-23
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

Question by:pixie2003
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
LVL 77

Expert Comment

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?


Expert Comment

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


Author Comment

ID: 10659785
They are in different database

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.


Author Comment

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 ?
LVL 77

Expert Comment

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.


LVL 50

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]

Author Comment

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 ?
LVL 50

Accepted Solution

Steve Bink earned 100 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:


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.

Author Comment

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.OpenQuery "actua2"

Please comment.
LVL 50

Expert Comment

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

Author Comment

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
LVL 50

Expert Comment

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

Author Comment

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' ;"
LVL 50

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.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

726 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