?
Solved

Connect Sharepoint Designer to Access 2007 Database

Posted on 2010-01-12
14
Medium Priority
?
1,544 Views
Last Modified: 2012-08-13
hi guys,
I am trying to create a database connection with Sharepoint Designer 2007 to an access 2007 database. I can't figure the right syntax. I already found the website that shows the connection string but none are working.  http://www.connectionstrings.com/access-2007
My Database resides on a shared folder that my server and my computer have access:
//psh/database/david123.accdb
The test database "david123.accdb" has only one table called table1 and the database has no security setup ( I basically created a new database , created a table and saved it has data.accdb)

When I go to sharepoint Designer I click :
-Connect to a Database ...
-Use custom connection String > edit
-then I tried OLEDB and ODBC
-typed the following in Connection string:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\psh\database\david123.accdb;Persist Security Info=False;
but I get an error that says:
"Server Error: an error occured while retrieving the list databases from........"

Can someone explain me or show me the right method?
Thanks
David
0
Comment
Question by:taverny
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
14 Comments
 
LVL 16

Expert Comment

by:quihong
ID: 26295263
Connecting SharePoint to a Access DB is really not supported or practical because of the "double hop" issue.

To get around this, place your access database locally on the SharePoint server.
0
 

Author Comment

by:taverny
ID: 26295281
thanks for your response, I really thought we could do that. that;s weird.
so how do I connect if it's locally (let's says under my c drive)?
0
 
LVL 16

Expert Comment

by:quihong
ID: 26295322
I guess it depends on what you are trying to do.

My recommendation to put the access database locally on the SharePoint server was to overcome the double hop issue and was in context with setting up a (Dundas Charts) web part to pull data from the access db.
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:taverny
ID: 26295343
Well, what I am trying to do is create a webpart view of data stored in my Access database. I know I can create a linked list but I don't want to do that because my database is pretty big, and I only want 2-3 fields from a table .
So what I am trying to accomplish is to display those 2 fields on sharepoint by creating a new database connection.
0
 

Author Comment

by:taverny
ID: 26295395
I just tried to move the database to a local folder on the sharepoint server and I get the same error:
Data Source=D:\New Folder\David123.accdb;Persist Security Info=False;Provider=Microsoft.ACE.OLEDB.12.0
0
 
LVL 16

Expert Comment

by:quihong
ID: 26295425
How big or how many rows is the table? Linking the Access Database to a SharePoint list might be the best solution. I have users doing this with 50K records. As long as you create nice filtered views, it shouldn't be too big of a problem.

If you want to use the Data View Web Part, you'll probably have to run SharePoint Designer locally on the SharePoint Server, since if you run it locally from your workstation you can't properly reference the access db. If you somehow manage to reference it correctly, when you deploy it, your users won't be able to reference it properly.
0
 
LVL 16

Expert Comment

by:quihong
ID: 26295457
Looks like you need to add one additional step to have SharePoint "trust" access.

http://www.experts-exchange.com/Networking/Sharepoint/Q_22035689.html
0
 

Author Comment

by:taverny
ID: 26410525
I might have found a solution using SQL I am testing right now , I will let you know
0
 

Author Comment

by:taverny
ID: 26598189
I am actually testing with SQL Link Server, It seems to work but a little slow. I will post a final answer by the end of the week
0
 

Author Comment

by:taverny
ID: 27306502
Well, I understand that this question needs to be deleted or closed. I would say to close it and assign the point to quihong since he was right in my question , sharepoint can 't connect directly to an Access dabase.
I would like also to add the following that might help other resolve the same issue that I had.

I opened a case with Microsoft to find a solution to my problem and basically the best solution they had is to use a Sql Server to parse the data to sharepoint. I setup a linked server to an access database , then I created a blanK database in Sql and create a view to pull data from access. this way Shaerpoint can pull the view from the blank database.
they also gave me those following lins for different options , may someone will find those links helpful
Thanks
David

Office Access 2007 and SQL Server
http://technet.microsoft.com/en-us/library/cc178973.aspx

Create an Access project
http://office.microsoft.com/en-us/access/HA101679531033.aspx

Connect an Access project to a Microsoft SQL Server database (ADP)
http://office.microsoft.com/en-us/access/HP052745861033.aspx

Migration Case Studies - White Papers.
http://www.microsoft.com/Sqlserver/2005/en/us/migration-access.aspx

How to create an SQL pass-through query in Access
http://support.microsoft.com/kb/303968

Move Access data to a SQL Server database by using the Upsizing Wizard
http://office.microsoft.com/en-us/access/HA102755371033.aspx
0
 
LVL 16

Accepted Solution

by:
quihong earned 2000 total points
ID: 27308296
Wow!!!!

Using a SQL linked server that is connected to your MS Access database is a SUPER SLICK solution!

I love learning something new while trying to help someone. Thanks for sharing. You deserve the points :)
0
 

Author Comment

by:taverny
ID: 27479592
Thanks guihong, but you did actually answer the question, we can't connect sharepoint to Access database direclty ..too bad
performance using SQL is not the best , but for my purpose it works
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When using a search centre, I'm going to show you how to configure Sharepoint's search to only return results from the current site collection. Very useful when using Office 365 with multiple site collections.
In this article, we’ll look at how to deploy ProxySQL.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question