Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004

    Unanswered: ODBC Timeout / Custom Date Function

    I have an Access 2010 database linked to a SQL backend table. The back end table has 18,488,208 records. Im querying the table in Access to find records with the previous business days date using the following custom date function. It uses a holiday table. In the query criteria field I have PreviousWorkDay(date()) to pull the records. I get the ODBC time out error and Im pretty sure its because of the function probably doing a calculation on each record in the table.
    Is there a better way to get my results.

    Public Function PreviousWorkDay(dtmDate As Date) As Date

    'Created By: Robert L. Johnson III
    'Mod Date: February 19, 2003
    'Purpose: Determine the previous business (working) day
    'In: dteDate is the date to be checked
    'Out: Returns the previous business day
    'Example: PreviousWorkDay(#1/2/03#) returns 12/31/02
    ' (1/1/01 is a holiday (New Year's Day))

    Dim dtmTemp As Date
    Dim blnPreviousWorkday As Boolean

    dtmTemp = dtmDate - 1
    blnPreviousWorkday = False
    Do Until blnPreviousWorkday = True
    If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
    dtmTemp = DateAdd("d", -1, dtmTemp)
    blnPreviousWorkday = True
    End If
    PreviousWorkDay = dtmTemp

    End Function

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    isweekend is? (im guessing a function that returns true or false if the specified date is a Saturday or Sunday)
    isHoliday is? (likewise a function that identifies if the specifried day is a holiday) that where your time is going

    An ODBC timeout generally is triggered either if the query is taking to long to do something, or is taking to long to connect to the remote server
    but we don't know which because we don't know the code

    for know try to identify where the time is being consumed

    easiest way to do that is to put a break point on the code AND step through it
    or whilst developing try the code with the isHoliday set to return FALSE.... run the code see if that clears the fault
    then, if it doesn't then its either isweekend or something else that is causing the problem. so set is weekend to return false and re run the code
    step through it and see what happens
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Sorry for the late reply. I changed my e-mail address on the account and I couldn't get back in to the forum. In any case there are 6 other queries that use the custom Date functions Previousworkday, IsWeekend and IsHoliday that run flawlessly but the record sets they are querying are much smaller. I really think it's because the function is looking at the date field of 18,488,208 records checking it for each condition, and then moving to the next record. Thanks

  4. #4
    Join Date
    Dec 2013
    Provided Answers: 2

    The function PreviousWorkDay(date()) will calculate ONE unique value.

    Calculate this in VBA and pass it to a SQL Server Stored procedure.

    The stored procedure will use the value as criteria in your query and
    return a recordset containing only the desired rows.

    Do all of the query work on the Server-Side.


  5. #5
    Join Date
    Feb 2004
    yikes... My knowledge of SQL server is rudimentary at best, taking a class in March. Any chance you can tell me how? If it's really involved I'll understand if you can't devote that kind of time to my post....thanks

Posting Permissions

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