How to Link Sharepoint list to MS Access

[maxamis4]

Hello everyone,

I am looking for some help and references. What I would like to do is programmaticall y refresh my links to SharePoint via VBA. Currently I am prompted every time to enter the password I need to authenticate to SharePoint. This is fine but I would like to do this through the backend if possible. Does anyone have this type of experience doing this type of work?

[Narender Sagar]

what is the SharePoint and Access version are you using..?
In SharePoint 2010 & MS Access 2010, it can be authenticated by domain login itself. Once you are logged on to your domain server, you'll be automatically authenticated to SharePoint 2010. You don't have to authenticate yourself again & again, while refreshing your database.

[maxamis4]

Good question,

SharePoint 2010

Office (MS Access) 2007.

I am not on a domain since I am working on a project for a client so it will not be using domain services and requires login because of that.

[NeoPa]

This sounds like it breaks down to a networking question fundamentally. I've never used SharePoint (which I've never regretted so far), but I would guess that an (persistent) authenticated network connection to the server it is running on would give you what you need. A common way of achieving such a persistent connection is to set up a network path to a share from that server as a drive letter. Make sure to set "Reconnect at log on".

[maxamis4]

NeoPa,

As always you bring an excellent point to the table. A little run down on SharePoint 2010. SharePoint to simplfy things acts like an excel sheet (obviously 2o times more powerful) in the same fashion that you can enter data, have columns, rows, filter, sort, and store data. Same as an MS Access table. You can image the creative folks at microsoft saw this as opportunity to create functional applications in the cloud using sharepoint. My client does not care to invest in a sharepoint developer and knows that natively ms Access can plug into sharepoint. He wants to use Sharepoint as an online cloud storage for his database while maintaining it functional.

Believe me i have tried to talk him out of it but he feels it can be the source to provide a hybrid cloud application with DR capabilities.

That being said Office 2007 has issues connecting to sharepoint 2010. It doesn't seem to be able to establish a connection every time unless you force it to authenticate to 2010 sharepoint services. Not sure why this is but once the credentials are put in then sharepoint link works just fine. I wanted to do this in the backend to leverage the link table relationship to his SharePoint list (linked table). So that is why I inquired about the back end authentication.

[NeoPa]

Thanks Maxamis. Unfortunately, while what you say makes sense, I cannot go any further with it due to lack of direct experience with it. I wish you luck nevertheless :-)

[Narender Sagar]

Maxamis, your client has really chosen good option. SharePoint 2010 along with Access 2010, is really really powerful. I am using it for about two years and I know its potential. Reason are many like : Data is stored centrally on SharePoint lists (tables) which acts as back end and Front end is Access 2010. I can list down so many benefits, of using SharePoint...(B ut right now, I'll not go into details of it )
However, to answer your question, Pl. follow following steps:Internet Explorer
Local Site (Not internet)
Go to Internet Options->Security
Click on Local Intranet then click on sites and advanced.
Add your SharePoint site to this list then click add then close and OK.
On the same window, click Custom Level and scroll down to the bottom of that list where it says "User Authentication"
Change this to Automatic log on with current user name and password.
External SharePoint Site (Internet Site, not recommended)
Same steps as above, but click on Trusted Sites to add your SharePoint site
Then click on internet to adjust permission levels
You can also check following site :

http://technet.microsoft.com/en-us/library/dd572939(v=office.13).aspx