ODBC connection failed error 3146 (used to work)

[gguthrie]

This is a select statement that used to work. The sql db was upgraded from 7 to 2000, since it last worked, and that is the only change. the VB code is used in an Access 2000 app. Now I can run this script in Query Analyzer and it works, slow, but fine. But when it's run in Access it times out.

The culprit seems to be
WHERE (Clients.CCCID) Is Not Null
If I take this out it runs through the script, but it selects nothing, but this is the one statement that if removed allows it to not time out.

here's the select statement. Thanks in advance!


strSQLR = "select CCCID, cap from clients where CCCID not in " & _
"(SELECT Clients.CCCID AS CCCID FROM jobs right JOIN Clients ON jobs.ShopID=Clients.Cli " & _
" WHERE (Clients.CCCID) Is Not Null And jobs.Status='done' And jobs.[Month] = #" & CDate(txtMonth) & "# ) " & _
" and CCCID is Not Null and cap is Not Null"

[SkinHead]

Before thinking about rewriting the SQL query, try extending the timeout on the database connection object / SQL command object. I think the default is 30 secs. Make it 180 secs & see what happens.

If this does the trick, then think about rewriting the SQL query.