Integrating Access with Sharepoint

[VBnewb13]

Hello everyone,

This might not be the appropriate thread but I'm looking for some bigtime help and this is the closest subject I could find :P

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

[Delerna]

I am not sure you are going to be able to get access working accross the internet in that way, it's certainly not how I would approach the problem but I am interrested in watching the results of this....

[Arielle]

Please could you give us more information about your environment?

What version of Access?
What version of SharePoint? (ie, WSS or MOSS and are you using 2003, 2007 or 2010 version?)
Are you looking to migrate to SharePoint permanently or still utilize the current Access/Excel method?

[VBnewb13]

I believe it is MOSS 2007. The version of access is 2007 as well.

I have come up with half of a solution thus far. Here is what I did:

I posted my excel spreadsheets on sharepoint and wrote a simple macro for them that saves any changes to a duplicate spreadsheet which is located in a shared network drive. I linked the files in the shared drive to my access db on sharepiont and it works fine.

The problem I still have not solved is how to open the Access db on sharepoint without being prompted to choose edit or read-only mode. It's not a huge issue since I have the search functionality working with the linked spreadsheets now, but it would be a nice step to bypass. Note - I do not have administrative privelages on SP 2007.

[Delerna]

being prompted to choose edit or read-only mode happens when multiple people open the same database at the same time, doesn't it.