How to configure SQL Server Connection


I am building a SharePoint site. In one page, I want to add a form which allows users to enter a part number into a text box and query my MS Access databases for all the info related.

I noticed that SharePoint Designer 2010 has External Content Type which allows you to connect to exteranl database. But I am running into problems setting it up.

First, I selected SQL Server from the External Data Source Type Selcetion box. Then in the SQL Server Connection box, I see fields for Database Server and Database Name. What am I suppoed to put in the Database Server field if what I have is an MS Access database and this database reside on a shared drive? What if my other MS Access database is on the C drive of my laptop? I am not trying to make things unnecessarily complcated. The situation is just that I need to connection to multiple external database which locate on difference places.

I am pretty new to SharePoint. Maybe a fundamental quetion I shall ask is whether the External Content Type is the right choice I shall use to connect to Access databases not residing on a server, but a drive?


Who is Participating?
zephyr_hex (Megan)DeveloperCommented:
you need to configure the ODBC connection on your sharepoint web front end server.  and you must be able to access the Access database from the sharepoint web front end server
zephyr_hex (Megan)DeveloperCommented:
a few things...

first, your Access db needs to be on a network share, with proper folder permissions.  to make things easy, i'd recommend that you place it on your sharepoint web front end server.

you wouldn't use a SQL data connection if your database is Access.

you need to create proper ODBC connections to your Access database.  if you followed my advice above and put that Access db on your sharepoint web front end server, you'll create the ODBC connection there.  be sure you're NOT creating a User DSN.

if you have multiple Access db.... i recommend that you "connect" them all in one Access view, and then base your ODBC connection to that one Access view.

or... if your Access data isn't going to change... perhaps consider importing it to one Access db, or even to a SQL table.

nnrscAuthor Commented:
Thanks for the advices.

I cannot put the Access DBs on my SharePoint site because they all are too big, way excedding the size limit the company puts on. So I have to keep them on the shared drive. Also those DBs use linked tables from other sources and update everyday.

I am not sure if I got you right. Here is what I did in SharePoint Designer 2010. Site Objects -> Data Sources -> Database Connection -> Source -> Configure Database Connection -> use custom connection string -> Edit. Then I selected Microsoft .NET Framework Data Provider for ODBC and followed the Standard Security for MS Access ODBC connection strings.

The error message I got says "The server for the data source returned a non-specific error when trying to execute your query. Check the format and content of your query and try again. If the problem persists, contact the server administrator."

I also tried the OLE DB connection, same error.
Any idea how to fix it?
nnrscAuthor Commented:
Thanks. I think that link points to enough info I can use to tackle the problem.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.