Append records from DataBase1 to DB2

[ideas]

hi am using an application on two PCs their database is saved as MS access database, now my problem is that i need to join these two database as one, so i can only use 1.

Edited July 20, 2007 by ideas

[mazin]

What I can understand is that they are exactly the same database with the same design and same tables.

And, the only difference is that tables in each of them has different records. And you want to append the records of the first database to the other, then delete the first one; then finally use the second one.

Am I correct? If so, let me know!

[ideas]

What I can understand is that they are exactly the same database with the same design and same tables.

And, the only difference is that tables in each of them has different records. And you want to append the records of the first database to the other, then delete the first one; then finally use the second one.

Am I correct? If so, let me know!

.......... Yes you are correct ............

[mazin]

Well!

You haven't mentioned if the two Pcs are on a network or not.

You haven't mentioned if each database conssists of a single file (tables and other objects)

or two files; back end with tables and front end with linked tables and other objects.

In either case, I'll mean "database with tables" when I refer to DB1 or DB2 in the following lines.

Let's call the database on the other PC "DB1" and the database on your (or whatever) PC "DB2".

Let's assume that there's a table named "MyTable" in both DB1 anf DB2.

Now, you want to append records from MyTable in DB1 to MyTable in DB2.

Then, copy DB1 to your PC if the two PCs are not on a network. If the two PCs are on a network,

you don't need to copy DB1.

Here'e how to append records from DB1 to DB2:

1- Open DB2 in design mode. You can press SHIFT while opening DB2 to get to this mode.

2- Go to File --> Get Extrenal Data and click "Link Tables...".

3- When the "Link" dialog box appears, browse to where DB1 is.

4- Once you click DB1, the "Link Tables" dialog box appears displaying a list of tables

5- Select "MyTable" and press "OK".

Or press "Select All" to link all tables if there are records you want to append from, later.

6- When the "Link Tables" dialog box disappears, go to "Tables" page (in DB2, of course).

7- Since there's, already, a table named "MyTable" in DB2, the linked table will be named "MyTable1", automatically.

8- Now, go to "Queries" page. Create a new query using "Find Unmatched Query Wizard".

9- When the wizard appears, select "MyTable1" on the first page and "MyTable" on the next page.

On the third page, select a key field in each table. Key fields should be the same, of course. Press the button "<=>".

On the next page, press the ">>" button.

On the next page, name your query as AppendMyTable. Select "Modify the design" then click "Finish".

10- While in design view, go to "Query" menu and choose "Append Query...".

11- When the "Append" dialog box appears, select "MyTable" from the list of table names, then click "OK".

12- Now, in the last column in your query (where it says "Is Null"), delete the field name that appears in the row "Append To:".

13- Close the query agreeing to save.

14- Now, run the query. It will append, only, the new records from MyTable1 to MyTable.

Start, again, from step 8 through 14 for each table you want.

When you finish processing all tables you want, delete all linked tables.

Now, you no longer need DB1. You can delete it and use only DB2.

NB: I suggest that you change the topic title because this is not a JOIN.

[IcemanND]

Changed topic title to better reflect request.

[mazin]

Changed topic title to better reflect request.

Thanks, man.