Saving linked table credentials in Access 2007

Question

I have an ASP.NET application written in C# and hosted on IIS6 Win2k3. This web application connects through code to an Access 2007 Database which holds Linked SharePoint lists in tables.

To get an idea of how this works you can go here:
[url="http://sharepoint.microsoft.com/Blogs/GetThePoint/Lists/Posts/Post.aspx?ID=68"]Link Access 2007 to SharePoint Lists[/url]




The issue is when the Access 2007 Database is set to “Working online with SharePoint” mode my application cannot connect properly and pull records. If I take the database to “Work Offline” mode then my web application has no problems connecting and pulling records.

At this time if you open the Access 2007 Database in windows explorer when you click on a linked table you will be prompted for the username and password. In a web application environment we won't get to see the prompt so the application fails.

I can open a connection to Access 2007 but if I go to pull records from a linked table it will need the username and password to get through the security on the remote SharePoint server. I have passed the credentials needed for that in a connection string but that is just used in creating a connection to Access 2007 initially and not the second set of credentials needed when pulling records from the linked table.

My Question is can I just save the credentials for the SharePoint Linked tables in Access 2007 so that I can bypass this step. This would be the same as opening the Access 2007 Database and clicking on a table and NOT being prompted for credentials as they are saved.


Thanks

Answer 1

There is some information on dynamically creating a link to a sharepoint list here: http://msdn.microsoft.com/en-us/library/aa159897(office.11).aspx

Unfortunately, the connection string does not include the userid and password.  If you can find out how to add the userid and password to the connection string then when the Access code begins running have it make a dummy link to any SharePoint list on the server.  You may have to actually touch some data in the list to force Access to say hello.  Once that link is established the rest should work without prompting.

Answer 2

Hello,

Yes that is the issue; how to get do this. I wish there was a way to just save the username and password in the Access Database itself but I haven't found anywhere to do that. 

Anyone got anymore ideas?

Answer 3

I think part of the problem is that Microsoft removed support for adding a userid and password to a URL.  Since the database definition is an HTTP URl it probably doesn't support a userid and password.

Answer 4

it works in ms access 2010. so I guess is an access 2007 bug

anyone have any idea how to fix in 2007?