Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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 :-)

share|improve this question

2 Answers 2

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.

share|improve this answer
    
I agree with your solution 100%, James, as this would be much easier to work with in my opinion. However, popular demand around the office is that excel is the norm/most comfortable. Excel offers color formatting and many other things that a simple sharepoint list does not offer, unfortunately. That being said, I have created the sharepoint lists already, but only as a means to get the data from excel into access in sharepoint. Now when people update excel, the lists are updated, which consequently updates access. I greatly appreciate your advice though, its a toughy cheers :) –  Anonymous Jul 29 '10 at 14:13

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.

share|improve this answer
    
I'm curious to try your advice about the UNC path. I am definitely going to look into that. Thanks :) –  Anonymous Jul 29 '10 at 14:33
    
You bet. Let us know if it works. –  Rob Wilson Jul 29 '10 at 15:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.