Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to configure SQL Server Connection

Posted on 2011-04-26
4
Medium Priority
?
853 Views
Last Modified: 2012-05-11
Hi,

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?


Thanks...

0
Comment
Question by:nnrsc
  • 2
  • 2
4 Comments
 
LVL 44

Expert Comment

by:zephyr_hex (Megan)
ID: 35470258
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.

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

third...
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.
http://www.sqlstrings.com/MS-Access-connection-strings.htm

also..
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.

0
 

Author Comment

by:nnrsc
ID: 35470989
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?
0
 
LVL 44

Accepted Solution

by:
zephyr_hex (Megan) earned 2000 total points
ID: 35471374
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

http://www.tek-tips.com/viewthread.cfm?qid=1292478&page=85
0
 

Author Closing Comment

by:nnrsc
ID: 35471431
Thanks. I think that link points to enough info I can use to tackle the problem.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There is one common problem that all we SharePoint developers share: custom solution deployment. This topic can't be covered fully in this short article, so all I want to do in this one is to review it from a development-to-operations perspectiv…
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
Integration Management Part 2
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

577 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