ODBC connection error

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
regsampAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
regsampAuthor Commented:
I used the Control Panel applet on a 32 bit machine. The server is 64 bit.
0
regsampAuthor Commented:
And the ODBC test comes back successful.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
regsampAuthor Commented:
When I go to Access, using File - External Data, ODBC, there is no ODBC option. Only IMPORT or Link Tables.
0
BitsqueezerCommented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
regsampAuthor Commented:
I can also connect through SQL Server Management Studio but not through Access.
0
regsampAuthor Commented:
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
BitsqueezerCommented:
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
regsampAuthor Commented:
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
BitsqueezerCommented:
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
regsampAuthor Commented:
Okay, I will have to wait and to this after hours. I will restart it and then update after that.
0
regsampAuthor Commented:
It looks it was the Named Pipes. Thank you for the help everyone.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.