Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Pass-Through Query Returns 0

    Hi all,

    Using Access 2013 / SQL Server Express 2012.
    My Pass-Through Query returns a count of 0.
    Should be ~2.2M
    The query works in Management Studio.

    No errors are received.
    What am I missing?

    thx
    w

    Code:
    select count(a.RecordNumber) As RecordCount
    from db.dbo.table a
    Where a.enddate between '7/1/2012' and '6/30/2013'
    ODBC Connection String:
    Code:
    ODBC;Description=SSE;Driver=SQL Server Native Client 11.0;Server=MyPC\SSEDB;Trusted_Connection=Yes;App=Microsoft Office 2013;Database=SSEDB;;Table=dbo.myTable

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Provided that the names of the objects (table, columns) are correct at the server level, here's what I would use:
    Code:
    Dim qdf As DAO.Querydef
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strConnection As String
    Dim lngCount As Long
    
    strSQL = "SELECT COUNT(RecordNumber) AS RecordCount FROM dbo.table WHERE enddate BETWEEN CAST('2012-01-07' AS SMALLDATETIME) AND CAST('2013-30-06' AS SMALLDATETIME);"
    strConnection = "ODBC;DRIVER={SQL Server};SERVER=MyPC\SSEDB;DATABASE=SSEDB;Trusted_Connection=Yes"
    Set qdf = CurrentDb.CreateQueryDef("")
    qdf.Connect = strConnection
    qdf.SQL = strSQL
    Set rst = qdf.OpenRecordset
    lngCount = rst.Fields(0).Value ' Not sure rst!RecordCount would work because RecordCount is confusing (it is already a property of a DAO.Recordset).  
    rst.Close
    qdf.Close
    Set rst = Nothing
    Set qdf = Nothing
    Have a nice day!

  3. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Siindho,

    Good stuff!
    I'll be borrowing that for sure.

    But my end-in-mind at the moment is connect to the Pass-Through Query From Excel PivotTable so on refresh, should execute the Pass-Through Query.

    Thanks
    w

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Sinndho,

    Any thoughts on why the Pass-Through Query does not return the expected result?

    thx
    w

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Pay attention to the way I pass the DATETIME values in my example (with and explicit CAST()). SQL Server is a bit "itchy" about such data types.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •