Functions/Inline sql and DAO most efficient way?

[munkee]

I populate around 45 textboxes on a form to display various counts of records within my database across multiple tables.

I use the basic framework in my submit button of:

 '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))

Calling functions to populate textboxes based on different inputs for each textbox in these functions (as I said there are around 45 textboxes with 12 different functions being called to populate them.)

The basic setup of each function is as follows:

'Function used to count the number of incidents per month based on month/year/department and incident type
Public Function CountingIncidents(cmboMonth As String, cmboYear As Integer, cmboDept As String, IncidentTyping As String)
On Error GoTo jumpout
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String
Dim counter As Integer
Set db = CurrentDb
 
strsql = "SELECT Count(*) AS CountOfHSEID"
strsql = strsql & " FROM tblIncidentType INNER JOIN (tbldept INNER JOIN tblhselog ON tbldept.DeptID = tblhselog.Department) ON tblIncidentType.IncidentTypeID = tblhselog.Incident_type"
strsql = strsql & " WHERE (((tbldept.Department)='" & cmboDept & "') AND ((tblIncidentType.IncidentType)='" & IncidentTyping & "') AND ((Year([Date_reported]))=" & cmboYear & ") AND ((Format([Date_reported],""mmmm""))='" & cmboMonth & "'));"
 
Set rs = db.OpenRecordset(strsql, dbOpenSnapshot)
If rs.RecordCount <> 0 Then
counter = rs("CountOfHSEID")
Else
counter = 0
End If

CountingIncidents = counter

completedyo:
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Exit Function
jumpout:
MsgBox Err.Description & " - " & Err.Number
Resume completedyo
End Function

As I said the other functions are all slightly different, counting different tables and across different time scales etc.

My basic question is am I doing this as efficient as I could be?

I have been wondering whether using DAO is the correct method to be executing the sql and I have got my mind lost a bit between DAO ADO and also just doing a docmd.runsql call. I could also just set up some querydefs which are waiting for parameters to go in but I am pretty sure this isn't the best way.

The whole procedure currently takes 4 seconds to run across just over 50 records in each table im looking at. This is fine at the moment but by the end of the year I will bein the 1000's so I figure the execution time will also increase at a similar scale?

 

[DCrake]

Stick to DAO tha this more efficient, however, what I would suggest is that you do not need to keep defining CurrentDb as Db, just use


Set Rs = CurrentDb.OpenRecordset(.....)

That way you do not need to keep closing it each time. Will save some resources and assist performance.

 

[munkee]

Thanks for the quick reply DCrake. I assume this is the standard way for returning a record count and there isn't anything overly drastic I should be changing apart from freeing up some resource by cutting out a few of the set statements.

I expect due to the shear volume of the counts I shouldnt be expecting anything lightning fast (although 4-5 seconds isn't really slow compared to the waiting times I have seen on some databases)

 

[DCrake]

One thing to note though when using Recordets you need to use the Rs.MoveLast to retreive the Rs.RecordCount correctly

Also you could try setting up agregate queries that do the Counts and simply do a DLookup() for the total in the queries. Because they are already syntactically checked by Access it does not need to validate the sql prior to running it.

 

[vbaInet]

Are all (or most) of the Counts coming from this? -->

FROM tblIncidentType INNER JOIN (tbldept INNER JOIN tblhselog ON tbldept.DeptID = tblhselog.Department) ON tblIncidentType.IncidentTypeID = tblhselog.Incident_type"

With respect to setting a db object everytime, you should do set it once in the Open event of the form and set it Nothing in the Close event. That way you can use that one db object throughout the session of the form.