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:
- 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.
- I have tried converting the spreadsheets to XML but still cannot get the tables to link for reasons unknown to me.
- 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.