Linking to multiple Excel tables

  • Thread starter Thread starter Bilbo
  • Start date Start date
B

Bilbo

Hi all
I have a number of spreadsheets on a shared drive, all
with the same columns in the same format, but information
relating to different offices.
I have linked them all individually into my database to
give the dynamic access that i need.
I am now getting greedy.
Is it possible to incorporate them all into one big table
that updates when the spreadsheets are updated, as the
individual linked tables do at present?
I am trying to do summary reports on the content of all
the spreadsheets and would rather not have to append them
each to a new table every time.
Any thoughts?
All assistance much appreciated.

Bilbo
 
Hi Bilbo,

If each of these worksheets is working reliably as a linked table, the
first thing to do is to count your blessings: linking to Excel is often
problematic.

You can't incorporate them into one big Access table without importing
(rather than linking) and therefore losing the "dynamic" aspect. The
next best thing (for now) is to build a union query that incorporates
all the linked tables. If you need to add a field that shows which
office each row originated from, do it like this

SELECT "XXX" AS Office, XXX.* FROM XXX
UNION ALL
SELECT "YYY" AS Office, YYY.* FROM YYY
UNION ALL
SELECT "ZZZ" AS Office, ZZZ.* FROM ZZZ
ORDER BY somefield;

Using the ALL keyword will (I think<g>) make it work faster; without it,
the query would spend time eliminating rows that appear in more than one
table.

The drawback of the union query is of course that it's not updatable.
 
Hi John

You are a wonderful person - may all of your dreams come
true and the sun shine forever on your house!
All problems seem to have been solved by the union query
although I am touching a large piece of wood when i say
that.
Thanks very much

Bilbo
-----Original Message-----
Hi Bilbo,

If each of these worksheets is working reliably as a linked table, the
first thing to do is to count your blessings: linking to Excel is often
problematic.

You can't incorporate them into one big Access table without importing
(rather than linking) and therefore losing the "dynamic" aspect. The
next best thing (for now) is to build a union query that incorporates
all the linked tables. If you need to add a field that shows which
office each row originated from, do it like this

SELECT "XXX" AS Office, XXX.* FROM XXX
UNION ALL
SELECT "YYY" AS Office, YYY.* FROM YYY
UNION ALL
SELECT "ZZZ" AS Office, ZZZ.* FROM ZZZ
ORDER BY somefield;

Using the ALL keyword will (I think<g>) make it work faster; without it,
the query would spend time eliminating rows that appear in more than one
table.

The drawback of the union query is of course that it's not updatable.


Hi all
I have a number of spreadsheets on a shared drive, all
with the same columns in the same format, but information
relating to different offices.
I have linked them all individually into my database to
give the dynamic access that i need.
I am now getting greedy.
Is it possible to incorporate them all into one big table
that updates when the spreadsheets are updated, as the
individual linked tables do at present?
I am trying to do summary reports on the content of all
the spreadsheets and would rather not have to append them
each to a new table every time.
Any thoughts?
All assistance much appreciated.

Bilbo
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Back
Top