How do I combine forms and tables in Access?

[Guest]

Hi,

I am fairly new to Access and I have a question with what I imagine is an
easy answer. Two people are collecting data with two separate Access files
that have the same layout/structure. Data is input in a form and transferred
from a form to tables. I would like to consolidate the two databases so that
the forms as well as tables are consolidated. I realize that I could
probably simply paste the records from one table to the table in the other
file, however I would also like the forms to be consolidated. I would
appreciate any suggestions. Thanks in advance.

Ted

 

[Joseph Meehan]

Hi,

I am fairly new to Access and I have a question with what I imagine
is an easy answer. Two people are collecting data with two separate
Access files that have the same layout/structure. Data is input in a
form and transferred from a form to tables. I would like to
consolidate the two databases so that the forms as well as tables are
consolidated. I realize that I could probably simply paste the
records from one table to the table in the other file, however I
would also like the forms to be consolidated. I would appreciate any
suggestions. Thanks in advance.

Ted

What do you mean "consolidate" the forms?

Are the two people sharing one computer or do they both have access to
the same LAN?

Ideally you will want to have them both share the same table.

Give us a little more information and maybe we can offer some more
specific advice.

BTW you would normally link from one database to the table in the other
and use an append query to copy the data to your database.

 

[Guest]

Ted:

If the users have Access to a shared folder on a network then you should
split the database (for which Access includes a wizard) so the tables are in
a single back end .mdb file, which is put in the shared folder on the server,
and the forms, reports queries etc. along with links to the tables in the
back end are in a front end file, of which each user has a copy on their
local machine. The built in Linked Table Manager is used to refresh the
links when the back end is moved to the server. All users can then work with
the same set of tables simultaneously and always be up to date.

If the users are at remote sites or not networked then take a look at
Replication in Help. This enables remote users to work with different
versions of the database and for these to be periodically synchronized.

Simply appending data from one set of tables will only work in a very simple
scenario and rules out the use of autonumber fields as keys. Even without
the use of autonumbers and some means of avoiding duplication of key values
problems are likely to arise if two or more users add a new row to a
referenced (look-up) table such as Cities, where both might add London or New
York say, resulting in duplication of these when the data is appended from
one set of tables to another, which would also cause an index violation error
if the tables are correctly indexed. Even worse would be if one user added
Paris, France and another Paris, Texas! This could cause great confusion in
the resulting database when both sets are merged into one. Replication
includes sophisticated mechanisms to resolve these sort of problems when
replica databases are synchronized.

Ken Sheridan
Stafford, England

 

[Guest]

Joseph,

Each person collecting data is inputing the data on his own computer and his
own access file. They are not using the same computer and they are storing
the data in two different files. I would like to combine those two files to
make one. For example, right now each person is entering data into his own
form which goes to his own table. However, the forms and tables that both
people are using are identical (i.e., the forms ask the same questions and
the tables have the same fields).

I would like to know if there is any way to combine the data in their two
separate (but identically structured) files to make one file now that all of
the data needed has been collected. Just to be clear, it certainly would
have been easier if they had simply shared the same file, however they did
not.

Please let me know if I need to be more specific. Thanks.

 

[Guest]

Ted:

If the computers are not networked then Replication is the only realistic
solution. I did once develop an application which was used at three remote
sites and included a facility to synchronize the data from all three without
using replication, but the synchronization process was quite complex and
involved a large number of UPDATE queries to ensure that all of the problems
of the type I described in my last reply were avoided. With Replication
Access does all the hard work for you automatically.

Ken Sheridan
Stafford, England

 

[Joseph Meehan]

Joseph,

Each person collecting data is inputing the data on his own computer
and his own access file. They are not using the same computer and
they are storing the data in two different files. I would like to
combine those two files to make one. For example, right now each
person is entering data into his own form which goes to his own
table. However, the forms and tables that both people are using are
identical (i.e., the forms ask the same questions and the tables have
the same fields).

I would like to know if there is any way to combine the data in their
two separate (but identically structured) files to make one file


now that all of the data needed has been collected.

BINGO!

Have each of them to send you the FILENAME.mdb file

Save both files to your computer. Open one file. Go to the top line
menu (File - Get External Data - Link Tables) then link to the table in the
other database that you saved on your computer.

Now create a query based on the table you had and change it to a append
query using the linked table for the source. (if names or types are not
exact matches you may need to do some adjusting) then run the query. Delete
the link and you have what you want, one table with all the data.