How to configure SQL Server Connection

Posted on 2011-04-26
Last Modified: 2012-05-11

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?


Question by:nnrsc
    LVL 42

    Expert Comment

    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.


    Author Comment

    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?
    LVL 42

    Accepted Solution

    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

    Author Closing Comment

    Thanks. I think that link points to enough info I can use to tackle the problem.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    I used to be SharePoint evangelist in our company, so my Outlook always full of questions about how to do this, or where I can find that. One day I found such an email with the following question: "how to attach 3-State workflow (one of the workflow…
    We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now