SQL pass thru VBA / ODBC timeout

[pghquest]

I have the following count taking place in my Access VBA code. Sometimes it works, and sometimes it doesnt. Seems the timeout is at 60 and I cant figure out how to change it to 600.

If it errors I'm getting a 3146 ODBC - call failed error

Public Function GetQueryCount7C() As Long
CurrentDb.QueryTimeout = 0
    Dim rst As DAO.Recordset
    Dim sql As String
 
    sql = "SELECT     COUNT(*)/ 2500 + 1 AS total " _
        & "FROM         Boyd0315Final INNER JOIN " _
        & "InventorySuppliers ON Boyd0315Final.EAN = InventorySuppliers.LocalSKU " _
        & "WHERE     (InventorySuppliers.SupplierID = 315) AND (Boyd0315Final.Cost is not null) AND (InventorySuppliers.Cost <> Boyd0315Final.Cost) OR " _
        & "(InventorySuppliers.SupplierID = 315) and (Boyd0315Final.QOH IS NOT NULL) AND (InventorySuppliers.BoydQuantityAvailable <> Boyd0315Final.QOH)"
        Set rst = CurrentDb.OpenRecordset(sql)
    With rst
 
        If Not .EOF Then
            GetQueryCount7C = .Fields(0).Value
        End If
        .Close
    End With
 
    Set rst = Nothing
End Function

[jforbes]

I think it's because you are setting the timeout on CurrentDB. CurrentDB is a pointer to the currently opened Access database with the twist that the pointer is created each time that your code references it. I'm paraphrasing here, but you get some strange happenings while trying use it and this could be one of those.

It might work if you were to set you database into a variable, try changing:

Public Function GetQueryCount7C() As Long
CurrentDb.QueryTimeout = 0
Dim rst As DAO.Recordset
Dim sql As String
 

to:

Public Function GetQueryCount7C() As Long
Dim oDB As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Set oDB = CurrentDb()
oDB.QueryTimeout = 120 

An unrelated problem you may run into is that you are mixing and matching ANDs and ORs in your Where clause. This may give you unexpected results.

[pghquest]

Thanks for the response. Still receiving the following
Error 3146 ODBC - call failed.

I dont know what you mean by AND's and OR's

I wrote that part in SQL and then copied it and modified it for VBA

[Seth Schrock]

Between lines 10 and 11, put in the following code:

Debug.Print sql

This will "print" a line in the immediate window (Ctrl + g will show it if it isn't visible already) then run your code. Please post back what is entered into the immediate window. This will help us make sure that the string is being pieced together correctly.

[pghquest]

I'm getting a "2" in the Immediate window..

[Seth Schrock]

Hmmm. You should be seeing "SELECT COUNT..." Please post your code as you have it now.

[pghquest]

Currently have the following. Its timing out doing the select count. If I copy/paste the code into SQL it works fine, but it takes time. The tables are huge.. Hundred million + records..

Public Function GetQueryCount7C() As Long
CurrentDb.QueryTimeout = 0
    Dim rst As DAO.Recordset
    Dim sql As String
    sql = "SELECT     COUNT(*) / 2500 + 1 AS total " _
        & "FROM         Boyd0315Final INNER JOIN " _
        & "InventorySuppliers ON Boyd0315Final.EAN = InventorySuppliers.LocalSKU " _
        & "WHERE     (InventorySuppliers.SupplierID = 315) AND (Boyd0315Final.Cost is not null) AND (InventorySuppliers.Cost <> Boyd0315Final.Cost) OR " _
        & "(InventorySuppliers.SupplierID = 315) and (Boyd0315Final.QOH IS NOT NULL) AND (InventorySuppliers.BoydQuantityAvailable <> Boyd0315Final.QOH)"
        Set rst = CurrentDb.OpenRecordset(sql)
        Debug.Print sql
    With rst
 
        If Not .EOF Then
            GetQueryCount7C = .Fields(0).Value
        End If
        .Close
    End With
 
    Set rst = Nothing
End Function

[pghquest]

I changed the regedit and its running again.. not my ideal solution though..

[jforbes]

These might not be an option for you, but if you are having this much latency and you are connecting to a SQL Backend that you have access to, there are a couple things you can do to try to speed it up.

You can paste you SQL into Management Studio and then see what the execution plan will be. This would allow you to tweak the query to try to get it to run faster. You might find a simple fix.

Another option is to create a Scalar Function on the SQL Server to return the value you need. What you are doing it a really good fit for a Scalar Function. http://msdn.microsoft.com/en-us/library/ms191320.aspx