ODBC Call Failed with complete Connect string but not with ODBC only.

[Guest]

I have created a pass-through query between MS Access and an SQL Serverwith statements like

Set qdfPassThrough = dbs.CreateQueryDef("Duplicates"
qdfPassThrough.Connect = "ODBC;DATABASE=DBSQL;UID=sa;PWD=;DSN=Famous
qdfPassThrough.SQL = strQuery (i.e., the SQL statement as a string
qdfPassThrough.ReturnsRecords = Tru

Upon execution I receive the error: ODBC call failed (apparently #3151)

The odd thing is that when I reduce the Connect statement to just "ODBC;" and execute it, it will prompt me for the DSN, UID and PWD (no password) and I provide the exact same information as displayed in the long connect statement and it all works fine

I checked the client and server named pipes and TCP/IP and everything seems to be in order. What is going on here

Thanks

 

[Ron Weiner]

Betacon

What happens when you remove DSN=Famous from your connect string?

Ron W

I have created a pass-through query between MS Access and an SQL Serverwith statements like:

Set qdfPassThrough = dbs.CreateQueryDef("Duplicates")
qdfPassThrough.Connect = "ODBC;DATABASE=DBSQL;UID=sa;PWD=;DSN=Famous"
qdfPassThrough.SQL = strQuery (i.e., the SQL statement as a string)
qdfPassThrough.ReturnsRecords = True

Upon execution I receive the error: ODBC call failed (apparently #3151).

The odd thing is that when I reduce the Connect statement to just "ODBC;"

and execute it, it will prompt me for the DSN, UID and PWD (no password) and
I provide the exact same information as displayed in the long connect
statement and it all works fine.

I checked the client and server named pipes and TCP/IP and everything

seems to be in order. What is going on here?