Win7 x64 / Office 2010 - ODBC connect error

I have a MS Word application which uses a DSN-less connection to access a remote SQL database and which runs properly under a 32 bit OS. However, on my Windows 7 x64 systems with Office 2010 (32 bit) I get the following error when running the application:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

I have done a bunch of searching, and have found a number of suggested solutions. The one which seems most likely to work is to install the Office System Driver Data Connectivity Components 2010.

Is this the best approach for solving the problem? If so, should I install the 32 or 64 bit version? If this is not the best approach, what would you suggest?

Thank you.

I have learned more. Sadly, the problem does not seem to be limited to my Win7 x64 machine. I have tried this application on two different XP machine and have received the exact same error message. This is particularly baffling since the exact same application ran perfectly 12 months ago.

Here is the relevant VBA code:

Script:

Dim cn As ADODB.Connection 
Dim cmd As ADODB.Command 
Dim rs As ADODB.Recordset 
 
Set cn = New ADODB.Connection 
cn.ConnectionString = "Driver={SQL Native Client};Server=<ip_address>;Database=<db>;Uid=<uid>;Pwd=<pwd>" 
cn.CursorLocation = adUseClient 
cn.Open 
 
Set cmd = New ADODB.Command 
Set cmd.ActiveConnection = cn 
cmd.CommandText = "sp_name" 
cmd.CommandType = adCmdStoredProc 
cmd.Parameters.Append cmd.CreateParameter("@Param", adChar, adParamInput, 25, "Value") 
Set rs = cmd.Execute 


I have also determined that the problem occurs before any data is transmitted to the remote server.

Since the application did not change - the date stamp on the .doc file is last August - I do not know what to look for in order to resolve the problem. Can anyone please suggest an approach?

Hi there
I'm trying a similar thing but using Office 2007 -- works perfectly and I'm on W7 X64

My application is an EXCEL spreadsheet that retrieves data from a remote SQL DB and doesn't seem to have any probs.

I've got Office 2010 on a Virtual W7 32 bit machine so I'll try that later --but of course running a Virtual Machine could add another layer of complexity into the mix.

Incidentally on a VIRTUAL W2003 server machine with Office 2007 the EXCEL application runs just fine so I suspect the deal is probably with Office 2010.

Did you use the ODBC software that the remote DB requires -- for example can you just do a basic Connect to the remote DB. in command mode and run any SQL queries.

This should tell you if you've got a problem with OBDC or not.

Another but possibly silly question -- has the remote DB changed its authentication procedures or updated ITS own software. If it has you might also need to apply some fixes on your front end.

Cheers
jimbo

Hi there
just tried a Virtual machine (W7 X86) running Office 2010.

No problem --EXCEL application retrieves data perfectly.

I suspect that the DB software has changed so you need to update your front end accordingly.

There shouldn't be any problems with the OBDC -- perfectly standard protocol.bo

If you are running a JAVA or VB script relying on the .NET interface you could be in trouble as this has considerably changed in W7 even from VISTA.

Cheers
jimb

The default 64bit ODBC is not visible to 32bit applications on a x64 Windows.

For 32bit applications running on a 64bit Windows OS, use ODBC located at:
C:\Windows\SysWOW64\odbcad32.exe

You can add a shortcut to it to your Control Panel.

The 64bit & 32bit ODBC apps look exactly the same, but your DSN entries will not show in both, unless you enter them in both.

Hi there
I forgot to mention that -- thanks for pointing it out.

Note that the MS office 2010 I've been using is the 32 bit version.

Cheers
jimbo