ODBC Connection to <dsn> failed

[bazianm]

Hi,



I have an application that is running with Access 2003 under XP and it is running fine. I am upgrading it to 2010 on Windows 7 and I am getting a connection error that makes no sense. Let's me show you the code first. It's pretty simple:

    adoJobRs.ActiveConnection = CurrentProject.Connection
    adoJobRs.Source = strSql
    adoJobRs.CursorLocation = adUseClient
    adoJobRs.LockType = adLockReadOnly
    adoJobRs.Open

The query that resides in strSql is a valid query. I know this because I suspended the program, queried the value of the variable in the immediate window, copied it and, after the program blew up, I created a query in the database, pasted in the query and I got a result.

The SQL is selecting data from a linked table. When I double click on the linked table, data comes up no problem.

An error -2147467259 is generated with a description of "ODBC--connection to 'CalLab' failed."

Clearly the DSN works else I would not get any data and all other queries work.

Some technical information:

1) Windows 7 64
2) Access 14.0.7015.1000 (64 Bit)
3) Microsoft Office Professional Plus 2010

I have 2 DSNs setup. One in the 32 bit admin and one in the 64bit admin (for when it was running under 32 bit office).

SQL Server 11.0.3128

I tried this with ADO 2.8 and 6.1 (recordset is 6.0).

Any assistance would be appreciated.

[ItsMe]

I don't have a lot of experience in ADO. But since there has not been any replies ...

How did you declare adoJobRs? What is strSql? Is it the name of a Stored Procedure, SQL statement, or a View?

[lfpm062010]

Did you check your library references?

[ItsMe]

Did you check your library references?

Apparently, they did

I tried this with ADO 2.8 and 6.1 (recordset is 6.0).

[bazianm]

Hi.

OK, I got this one figured out. I created a VM with Windows 7 32 bit, ran everything and it went away. I tried it using Windows 7 64 bit and Access 32 bit and I get an out of memory error. Looks like Access 2010 doesn't like Windows 7 64 in any case.

I will make this a new thread because the topic has changed. THANKS for all your help.