database merging

Question

I have two MS-Access databases (.mdb) with 10 rows and 5 columns in both databases and the data are same too, but i need to merge both .mdb to a new .mdb.

How do I do that?

Answer 1

You say 'database' but you seem to mean both database and a single table. Most databases consist of more than one table, and Access databases can consist of several tables, forms, reports and modules.

Here is an example of one of several ways of creating a new database from two others. Note that this does not take account of indexes and will not work if there is a unique index and both tables have the same values; if there isn't a unique index, you will end up with duplicate values in the new table / mdb, even in autonumber fields. It would be much better to specify the fields to be copied, however, as has been noted, the question is a little short on information, as is this answer.

Dim DBInA As String
Dim DBInB As String
Dim DBOut As String
Dim sSQL As String
Dim db As Database

DBInA = "C:\Docs\DBInA.mdb"
DBInB = "C:\Docs\DBInB.mdb"
DBOut = "C:\Docs\DBOut.mdb"

FileCopy DBInA, DBOut

Set db = OpenDatabase(DBOut)

sSQL = "INSERT INTO Table1 SELECT * FROM Table1 In '" & DBInB & "'"
db.Execute sSQL, dbFailOnError