How do you link imported linked excell tables in Access

[Guest]

I have imported excell spreadsheets into Access which are linked to Excell.
How do I now combine these linked sheets into one linked Access sheet so that
I can then use that database to pivot off. I am using Access 2003 at this
point in time I am copying all of the data from the 30 linked sheets and
pasting it into an unlinked access sheet. Any help would be much appreciated

 

[Joseph Meehan]

I have imported excell spreadsheets into Access which are linked to
Excell. How do I now combine these linked sheets into one linked
Access sheet so that I can then use that database to pivot off. I am
using Access 2003 at this point in time I am copying all of the data
from the 30 linked sheets and pasting it into an unlinked access
sheet. Any help would be much appreciated

I am not sure what you are doing, but it sounds like you need to link
directly to the original Excel sheets rather than trying to link via an
intermediate database.

 

[Ron2006]

If you are trying to automate the process instead of copy and paste
then you need to write 31 queries.

1) empty resulting access table.
2 thru 31) Append each of the 30 linked Excell tables to the resulting
access table.

You are now ready to do what you want with the resulting accumulation
of data.

Ron

 

[Guest]

Thank you Ron this did help as now I can create a new table with all of the
information, but would it be possible to have the same accumulated table
linked to the others so that when I update the excell files the tables in
Access will get updated and therefore also the accumulated table. The
accumulated table will keep on getting bigger as the other linked tables are
history year to date tables which get updated every month. it's at about
1million lines at present.

Thank you for your help it has made the proccess much shorter.

 

[Guest]

Is it possible to link 30 excell spread sheets of about 55000 lines each to
one pivot table?

 

[Joseph Meehan]

Is it possible to link 30 excell spread sheets of about 55000 lines
each to one pivot table?

Sorry I don't know.

 

[Ron2006]

As to your first post, the answer is no.
The data in the combined table is actually a copy of each and every of
the other records in the other tables.

Now, since all of the tables appear to have the exact same structure
then instead of making the copies as we did, it may be possible to do a
very big UNION query. Now whether that would be too big / complex for
the query builder to handle I do not know.

The following is part of a posting about union queries that may give
you what you need:

=====================================================
I don't believe it's possible for a UNION query to multiply records. A
UNION
query can, however, subtract records. If you've got identical records
in two
(or more) of the tables, UNION will eliminate the duplicates. To
prevent
this from happening, use UNION ALL

Something I neglected to mention earlier was that should it be
desirable to
know from which table a given record came, you can add an extra field
to the
UNION query to indicate the source:


SELECT "Table1" AS Source, Field1, Field2, ... FROM Table1
UNION
SELECT "Table2" AS Source, Field1, Field2, ... FROM Table2
UNION
SELECT "Table3" AS Source, Field1, Field2, ... FROM Table3
UNION
SELECT "Table4" AS Source, Field1, Field2, ... FROM Table4


Doing that means that there cannot be duplicates among the subqueries
(unless, of course, the original tables contain duplicates), so no
records
will be eliminated as duplicates.


--
Doug Steele, Microsoft Access MVP
===============================================

Duplicates between queries may cause a problem.

Also remember that this does NOT make or constitute a back up of those
tables. It is simply a way of looking at the existing data. The number
of tables is what would worry me. Trying it is probably the only way of
finding out if it will work.

Also you cannot change the data on any of the records from access.(if I
remember correctly) since they are in spreadsheets.

Ron