merging databases

[dennis]

How do I merge two databases in Microsoft Access 2000?

 

[John Vinson]

How do I merge two databases in Microsoft Access 2000?

That depends on what is in them. Do you have similar tables in the two
databases, just with different data? Different tables? The same, or
different forms/reports/modules? If you need to combine the data in
tables in two separate databases, do they have Autonumbers (if so
there will be added difficulties since you'll have different data for
the same autonumber value)?

 

[Naresh Nichani MVP]

Hi:

You can try using the Append Query feature in Access.

Create a table with same data types as in source database - you can copy
structure.

Then create two Append queries - first appends data from first database and
the second from other database.

Run these 2 queries in Database window and test.

As John said watch out for AutoNumbers - this may cause relationships to
fail with linked tables etc. If you have AutoNumbers etc you may need some
code to do this.

Regards,

Naresh Nichani
Microsoft Access MVP

 

[toby]

i've the same problem as dennis, i 've different excel file as the database
source. they all linked up to the access. and i want to use query ("select
from") function to draw data with specific criteria from these sources at
the same time.
so, can u describe a bit more how to make it?? Naresh.

Thanks in advance.

Toby

 

[John Vinson]

i've the same problem as dennis, i 've different excel file as the database
source. they all linked up to the access. and i want to use query ("select
from") function to draw data with specific criteria from these sources at
the same time.
so, can u describe a bit more how to make it?? Naresh.

Two ways:

- Probably best: create a local Table in Access with the desired field
sizes and datatypes. When Access links to or imports from Excel it has
to guess at the proper datatype and often guesses wrong (e.g. fields
containing a mix of text and numbers may get treated as numbers). Use
File... Get External Data... Link to link to each spreadsheet in turn
and create an Append query to append from the spreadsheet to the
table. Now you have a single table upon which you can base a query.

- Another possibility: Use File... Get External Data... Link to link
to each of the spreadsheets. Create a UNION query to string them all
together into one long recordset - see the online help for UNION, it's
not hard but you need to go to the SQL window to create it. You can
put criteria on each spreadsheet's data, or base another query with
flexible criteria on the UNION query.