S
Sharon
can anyone tell me if it is possible to combine two access databases (with
same column headings) and how.
same column headings) and how.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
Stefan Hoffmann
hi Sharon,
a little bit more patience...
file)?
Creata a union query to collect the data from both tables. Use this
query as a base for a table creation query.
mfG
--> stefan <--
a little bit more patience...
I assume you are talking from two _tables_ in one _database_ (one .mdbcan anyone tell me if it is possible to combine two access databases (with
same column headings) and how.
file)?
Creata a union query to collect the data from both tables. Use this
query as a base for a table creation query.
mfG
--> stefan <--
S
Sharon
Hi Stean
no I meant two different databases.
no I meant two different databases.
S
Stefan Hoffmann
hi Sharon,
the tables pane. There is an entry like: Link Tables.
Here you can specify an Access .mdb as source and then you can select
the tables you want. Do this twice for both databases.
Then you can use queries on these linked tables to combine the data.
mfG
--> stefan <--
Create a new database. In the database window, open the context menu inno I meant two different databases.
the tables pane. There is an entry like: Link Tables.
Here you can specify an Access .mdb as source and then you can select
the tables you want. Do this twice for both databases.
Then you can use queries on these linked tables to combine the data.
mfG
--> stefan <--
A
access user
import the tables from the second into the first and then append the records
in the tables from the second db into the tables from the first db
in the tables from the second db into the tables from the first db
R
Rob Parker
Hi Sharon,
I assume that you mean two tables within an Access database, rather than two
databases (which include all the tables, queries, forms, reports, macros and
modules). Or maybe you mean two tables in two separate databases.
Step 0. Before you start, make a backup copy of your database(s)
The way to do it is to set up an Append query and run it. Open a new query
in design mode, and add one of your tables to it. Then, on the Query menu,
select Append Query .... When you do, a dialog box will open, allowing you
to enter the name of the second table (the one which will contain all the
records when the append process is completed. If the second table is in a
separate database, click the radio button for "Another database", and enter
the filename/path in the text box, or use the Browse button to locate the
second database (.mdb) file. Since your field names are the same in both
tables, you can simply drag the * from the table showing in the top section
of the query design form into the first field of the grid. If you change to
SQL view of the query (either via the View menu, or from the right-click
menu in the top section of the design window), you will see something like:
INSERT INTO NameOfTable2
SELECT NameOfTable1.*
FROM FROM NameOfTable1;
If you have two separate databases, the first line will be something like:
INSERT INTO NameOfTable2 IN 'C:\Documents and Settings\Username\My
Documents\Another_Database.mdb'
To see the records which will be appended to the second table, you can click
the left-most icon on the query toolbar and change to Datasheet view. This
will NOT run the append query; it will simply show you the records which
will be appended when you do run the query. To run the query, either select
Run from the Query menu, or click the re ! icon on the query toolbar. Only
run the append query once (unless you want to append multiple sets of the
records from the first table).
INSERT INTO T1b IN 'C:\Documents and Settings\Rob\My Documents\Access\Tips
Tutorials & Tests\test.mdb'
If you have table constraints (eg. indexed fields with No Duplicates) which
prohibit duplicate records, you will get a message that some records could
not be appended if there are any records which could not be appended.
If you've got autonumber fields used as primary keys, which are used as
foreign keys in relationships, it becomes somewhat more complicated, as the
primary key will change when you do the append, but the foreign keys will
not. You will need to copy the existing autonumber field to a new field
before doing the append, and later use this field to link with the related
tables in update queries to change the foreign keys to the correct new
values.
HTH,
Rob
I assume that you mean two tables within an Access database, rather than two
databases (which include all the tables, queries, forms, reports, macros and
modules). Or maybe you mean two tables in two separate databases.
Step 0. Before you start, make a backup copy of your database(s)
The way to do it is to set up an Append query and run it. Open a new query
in design mode, and add one of your tables to it. Then, on the Query menu,
select Append Query .... When you do, a dialog box will open, allowing you
to enter the name of the second table (the one which will contain all the
records when the append process is completed. If the second table is in a
separate database, click the radio button for "Another database", and enter
the filename/path in the text box, or use the Browse button to locate the
second database (.mdb) file. Since your field names are the same in both
tables, you can simply drag the * from the table showing in the top section
of the query design form into the first field of the grid. If you change to
SQL view of the query (either via the View menu, or from the right-click
menu in the top section of the design window), you will see something like:
INSERT INTO NameOfTable2
SELECT NameOfTable1.*
FROM FROM NameOfTable1;
If you have two separate databases, the first line will be something like:
INSERT INTO NameOfTable2 IN 'C:\Documents and Settings\Username\My
Documents\Another_Database.mdb'
To see the records which will be appended to the second table, you can click
the left-most icon on the query toolbar and change to Datasheet view. This
will NOT run the append query; it will simply show you the records which
will be appended when you do run the query. To run the query, either select
Run from the Query menu, or click the re ! icon on the query toolbar. Only
run the append query once (unless you want to append multiple sets of the
records from the first table).
INSERT INTO T1b IN 'C:\Documents and Settings\Rob\My Documents\Access\Tips
Tutorials & Tests\test.mdb'
If you have table constraints (eg. indexed fields with No Duplicates) which
prohibit duplicate records, you will get a message that some records could
not be appended if there are any records which could not be appended.
If you've got autonumber fields used as primary keys, which are used as
foreign keys in relationships, it becomes somewhat more complicated, as the
primary key will change when you do the append, but the foreign keys will
not. You will need to copy the existing autonumber field to a new field
before doing the append, and later use this field to link with the related
tables in update queries to change the foreign keys to the correct new
values.
HTH,
Rob
S
Sharon
Thanks to everyone for their answers.
A
Aaron Kempf
you would be much better off if you were using Access Data Projects
then you could refer to cross-database information EASILY
then you could refer to cross-database information EASILY