Integrating Excel and Access with Sharpoint

Question

My problem deals with Excel, Sharepoint, and Access all in one. I will try to detail it as best as I can.

To start, I have an Access Database containing some forms and 3 tables. These 3 tables are linked to 3 Excel spreadsheets. The main purpose of this database is to act as a search form that takes user input and searches through the 3 linked tables and display the results.

What I want to do is put this Database onto SharePoint so that everyone on my team can use it for various searches. In order for information to stay current, I have the 3 excel spreadsheets on SharePoint for people to check out, update, and then check back in – (now you see the reason for linking the spreadsheets and tables, I want the updated information to be immediately displayed in the Database).

Here is where I run into the following 2 problems: 1) I cannot link an excel spreadsheet to an access database on SharePoint. The error message I receive basically states that the data needs to be in XML format in order to be linked.
a. I have tried converting the spreadsheets to XML but still cannot get the tables to link for reasons unknown to me. 2) The way my ‘search database’ works is a user inputs information, the database creates a new form (I used VB to make all this happen), and displays the results. My problem is that I cannot get the database to open in edit mode only from a document library. Every time I attempt to open the DB from the document library, a message pops up asking whether I want ‘read only’ or ‘edit mode.’ Ideally, I would like no message to pop up and just have the database do what it is supposed to do.

It seems to me that this should be relatively simple to fix but I just can’t figure it out. Logically, it makes sense to me that I could have the Access Database and the linked excel tables all in the same sharepoint folder and it should work no problem.

I have spent quite some time researching how to fix this problem but have not come up with any solution. Hopefully someone out there will be able to help :)

Thanks :-)

Answer 1

Would it be possible to recreate your spreadsheets as SharePoint lists? This way, people can concurrently update your data source with no need to check in/out the entire list.

Access will also be able to interact with your SharePoint list for your forms, unless you can leverage the Search capabilities to also match your needs.

Answer 2

I agree with James' solution above if recreating the spreadsheet as lists is feasible. If it is not, have you tried linking to the Excel docs via the document library's UNC path instead of using the URI?

I'm afraid the answer to your second question is not simple. Those are the kinds of reasons they introduced Access Services in SharePoint 2010.