Sharepoint 2010 Forum

Ask Question   UnAnswered
Home » Forum » Sharepoint 2010       RSS Feeds

Databases and Sharepoint 2010

  Date: Nov 01    Category: Sharepoint 2010    Views: 978

We have an extensive Microsoft Access database which we want to put
online for our own use (we are a church). I understand that Sharepoint and
Access will work together to some degree. So far I have been struggling with
trying to get information out of Access and into the Sharepoint lists, but I run
into too many limitations. One problem is that we are using a hosted Sharepoint
Services 2007 - just Foundation.

If we upgrade to 2010, will we get any better data integration? What I need is
to be able to make one sharepoint list look up data from another sharepoint list
- to do queries, in other words. We can do currently do this to a very limited
degree with our current state, but it isn't enough, and we are forced to to much
manual editing.

What we need is a web-front end to our database. Can Sharepoint 2010 be this?
Do I need Sharepoint Server, or do I need Sharpoint Standard or Enterprise to do
this, or is there a better alternative?



5 Answers Found

Answer #1    Answered On: Nov 01    

To be clear -- is it really a web front end that you need, or is it just the
ability for many geographically-distributed people to be able to use the
same data? If the latter, then you can put your tables into SharePoint
lists and convert your Access DB to use linked tables (linked to the SP
lists). At that point, any user can simply use the Access package that you
distribute, and everyone will have access (heh) to the same set of data.

Answer #2    Answered On: Nov 01    

Well, the catch is that we need the data BOTH inside our lan database for office
use (queries and all for other reasons) AND on a web page, AND up to date. The
data is live in the database or live on the sharepoint list, and to reconcile
the data, we have to do a manual update of one or the other as we receive
automatic update of the changes. It is a hassle, but I don't know how else to
do it. I'm not so sure just what I need as I've never been this far before.

The problem with linked tables is that the MS Access database data we need to be
on a sharepoint list is gotten from a query, not a table. Likewise, if a user
wants to make changes to the sharepoint list, sharepoint isn't able to do data
lookups. For example, Chapel X has an accountant named Bob. In our database,
Bob has his own record, and we give Bob's record number in the record for Chapel
X - the database then pulls all of Bob's data into Chapel X's form. Sharepoint
won't do that. So if we want to change the accountant for Chapel X from Bob to
Mike, we need to find the record ID for Mike. That would mean that all the info
for Mike needs to be in sharepoint too.

We have over 50,000 records in our MS Access database alongside so many queries
and also specialized forms. Right now we only need the info from one or two
queries to available online for external access (heh), but they need to be
editable, and it seems to complicated to make them editable unless we want to do
a lot of manual maintenance and monitoring.

Answer #3    Answered On: Nov 01    

In Sharepoint Designer, I see there is something called "Data View" - which lets
me insert data from sharepoint lists. Can I use that to create a list that is
the result of a query from two lists?

Answer #4    Answered On: Nov 01    

I figured something out! Here is how to create a query in
sharepoint (Sharepoint Services 2007!) using SPD, and display the queried
results of multiple sharepoint lists in a single table.

Answer #5    Answered On: Nov 01    

I believe you can use the Access database via BDC. However, why not upsize
it to SQL Server? Just a thought.

Didn't find what you were looking for? Find more on Databases and Sharepoint 2010 Or get search suggestion and latest updates.