Functions and in line sql to fetch counts.. best way?

[munkee]

Hi all,

This is more of a check against what I am doing to see if this is the best / most optimised way of carrying out the procedure.

I run a number of queries against multiple tables to produce monthly and fiscal year figures.

These are:

Number of actions to be closed
Number of actions completed on time
Number of actions completed

Monthly/Fiscally.

In order to do this I use 3 combo boxes and a submit button on my main form with the code behind calling multiple functions to populate text boxes on the form:

Private Sub Command81_Click()
On Error GoTo pleasedont
 
    'Counting actions required to close in current month and current fiscal year
    Me.txtActionsReqClose = CountingActionsPerMonth(Me.cmboMonth.Column(0), Me.cmboYear.Column(0), Me.cmboDept.Column(1))
    Me.txtActionsRequiredClosedFiscal = CountingActionsPerFiscalYear(Me.cmboMonth.Column(0), Me.cmboYear.Column(0), Me.cmboDept.Column(1))
 
    'Counting actions completed on time in current month and current fiscal year
    Me.txtMonthActionsCompletedOnTime = CountingActionsClosedOnTimePerMonth(Me.cmboMonth.Column(0), Me.cmboYear.Column(0), Me.cmboDept.Column(1))
    Me.txtActionsCompletedOnTimeFiscal = CountingActionsClosedOnTimePerFiscalYear(Me.cmboMonth.Column(0), Me.cmboYear.Column(0), Me.cmboDept.Column(1))
 
    'Counting actions completed in current month and current fiscal year
    Me.txtActionsCompletedThisMonth = CountingActionsClosedPerMonth(Me.cmboMonth.Column(0), Me.cmboYear.Column(0), Me.cmboDept.Column(1))
    Me.txtActionsCompletedFiscal = CountingActionsClosedPerFiscalYear(Me.cmboMonth.Column(0), Me.cmboYear.Column(0), Me.cmboDept.Column(1))
 
Exitingyo:
    Exit Sub
 
pleasedont:
    MsgBox Err.Description & " - " & Err.Number
    Resume Exitingyo
End Sub

My functions use inline sql and DAO to return a value. I will post up one of my Fiscal functions just to see as an example as the query also contains a function within it as our fiscal year runs from October - September.

'Function used to count the number of actions that need to be closed for the fiscal year selected
Public Function CountingActionsPerFiscalYear(cmboMonth As String, cmboYear As Integer, cmboDept As String)
 
On Error GoTo jumpoutst
 
Dim dbst As DAO.Database
Dim rsst As DAO.Recordset
Dim strsqlst As String
Dim counterst As Integer
 
Set dbst = CurrentDb
 
 
strsqlst = ""
 
strsqlst = "SELECT Count(*) AS [Fiscal Actions To Complete]"
strsqlst = strsqlst & " FROM tblCorrective INNER JOIN (tbldept INNER JOIN tblSecurity ON tbldept.DeptID = tblSecurity.Udept) ON tblCorrective.CorrectivePerson = tblSecurity.SecurityID"
strsqlst = strsqlst & " WHERE (((tbldept.Department)='" & cmboDept & "') AND ((tblCorrective.CorrectiveCompletedDate) Between fiscalStartDate('" & cmboMonth & "'," & cmboYear & ") And fiscalEndDate('" & cmboMonth & "'," & cmboYear & ")));"
 
'Debug.Print strsqlst
Set rsst = dbst.OpenRecordset(strsqlst, dbOpenSnapshot)
If rsst.RecordCount <> 0 Then
counterst = rsst("Fiscal Actions To Complete")
Else
counterst = 0
End If
 
'Debug.Print counter
CountingActionsPerFiscalYear = counterst
 
 
completedyost:
rsst.Close
dbst.Close
Set rsst = Nothing
Set dbst = Nothing
Exit Function
 
jumpoutst:
MsgBox Err.Description & " - " & Err.Number
Resume completedyost
 
End Function
 

Is this a sound way to produce values? It runs pretty smoothly on my machine but I will be splitting the database soon and I would rather find out if the method is alright now before moving on and realising I am using a very slow method for retrieving values.

[hype261]

One of the way you could potentially speed this up is instead of using inline sql I would just create a parameterized query inside by database and then call that query from the code. Here is a quick example with a query called myQuery that is expecting a parameter @ID

Public Sub FancyFunction
On Error goto Err_FancyFunction
 
Dim qry as DAO.QueryDef
Dim rcd as DAO.Recordset
 
Set qry = CurrentDb.QueryDef("myQuery")
 
qry.parameters("@ID") = 1
 
set rcd = qry.openRecordSet
 
'Do something fancy with the recordset
 
Exit_FancyFunction:
rcd.close
set rcd = nothing
qry.close
set qry = nothing
 
Err_FancyFunction:
MsgBox err.description
resume Exit_FancyFunction
 

End Sub

The benefits of this is that the sql has already been parsed and optimized by the engine and could result in some performance gains. Also if you ever need to use the sql again some place you can easy do it without any copy and paste.

[patjones]

Hi,

If you did want to keep it inline, I'm not sure why you couldn't just write one general counting function to use anytime you needed to count something rather than having four separate functions which appear to do essentially the same thing.

Pat