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

x
?
Solved

ODBC connection error

Posted on 2014-08-08
14
Medium Priority
?
301 Views
Last Modified: 2014-08-11
We have an reports that were created in Microsoft Access 2003 that would connect through and ODBC connection to a SQL 2000 database without issue.

We have recently moved the database to a new SQL 2008R2 database and setup a new ODBC connection to that database without issue. But when we run the reports we are getting the following error.

Any assistance offered would be greatly appreciated. error.JPG
0
Comment
Question by:regsamp
  • 8
  • 3
  • 3
14 Comments
 
LVL 85
ID: 40249346
How did you create the new ODBC connection? If you didn't use Access, and instead used the Control Panel applet then you may have used the wrong one, depending on your platform.

If you're working on a 64-bit platform, you need to use the odbcad32.exe located in the Windows\SysWOW64 directory.
0
 

Author Comment

by:regsamp
ID: 40249348
I used the Control Panel applet on a 32 bit machine. The server is 64 bit.
0
 

Author Comment

by:regsamp
ID: 40249359
And the ODBC test comes back successful.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 668 total points
ID: 40249364
Try creating a connection directly in Access, using File - External Data, ODBC. You can create a new DSN, and supply login information during that process. Be sure to check the "Save Password" box when you link the tables.
0
 

Author Comment

by:regsamp
ID: 40249369
When I go to Access, using File - External Data, ODBC, there is no ODBC option. Only IMPORT or Link Tables.
0
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 1332 total points
ID: 40249627
Hi,

if you setup a new SQL Server then the network protocols are disabled by default. As shown in your error message, your connection tried to connect through Named Pipes. So to enable that, go to your server, start the SQL Server Configuration Manager and enable TCP/IP and Named Pipes protocol.

Moreover you need to allow remote connections on the SQL Server settings which is new in SQL Server 2008. This and more details how to setup you can find here:

http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

Cheers,

Christian
0
 
LVL 85
ID: 40249637
You would use the Link Tables option for this, which will start the Wizard and allow you to select/create an ODBC datasource. From there, you would build your DSN and use that to connect to the database.

I would suspect you can connect to the database, since you were able to create an ODBC connection (as you stated in your original comment) - however, as Christian indicates you must be sure the server is setup correctly.
0
 

Author Comment

by:regsamp
ID: 40249679
I can also connect through SQL Server Management Studio but not through Access.
0
 

Author Comment

by:regsamp
ID: 40249687
Remote Connections is allowed along with TCP/IP. There is no firewall active on the server. Named Pipes is disabled. Would that be doing it? When I try this below it will not connect. I can connect through ODBC and Server Management Studio but I cannot connect through any part in Access, including "Link Tables".

"You would use the Link Tables option for this, which will start the Wizard and allow you to select/create an ODBC datasource. From there, you would build your DSN and use that to connect to the database.
0
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 1332 total points
ID: 40249706
Did you try to enable Named Pipes protocol?
Please remember that you need to restart the SQL Server service whenever you change something with the SQL Server Configuration Manager.
0
 

Author Comment

by:regsamp
ID: 40249722
I just enabled it and which service exactly and where should I restart. This is a production database server so I will have to do it after.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 40249731
You need to restart the SQL Server service itself, can also be done in the Configuration Manager (you can see that on the screenshot in the link above: "SQL Server Services", if you open that you'll find the service(s), there you can restart the server).
0
 

Author Comment

by:regsamp
ID: 40249749
Okay, I will have to wait and to this after hours. I will restart it and then update after that.
0
 

Author Comment

by:regsamp
ID: 40254401
It looks it was the Named Pipes. Thank you for the help everyone.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
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…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

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