Solved

Update data from a database to another

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

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 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]
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 50

Accepted Solution

by:
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:

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 50

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 50

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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

760 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

19 Experts available now in Live!

Get 1:1 Help Now