Breaking away from mutiple DCount

[munkee]

Hey all,

Right I have just split my database and uploaded in to my network. Everything is running quicker than I expected however on my admin form I have multiple DCounts.


There are 5 statuses a record can be in:

Initial notification
Awaiting acceptance
Action being taken
Awaiting closure
Closed

I use the DCount function to look up how many records are within each status. For example:

=DCount("*","qryAdminRecordOverview","NCStatus='Initial Notification'")

This produces the number fine except it takes some time to actually load when I open up my form.

So I basically have two questions here.

Firstly, when I am saying it takes a while to load I am talking around sub 10 seconds for the values to load. I know this is not long but I currently only have around 30 records for the DCounts to look through and I am unsure whether this performance will drastically decrease? I am expecting to have 1000's of records over the course of a year but if it is simply that Dcount just produces a general overhead then I can live with the speed it is at the moment.

Secondly, How can I break away from using DCount. I am thinking a Select Count(*) type of statement in a query, but I am unsure whether this would be any quicker in the long run as I assume I will then have to assing it to my textboxes via VBA. I will then requery whenever I make any changes (I have a view button beside the text boxes to then load the x amount of records of that status below which I can then change the status therefore refresh to update the new counts.) I also have the options of whether I do one big query with each of the 5 status counts in or seperate down in to 5 individuals.

Any input is welcomed.

Thanks,

Chris

[ADezii]

First and foremost, try Indexing the [NCStatus] Field (Duplicates Allowed).

[munkee]

I had the [NCStatus] indexed already on my main table. I added one to the actual table tblNCStatus and no markable improvement.

I have given the following a try just on a button click event and it returns very fast:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intResult As Integer
Dim strSQL As String
 
Set db = CurrentDb
 
strSQL = "SELECT Count(*) AS [Initial Notification] FROM tbllog WHERE tbllog.NCStatus=1"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
 
intResult = rs("Initial Notification")
Me.Text72 = intResult
rs.Close
db.Close

However, after your indexing comment I am kind of worrying this is not just down to my poor database design. I have given normalisation a read in my huge access book and it is hard going to understand sometimes, although I think my tables are set out a lot better than most would have. I am unsure as to where to index most the time so I have just gone with what the database performance analyser has suggested.

[ADezii]

If the above code works well for you, then you can simply:

1. Create a Public Function.
2. Pass it the appropriate NCStatus Code ( 1 to 5).
3. Set the Control Sources of the Text Boxes equal to the Function, given the appropriate Arguments.
   

   Public Function fReturnNotificationCount(bytStatus As Byte) As Integer
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim strSQL As String
    
   Set db = CurrentDb
    
   strSQL = "SELECT Count(*) AS [Initial Notification] FROM tbllog WHERE tbllog.NCStatus=" & bytStatus
   Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
   fReturnNotificationCount = rs("Initial Notification")
    
   rs.Close
   db.Close
   Set rs = Nothing
   End Function

4. Control Sources for the 5 Text Boxes:
   

   =fReturnNotificationCount(1)
   =fReturnNotificationCount(2)
   =fReturnNotificationCount(3)
   =fReturnNotificationCount(4)
   =fReturnNotificationCount(5)

[Steven Kogan]

Is there a noticeable speed difference if the click event vba uses a simple dcount function? I'd expect a dcount function to run just as fast or faster.

The code called from the button click runs the select statement only once, regardless of how many records are in your form's recordset (so it is fast), while if you have the dcount function in a text box control source it will run once per record (so it is slower).

If you are always displaying just one record then the code can go in the form's current event. Alternately, if you are displaying the dcount information in the header or footer section instead of the detail section then the vba code on the current event could be used and would be faster (since it is run for only the current record).

[munkee]

To explain further to my setup.

I have an unbound form as an admin overview because I want to be able to oversee how many records are within each status and monitor as they move through my process.


As I stated before I use the dcount in a number of text boxes to count how many records are in each status. Beside these I have a View button. This button loads a continuous sub form below the textboxes with the records of that status.

The selection/loading of the records in to the subform is very fast, it is purely the dcount function which is slow.I assume this is because as has been said the dcount function is firing for each of the records.I think I may switch to the function that has been provided and use that to call the number of records within the select query.

However as I stated before, this is now more worrying me that my database design is bad over the top of everything else as I have read a Dcount should not run THAT slow and when it does it is usually down to poor design. With that being said, this Dcount has only slowed down since splitting and putting my database across the network. During normal usage as a complete file and sitting on my hard drive the dcount runs instant.

[Steven Kogan]

If the unbound form contains the dcount function then it would be running just once.

Let me know if using the vba function goes noticeably faster than the dcount function. That would be interesting.

[munkee]

After putting the function in on the same page in seperate boxes to the dcount they both run exactly the same speed. When I remove all of the dcount boxes the function seems to run quicker.

I can requery these boxes and they load pretty fast also. An improvement over the dcount function.