regsamp
asked on
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.
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.
ASKER
I used the Control Panel applet on a 32 bit machine. The server is 64 bit.
ASKER
And the ODBC test comes back successful.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I go to Access, using File - External Data, ODBC, there is no ODBC option. Only IMPORT or Link Tables.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
I can also connect through SQL Server Management Studio but not through Access.
ASKER
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.
"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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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).
ASKER
Okay, I will have to wait and to this after hours. I will restart it and then update after that.
ASKER
It looks it was the Named Pipes. Thank you for the help everyone.
If you're working on a 64-bit platform, you need to use the odbcad32.exe located in the Windows\SysWOW64 directory.