Need some help in efficiently setting up the following:
Need to be able to consolidate data currently contained in SharePoint
lists
in Access 2007 in order to efficiently produce consolidated reporting.
Currently there is one list contained in multiple SharePoint sites on our
Intranet - lists are standard across sites.
I see how to link the list to a database but would like to know if there
is
a way to link into multiple tables in a single database vs. a database for
each site/link. Again, the goal is to produce consollidated reporting.
Any thoughts?
I don't see why you can't create a linked table to each site in ONE access
database. There is the possibility that you might be asked for a logon when
you try to get data from different sites (but, you can usually check the
logon box "remember" during the logon process). Even if you are asked for
logons, you can still link to differnt sites.
So, It is certainly legal and possbile to create several linked tables to
several different SharePoint sites in ONE access database. You can even do
this if you have different logons for different sites.
After linking the tables, then I would run a series of update (actually
append) queries that sends the data from each linked table lists to a SINGLE
table in which you do your reporting on. I would do this for reasons of
performance, and also the possibility that you might be prompted for
different logons during the process that appends the lists to a single local
table. You likely do NOT want this logon prompt stuff occurring during a
report rendering (and using a union query that connects all tables and makes
them appear as a single table).
So, it is likely possible to build a union query that is based on all the
linked tables, but I would not be comfortable using such a query for the
data source of a report since each different site could in theory trigger a
logon...at least when you first run this union query. So, better as
suggested above to run a series of append queries that sends the data from
each linked table into a local reporting table. You then have all the data
in one table, and performance, and also the possible issue of having to log
into different sites will be eliminated during report generation....
