merge msaccess tables

Question

How can i merge two identical tables of two msaccess identical db? For eg:

db1..table1
ID   Name
1    Sanjana
2    Parul
3    Rohan

db2...table1
ID     Name
1      Sarika
2      Deepak

I want to append the values of second table into first as follows:

ID        Name
1         Sanjana
2         Parul
3         Rohan
4         Sarika
5         Deepak

Answer 1

The datatype for the ID field appears to be an autonumber. As such you can do the following:

INSERT INTO db1...table1
SELECT Name FROM db2...table1

Answer 2

Well since it is access, you have two ways. the first is mentioned by LSFR Consulting and the second would be to use the import wizard and tell the import to ignore the primary key column. That would merge the data from db2 into DB1 without having a primary key collision.

Answer 3

You can use an append query:

INSERT INTO Table1 ( FName ) IN 'c:\docs\ltd.mdb'
SELECT A.FName
FROM A;

Answer 4

OK, heres an approach more suited to a beginner making use of the gui.

1. Backup both databases and store them away somewhere safe.

2. Do a compact and repair from the tools menu on both databases

3. Create a linked table in db1 pointing to the table in db2 To do this right click on some white space in the table view of database window and choose link table... Follow the wizard to select db2 and then select table1.

4. Use an append query to append the data from the linked table1 into the db1.table1 Click into the queries view of Access, create a new query in design view, change its type to Append (right click in free space where the tables appear and go to type->append) Then choose db1.table1 when prompted as the table to append to. Now add the linked table1 into the query, select the fields from which you want to take data (in the example it would just be Name). Note you do not want to take the id field across as this will need to be updated to follow on from where db1.table1 left off, im assuming this is set to autonumber.

5. Delete the linked table from db1

Im not 100% certain the sort order will be retained from db2.table1 when its appended to db1.table1 as in your example. In most database designs this wont be important but if it is someone else may be able to shed light - i imagine that if the ID field in both tables is also the primary key it will.

Answer 5

If this is a one time operation simple copy-n-paste will work.

Open both databases in MS Access. Open both tables. Select values to copy (right-click on Column header and Ctrl+C).

Proceed to target table. Make Name field selected in the last row (new record). For this purpose mouse over Name column left edge (cursor becomes a plus sign) and click to select the cell. Ctrl+V. Done.