Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    8

    Unanswered: Better, Faster VBA Coding upgrade to ADO or DAO?

    Hi Im looking for a more efficient way to run the following code using either DAO or ADO to speed things up or any other suggestions that you may offer.

    FYI:
    This is in a report Date header which serves as a beginning bank balance for that day. The Ending balance is in the Date Footer. It looks in the detail section for a date then it gets the ending balance from the previous day. Because the previous day might be NULL I came up with a piecemeal solution. Ideally it would search for the previous date and then use the balance for that date for the starting Balance for the date header. This report can be ran over multiple dates depending on which data is entered into the preceding form. It is accessing a table with a Date and EndBank Fields.

    The problem I'm having is that there might not be a day with a balance for a couple of days or longer. My solution provides for up to 3 days without a date and balance.

    I am looking for the most efficient way to achieve this using either DAO or ADO to move through the recordset. I have researched this and I cannot figure out how to move through the recordset to the next date without a null value. I'm sure it is simple but I'm having a mental block.

    CURRENT FUNCTIONAL WAY:
    Private Sub DateHeader_Format(Cancel As Integer, FormatCount As Integer)

    With Me
    Dim vBegBal, vDosBegBal, vTresBegBal As Variant

    vBegBal = DLookup("[EndBank]", "tblBalances", "[Date] = ([txtdate] -1)")

    If vBegBal <> vbNullString Then
    .BegBal = vBegBal
    Else
    vDosBegBal = DLookup("[EndBank]", "tblBalances", "[Date] = ([txtdate] -2)")
    If vDosBegBal <> vbNullString Then
    .BegBal = vDosBegBal
    Else
    vTresBegBal = DLookup("[EndBank]", "tblBalances", "[Date] = ([txtdate] -3)")
    If vTresBegBal <> vbNullString Then
    .BegBal = vTresBegBal
    End If
    End If
    End If
    End With

    End Sub


    ATTEMPTED WAY:
    I know this wont work but just a guess of what road to go down?

    Dim RS As Recordset
    Dim cn As Connection
    Dim vDate as Variant

    Set cn = CurrentProject.Connection
    Set RS = New Recordset

    With ME
    RS.Open "Select * from tblBalances WHERE [Date] = #" & ((.txtDate)-1) & "#")
    .BegBal = RS("EndBank")
    End With
    RS.Close
    Set RS=Nothing

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Generally, the DAO library is slightly faster than ADO when working with "pure" Access objects. I write "generally" because it can vary from one case to another and only testing can determine the fastest method. In any cases, the difference between both libraries is not very important (a few milliseconds in simple situations).

    Here's a solution using DAO. I'm not sure of what [txtdate] is in your code:the name of a control or the name of a column in tblBalances, I opted for the second possibility here.

    Code:
    Private Sub DateHeader_Format(Cancel As Integer, FormatCount As Integer)
    
        Const c_SQL As String = "SELECT EndBank, [Date], txtdate FROM tblBalances;"
        
        Dim rst As DAO.Recordset
        
        Set rst = CurrentDb.OpenRecordset(c_SQL, dbOpenSnapshot)
        With rst
            .FindFirst "[Date] = (txtdate -1)"
            If .NoMatch = False Then
                Me.BegBal = !EndBank
            Else
                .FindFirst "[Date] = (txtdate -2)"
                If .NoMatch = False Then
                    Me.BegBal = !EndBank
                Else
                    .FindFirst "[Date] = (txtdate -3)"
                    If .NoMatch = False Then
                        Me.BegBal = !EndBank
                    End If
                End If
            End If
        .Close
        End With
        Set rst = Nothing
        
    End Sub
    Notes:
    1. If [txtdate] is the name of a control, the lines applying the criteria become:
    Code:
    .FindFirst "[Date] = " & Me.txtdate.Value -3
    2. If txtdate is of type Date/Time or contains a value of Date/Time format, you shoud use the DateAdd() function instead of subtracting an integer from it:
    Code:
    .FindFirst "[Date] = DateAdd("d", -3, txtdate)"
    3. You should not use Date or any reserved word (see: Access 2007 reserved words and symbols - Access) for naming the objects in an Access database. Sooner or later, there will be problems because of that.

    4. Do not expect a big difference when using such a code as compared with the code you use now.

    5. For trying to measure the execution time, you can use the GetTckCount API function, like this:
    Code:
    Private Declare Function GetTickCount Lib "kernel32" () As Long
    
    Private Sub DateHeader_Format(Cancel As Integer, FormatCount As Integer)
    
        Const c_SQL As String = "SELECT EndBank, [Date], txtdate FROM tblBalances;"
        
        Dim lngTStart As Long
        Dim rst As DAO.Recordset
        
        lngTStart = GetTickCount
        Set rst = CurrentDb.OpenRecordset(c_SQL, dbOpenSnapshot)
        ...
        End With
        Debug.Print "Execution time in milliseconds: "; GetTickCount - lngTStart
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Sep 2010
    Posts
    8
    Sinndho,
    Thank you so very much for the detailed and thorough explanation and different options.
    This is what i was looking for I just couldn't wrap my head around it.

    I do have some further thoughts and or questions.

    Notes:
    1. txtDate is a control on the report.
    2. txtDate is of the Date/Time Type so I will have to use:
    .FindFirst "[Date] = DateAdd("d", -3, txtdate)"
    3. Yes leftover bad code from when it was first written
    4. GREAT. TY

    This only covers basically 3 days previous to the [Date] per my example
    I'd prefer to not have to layer the if/then statements from -3, -2, -1 and have a more dynamic solution to calculate the difference between me.txtdate and the next closest [date] backwards THEN use the DAO to zero in on the balance for that day.

    Could I, or is it possible, to use a DateDiff to calculate the days between Me.txtDate and the last [Date] closest to Me.txtDate in the table then use one DateAdd Statement so it is a more dynamic calculation?

    Something like:
    Dim vDateCalc as Long
    vDateCalc=DateDiff("d", me.txtDate, [Date])

    THEN

    .FindFirst "[Date] = DateAdd("d", -VdateCalc, txtdate)"

    I'm sure this is quite a stretch but just trying to get it to be a more dynamic calculation to account for all instances.

    This report is a check ledger with me.txtdate generated from a union query. I am looking up a previously recorded bank balance in the table Balances for the day before me.txtDate. BUT if for some reason there is no balance I am going back 3 days searching for another balance to use.

    It is quite possible, for some reason or another, that it might be more than 3 days and rather than manually use if/then statements I would prefer to use something to 1st calculate the difference in days between me.txtDate and the next day in the past and then apply that difference to subtract days from the control me.txtdate to get the EndingBalance in tblBalances.

    Thank you again for your help. I can use this as is. I am just trying to further understand what my options are and make it more dynamic than static code.

    Any further comments or thoughts you can offer are appreciated.

    Regards,

    Bmw

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could simplify the date calculations by creating then using a Calendar table. In its simplest form, it consists in only one column of type date/Time that contains dates for a certain period of time (say from 1/1/2000 to 12/31/2050.

    Such a table, that needs to be created only once is easy to define and to fill:
    Code:
    Public Sub FillCalendar()
    
        Const c_SQL As String = "CREATE TABLE Tbl_Calendar ( Cal_Date DATETIME NOT NULL );"
        
        Dim rst As DAO.Recordset
        Dim varDate As Date
        
        CurrentDb.Execute c_SQL, dbFailOnError
        Set rst = CurrentDb.OpenRecordset("Tbl_Calendar", dbOpenDynaset)
        With rst
            varDate = #1/1/2000#
            Do
                .AddNew
                !Cal_Date = varDate
                .Update
                varDate = DateAdd("d", 1, varDate)
            Loop Until varDate = #1/1/2051#
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    Notice that a more realistic (and more useful) calendar table would look like:
    Code:
    CREATE TABLE [Tbl_Calendar]
        ( [Cal_Date] DATETIME NOT NULL CONSTRAINT PrimaryKey PRIMARY KEY,
          [Cal_DateInt] INTEGER NOT NULL,
          [Cal_Year] INTEGER NOT NULL,
          [Cal_Month] INTEGER NOT NULL,
          [Cal_Day] INTEGER NOT NULL,
          [Cal_YearDay] INTEGER NOT NULL,
          [Cal_DayNumber] INTEGER NOT NULL,
          [Cal_WeekNumber] INTEGER NOT NULL,
          [Cal_WorkDay] BIT NOT NULL,
          [Cal_Comment] TEXT(50) NULL
        );
    Now you can use an INNER JOIN between Tbl_Calendar and your data set, e.g.
    Code:
    Const c_SQL As String = "SELECT * FROM Tbl_Data INNER JOIN Tbl_Calendar " & _
                            "ON Tbl_Data.SomeDate = Tbl_Calendar.Cal_Date " & _
                            "WHERE Tbl_Calendar.Cal_Date Between DateAdd("d", -3, #@D#) And #@D#;"
    
    dim strSQL As String
    
    strSQL = Replace(c_SQL, "@D", SomeDateValue)
    Last edited by Sinndho; 08-05-14 at 17:22.
    Have a nice day!

Tags for this Thread

Posting Permissions

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