One machine can't run my ADP, says there's no DSN

[Maury Markowitz]

I have an ADP app I run on most of the machines on my network. It uses the
SQL Server driver in the ODBC setup, cleverly named "SQLSERVER" along with a
connection string that uses the ODBC connection. Works great!

Except on the new machine. I set up everything exactly the same as I always
have in the ODBC control panel. However any attempt to connect from inside
Access results in an error stating there is no DSN and there's no default
driver.

The only think interesting about this machines is that it's x64 version of
Windows. Is this a problem?

Maury

 

[Sylvain Lafontaine]

64 bit version, yes, it's a problem but not a big one. ADP can only with
OLEDB providers and not with ODBC providers. DSN are exclusively for ODBC
providers, so when you are using a DSN for connecting with ADP, you are
using in fact the "Microsoft OLEDB Provider for ODBC" (also known as
MSDASQL) which is a strange chimera between OLEDB and ODBC.

The MSDASQL provider is obsolete and won't be ported to 64 bit. In order to
connect from a 64 bit machine, you must use a regular OLEDB provider for
SQL-Server (either for TCP/IP or for named pipes; I suggest that you use
TCP/IP). In case, simply provide the name of the server or its tcp/ip
address. You might have to add the port number - particularly if it's not
1433) and you might have other connection problem as well; all easily
solved. As you are already capable of creating a DSN toward this server,
you shouldn't have any real problem.

 

[Sylvain Lafontaine]

Of course, there is a big error in my previous message: Office and Access
2007 run exclusively under 32 bit; so when they are running on a 64 bit
machine, they are running in fact under the emulated 32 bit mode, also know
as WOW64 (don't ask my why it's not called WOW32). As such, ADP should be
able to use the MSDASQL provider in 32 bit mode.

After a little searching on the internet, I've found that DSN's for 32 bit
and 64 bit are stored separately in the registry: those in HKLM/HKCR are for
64bit while 32bit DSN’s are stored in WOW registry. You will have to use
the 32 bit odbc admin tool by running:

Windows\syswow64\odbccad32.exe

in order to create a 32 bit DSN for ADP.

However, my suggestion of directly using OLEDB instead of MSDASQL is still a
good one because it should give you a better performance. It's also
possible that you might still have problem even after using odbccad32.exe if
MSDASQL is not capable of running under the emulated 32 bit mode.