ODBC connection failed

[venkiatmarut]

Hi,

I am getting the error ODBC connection failed, when it was executing a query in VBA.

Yes, the query is quite complex and it has so many subqueries inside.
It may take some time say few minutes. but the error is coming without that query executing fully.


any Idea why this error is comming? Is there anywork aroud for this problem?

[venkiatmarut]

Hi,

I am getting the error ODBC connection failed, when it was executing a query in VBA.
any Idea why this error is comming? Is there anywork aroud for this problem?

When I am trying to execute query from Access MDB, it is showing the below error_______



[Microsoft][ODBC SQL Server Driver][SQL Server][DBNETLIB]ConnectionRead (recv()).(#10054) [Microsoft][ODBC SQL Server Driver][SQL Server][DBNETLIB]General network error. Check your network documentation. (#11)



Could some one help me out please?
:banghead:

[kunguito]

1) Does it just say ODBC connection failed?

2) What are you connecting to? Oracle? MySQL? ...

3) Did you install any ODBC client?

4) If answer of 3 is YES then
Have you tested that the configuration works?


on any other query? you might try an easy SQL.

[venkiatmarut]

Thanks for your reply.



1) Does it just say ODBC connection failed?

sorry. I am really sorry.

When I am trying to open the Access and trying to do the operations, it is giving the error ODBC call failed.



2) What are you connecting to? Oracle? MySQL? ...

The tables linked to SQL Server 2005 server.



3) Did you install any ODBC client?

I am using DSNs. ODBC client means I didn't get you?



4) If answer of 3 is YES then
Have you tested that the configuration works?
on any other query? you might try an easy SQL.

it is working for all the other querys. Only one query which has intern many subqueries and lot of inner joins and conditions. And data also have in huge amount.

[CreganTur]

If you haven't done so already, you may need to change your database to use ANSI 92, instead of using the default ANSI 89.

From the database window click Tools -> Options -> Tables/Queries -> click the This Database checkbox under the 'SQL Server Compatible Syntax (ANSI 92)' header.

I'm suggesting this because you state that the only query that fails is one that utilizes subqueries.

[kunguito]

I think I won't be of help! Sorry!



DNS: Data source name (http://en.wikipedia.org/wiki/Database_Source_Name)contains the information about a database that an ODBC uses to connect to it.

ODBC: Open Database Connectivity (http://en.wikipedia.org/wiki/ODBC) allows your program to connect to a database regardless of the specific DBMS.

[venkiatmarut]

If you haven't done so already, you may need to change your database to use ANSI 92, instead of using the default ANSI 89.

From the database window click Tools -> Options -> Tables/Queries -> click the This Database checkbox under the 'SQL Server Compatible Syntax (ANSI 92)' header.

I'm suggesting this because you state that the only query that fails is one that utilizes subqueries.

Thanks for your reply Randy

I didn't find the Datbase check box in the specified path. I am using Access 2000. I didn't find this option.

Please suggest

[CreganTur]

I didn't find the Datbase check box in the specified path. I am using Access 2000. I didn't find this option.


Sorry man, Access 2000 is not ANSI 92 compliant.

Try running a query with a subquery in Access that only queries your access tables. If it fails then it's because you're not ANSI 92 compliant, which means you will either have to upgrade to a newer version of Access, or rewrite your query without the subquery.

[venkiatmarut]

see the content from microsoft. I think this might also be the problem from SQL server end.

http://support.microsoft.com/kb/942861/en-us (http://support.microsoft.com/kb/942861/en-us)