Update one database with data from another

[1angryadmin]

I have 2 databases: database A and database B

both have a table which is identical call it Table 1

I want to update table 1 in database B

with the data from table 1 in Database A

what is the easiest wat to accomplish this?


I love databases...

 

[darkangrydragon]

It's not very elegant, but:

1. Open Database A.
2. Open Table 1.
3. Press Ctrl+A to select the entire table.
4. Press Ctrl+C to copy the entire table
5. Open Database B.
6. Open Table 1 (in Database B).
7. Go to the add new record line at the bottom of the table.
8. Select Edit->Paste Append from the menu bar.
9. If you get any warnings, it usually means you have duplicate entries in both tables (at least entries with the same primary key). Click Yes to suppress further warnings.
10. All non conflicting records will be pasted into the new table.

Note: This will only work if the two tables have the exact same definition.

Hope that works!
Best regards,
Andrew

 

[1angryadmin]

thanks darkangrydragon but i am looking to do it programaticaly i currently use the copy and paste method

I copy the table in database A

then i paste it into database B

I would like to use a form or something that would automate the process so it could be scheduled.

This needs to occur once a week.

 

[darkangrydragon]

Well,

You could do this through VBA by opening a connection to your second db from the first and then using an SQL INSERT INTO statement. I don't have the exact code in front of me, but it is possible...

Good luck!

-Andrew

 

[1angryadmin]

VBA sounds like it might work does anyone have something more specific like a code example?