ODBC Call Fail

[LaBam]

I have a database in MS Access with tables linked to an SQL Server 2000. Whenever i run my code that has to do with opening these tables, i keep on getting the error message that the ODBC has timed out. This is the portion of the code that is giving the error. Please help.

.............................................................
     Set db = CurrentDb()
    db.QueryTimeout = 0
[B] Set rstOutgoing = db.OpenRecordset("SELECT ANumber, Max(StartDate) AS LastCall FROM PREPAID " _
    & "WHERE (BNumber <> '100' and BNumber <> '101' and BNumber <> '130' and BNumber <> '150' and BNumber <> '160') " _
    & "GROUP BY ANumber ; ", dbOpenForwardOnly) [/B]   
With rstOutgoing
        Do While Not .EOF
            currNumber = !ANumber
            If IsNull(!LastCall) = True Then
                '.MoveNext
                LastCall = " "
            Else
                LastCall = !LastCall
            End If
                Set rstNewValue = db.OpenRecordset("SUBSCRIBER_STATUS", dbOpenTable)
                With rstNewValue
                        .Index = "PrimaryKey"
                        .Seek "=", currNumber
                        If Not .NoMatch Then
                            .Edit

...............................................................
Is there any way I could stop my ODBC Connections from TIMING OUT at tall?

 

[namliam]

If you are query-ing a table containing CDRs (call detail records) it will probably contain a gazillion records and doing a query like this is going to take hours to run (I may be over estimating a little ). I dont think you want that...

You can set the ODBC timeout in both the SQL database and in the registry somewhere. The lower of the 2 is the limitting factor...

Seriously, mosttimes on a CDR table there will be a index on starttime (or startdate as you have it) or on CreationTime (of the record) I suggest if you run this on a day to day basis or on a regular basis .... That you limit your query to that period i.e. Where Starttime >-= date() - 1 and starttime < date() or something... Date probably doesnt excist in SQL server... More of an Oracle man myselve... But you get the idea right?

 

[LaBam]

You can set the ODBC timeout in both the SQL database and in the registry somewhere. The lower of the 2 is the limitting factor...

Thanks for the reply. (You're quite right, I'm working on CDRs.)

I'm quite new to SQL so please bear with me if i seem to be asking too simple questions. Could you please elaborate on how I could set the timeout properties in the SQL database and in the registry?

 

[namliam]

I am not a SQL Server person, more of an Oracle person myself... So I cannot help you totaly...

Just use Regedit and search for ODBC and find your key. For Oracle by default it is set to 2 minutes (120 sec to be more precise)

Your reall issue is not in the ODBC, but rather in your query. Which is simply "to massive" and time consuming IMHO.