Connecting to new SQL Server
We replaced a server 2003 SQL 2005 32 bit with a server 2008 R2 SQL 2005 64 bit. Programs (using ODBC) updated on the new server run fine from the workstations (mostly Win 7 64 bit). The same ODBC definition is unable to see or connect to the new server from the workstations. An MS Access ADP is unable to run from the workstations; it runs fine on the new server. I have checked the SQL client drivers, comm protocols, and permissions.
Please help.
Please help.
I don't use the ADP format (because it never gave me anything I couldn't do more easily in an .MDB) and MS stopped upgrading it many years ago (around A2007). Is it possible that the ADP doesn't support SQL Server 2008 R2?
ASKER
It is SQL Server 2005 64 bit running on Server 2008 R2. The ADP provides significant performance improvement.
ASKER
The office version is 2003. I will check my client and see.
Versions of Access through A2010 (and possibly A2013 but I don't remember) support the .ADP but the .ADP cannot support newer versions of SQL Server. I know it can't do 2012 but I don't remember where it stopped. 2005 may be the end of its road since 2008 R2 came out AFTER A2007.
That is just the way software works. It can be made to support versions created prior to itself because those are known entities. It cannot be made to support versions created subsequent to itself because those are not known at the time UNLESS MS makes an update to make it possible. And they may have elected to not update the ADP to go to SQL Server 2008 R2 because by the time that version had been released, the ADP was already deprecated.
That is just the way software works. It can be made to support versions created prior to itself because those are known entities. It cannot be made to support versions created subsequent to itself because those are not known at the time UNLESS MS makes an update to make it possible. And they may have elected to not update the ADP to go to SQL Server 2008 R2 because by the time that version had been released, the ADP was already deprecated.
ADP support was dropped in Access 2013.
A2000/2003 should connect to SQL 2008 as long as you don't do any design changes in tables. If your going to do that, then I believe 2007 SP1 is the earliest you can be on.
I haven't used ADP's in years, so I'm a little out of date on things. Maybe someone else with some hands on with ADP's will jump in and have a definitive answer.
If not, I can try it.
Jim.
A2000/2003 should connect to SQL 2008 as long as you don't do any design changes in tables. If your going to do that, then I believe 2007 SP1 is the earliest you can be on.
I haven't used ADP's in years, so I'm a little out of date on things. Maybe someone else with some hands on with ADP's will jump in and have a definitive answer.
If not, I can try it.
Jim.
Hi,
ADP is designed to use ADO and has an own connection management which has nothing to do with ODBC, it is completely independent of the ODBC settings of the system so you do not need to check anything there.
When you look into the server connection of the ADP all settings must fit for your new server. All ADPs (independent of which Office or SQL Server version) can connect to a SQL Server because it's a simple ADO connection only restricted to use the SQLOLEDB driver.
Normally ADPs are constructed to also DESIGN objects of the used SQL Server, this is what you can't do with an older Access with a newer SQL Server (but the Access designers are not worth to be used, you should only use the SQL Server Management Studio or other professional designers to design the SQL Server objects). You can't also work with newer datatypes or some of them only limited. Another thing is that ADPs are using special stored procedures to work with SQL Server and so you can get in trouble if there are newer versions of them which maybe don't work in the expected way. So the general rule is: Don't use ADPs of older Office versions with SQL Server versions which didn't exist at the time the Office version came out. As you can deliver Access runtime versions until A2010 working with ADPs with up to SQL Server 2008 and for free there is no really sense in using A2003 for that.
But back to the connection problem: You would get a connection to the server in any way with any ADP version. You can also simply test if it is a pure connection problem by simply opening an Excel sheet, inserting a standard module and create a simple ADO connection sub with SQLOLEDB, that's the same what ADP does in the background. You can also use the SQL Server Profiler to see what's happening in the background, sometimes there are more detailed information using this way.
Please remember that any new SQL Server installation is protected to be used in the network, you need to enable TCP/IP protocol and mostly also the Named Pipes protocol. In Windows 7 sometimes I had the issue also on client machines where you can also configure the client protocol with SQL Server Configuration Manager.
You should of course also check your local firewall setting if the needed port is free, disable it temporarily to see if that's the problem.
Next is the use of the FQDN (full qualified domain name) of your server. In big networks sometimes subdomains cannot be found through the local DNS if only the servername itself is used in the connection. Be sure to always use the FQDN to avoid such problems. You can of course also use the IP address directly as they normally change only rarely in case of servers. Don't forget to use the instance name in your connection string also if you have more than one instance running on the server.
Cheers,
Christian
ADP is designed to use ADO and has an own connection management which has nothing to do with ODBC, it is completely independent of the ODBC settings of the system so you do not need to check anything there.
When you look into the server connection of the ADP all settings must fit for your new server. All ADPs (independent of which Office or SQL Server version) can connect to a SQL Server because it's a simple ADO connection only restricted to use the SQLOLEDB driver.
Normally ADPs are constructed to also DESIGN objects of the used SQL Server, this is what you can't do with an older Access with a newer SQL Server (but the Access designers are not worth to be used, you should only use the SQL Server Management Studio or other professional designers to design the SQL Server objects). You can't also work with newer datatypes or some of them only limited. Another thing is that ADPs are using special stored procedures to work with SQL Server and so you can get in trouble if there are newer versions of them which maybe don't work in the expected way. So the general rule is: Don't use ADPs of older Office versions with SQL Server versions which didn't exist at the time the Office version came out. As you can deliver Access runtime versions until A2010 working with ADPs with up to SQL Server 2008 and for free there is no really sense in using A2003 for that.
But back to the connection problem: You would get a connection to the server in any way with any ADP version. You can also simply test if it is a pure connection problem by simply opening an Excel sheet, inserting a standard module and create a simple ADO connection sub with SQLOLEDB, that's the same what ADP does in the background. You can also use the SQL Server Profiler to see what's happening in the background, sometimes there are more detailed information using this way.
Please remember that any new SQL Server installation is protected to be used in the network, you need to enable TCP/IP protocol and mostly also the Named Pipes protocol. In Windows 7 sometimes I had the issue also on client machines where you can also configure the client protocol with SQL Server Configuration Manager.
You should of course also check your local firewall setting if the needed port is free, disable it temporarily to see if that's the problem.
Next is the use of the FQDN (full qualified domain name) of your server. In big networks sometimes subdomains cannot be found through the local DNS if only the servername itself is used in the connection. Be sure to always use the FQDN to avoid such problems. You can of course also use the IP address directly as they normally change only rarely in case of servers. Don't forget to use the instance name in your connection string also if you have more than one instance running on the server.
Cheers,
Christian
ASKER
Thanks All. I am still trying your suggestions.
Hi,
it would be helpful if you show the exact error message and also what's the result in trying to open a connection using VBA and ADO in Excel.
Cheers,
Christian
it would be helpful if you show the exact error message and also what's the result in trying to open a connection using VBA and ADO in Excel.
Cheers,
Christian
ASKER
This is actually two separate problems.
One is the inability to connect via odbc to one database while having the ability to connect to a different database on the same server via odbc. The error message I receive is attached. This is for the connection unable to be made. The program using ODBC works fine when run on the server itself. When I try to launch it from a workstation (that successfully connects to a different database on the same server), the connection cannot be completed. I am so befuddled at this point I am not sure what to try next. If I try install a native client on the workstation, it fails and tells me it was due to a higher version of the client already on the workstation (which I believe to be false).
The ADP problem will be expressend in another question.
SQL-Err.doc
One is the inability to connect via odbc to one database while having the ability to connect to a different database on the same server via odbc. The error message I receive is attached. This is for the connection unable to be made. The program using ODBC works fine when run on the server itself. When I try to launch it from a workstation (that successfully connects to a different database on the same server), the connection cannot be completed. I am so befuddled at this point I am not sure what to try next. If I try install a native client on the workstation, it fails and tells me it was due to a higher version of the client already on the workstation (which I believe to be false).
The ADP problem will be expressend in another question.
SQL-Err.doc
Hi,
did you try everything which is explained on the following page?:
http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx
Again: Did you try to create a connection with ADO in Excel VBA like I said above and what was the result?
ADPs don't use ODBC and either no SQL Native Client, did you try to create a connection using the FQDN of the server including the instance name like I said above and what was the result?
It is not helpful if you repeat your problem without answering the questions.
Cheers,
Christian
did you try everything which is explained on the following page?:
http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx
Again: Did you try to create a connection with ADO in Excel VBA like I said above and what was the result?
ADPs don't use ODBC and either no SQL Native Client, did you try to create a connection using the FQDN of the server including the instance name like I said above and what was the result?
It is not helpful if you repeat your problem without answering the questions.
Cheers,
Christian
ASKER
Not yet.
Should this work for SQL AServer 2005? (It is not a SQL 2008 environment - just a MS Server 2008 R2 server)
I don't have much time to work on this. Please be patient.
Should this work for SQL AServer 2005? (It is not a SQL 2008 environment - just a MS Server 2008 R2 server)
I don't have much time to work on this. Please be patient.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You need to be carefull...there are two versions of the ODBC32ad.EXE program, a 32 bit and 64 bit one.
Which you use depends on the Office version you have. Sounds like your using 32 bit, so the one you need to use is:
C:\Windows\sysWOW64\ODBD32
The one from the control panel gives you the 64 bit one, which is in C:\windows\system32
Confusing right?
Make sure anything you define is for a system DSN.
Jim.