Just a general question that I can't find an answer to on Google...
If I need to display various unrelated (in database terms) details from multiple tables on a single unbound form is it more efficient to make queries or to use recordsets?
I'm making a form that includes a summary of various actions taken, things like total time spent, most recent contact attempt, number of other actions for the same person.
I'd perhaps end up with 4 or 5 different queries to refer to. Is one way better than the other, or is it just a matter of preference?
I've used DCount on a form before to display a single piece of data and I noticed that it was really slow. It took maybe 3 or 4 seconds to populate the box after the form loaded... Is that typical or did I maybe use it wrong?
I'll take a look at the options available when I'm back in work. Thanks for the pointer.
So from the answers I assume there is no right way or wrong way, just whatever is tidier/easier for the particular case?
Well, it basically runs a query to get the data, so would a query take that long? If it's just a display issue, you could set the values in code in the load event of the form. I'd use a recordset for multiple values from the same source, probably just a domain function for a single value.
If I need to display various unrelated (in database terms) details from multiple tables on a single unbound form is it more efficient to make queries or to use recordsets?
1. How many tables are involved?
2. How many records does the query return?
3. How many records per query need to be displayed?
4. How many fields from each query need to be displayed?
I would just add in relation to your headline Query vs Recordset for returning values
Recordsets are better suited for processing, ie performing actions while navigating through a recordset.
Queries or SQL as suggested above will work fine to display criteria based record collections and return simple totals/counts etc
I suspect I was using the DLOOKUP wrong... I was using it to pull a version number for the application from a field and display it at the bottom of the main form. It was the only detail in the table and was ony used for that, but took a good 3-4 seconds to populate. I switched to using a recordset in VBA if I remember rightly.
As far as my original query goes I'd like to make a summary page that would show a lot of info about the database. Some of the areas would be
Total number of records in tbl_main
Total number of records in tbl_main that have each of 3 tick boxes checked (so 3 values)
Total number of records in tlk_contactEvents where outcomeFK is 2 - 5 and the total of contactDuration for those
Total number of records in tlk_contactEvents where outcomeFK is 1 and the total of contactDuration for those
Total number of entries in tlk_located where appliedTime is null and not null
I have no problem with how to get these details, I could use a query for each or a recordset in VBA and recordset.count, or I could use domain aggregates as suggested earlier. What I was wondering is if it's best practice or more efficient to do it a certain way.
If I were to make a query for each of the items I need I could easily end up with 10-15 queries that are only used for 1 value.
If I were to just do it all in VBA using recordsets I wouldn't have so many queries, but it would be a bit more involved to set things up.
I don't know about DCount etc but I imagine that's pretty simple.
So I was just wondering if there's any big advantage to doing it either way? Or if there's one method that I should outright stay away from?
I would just add in relation to your headline Query vs Recordset for returning values
Recordsets are better suited for processing, ie performing actions while navigating through a recordset.
Queries or SQL as suggested above will work fine to display criteria based record collections and return simple totals/counts etc
That's kind of how I was thinking of recordsets. Pretty much everything I've done in Access to date has been through unbound forms as I found it easier to get things done that way at first. So I've got pretty comfortable with using recordsets. The trouble is I think I'm missing out on some of the built-in features that can make my life easier, so I'm making a conscious effort to use them more.
That's why I'm asking the question here... Am I really missing out, or is it simply a matter of preference?
"As far as my original query goes I'd like to make a summary page that would show a lot of info about the database"
Depends whether you intend to keep a history of these values, if you do then a simple table with a field for each value would do the job, include a date/time stamp field. Each time you intend to refresh/add new record (daily/weekly/monthly), you can append a new record and update each field using desired method. This table's values would then feed your summary page with the latest values
The number of queries you'll end up having shouldn't matter if it ends up being quicker. So don't get hung up on number of objects. Queries were built to handle data manipulation.
I have no problem with how to get these details, I could use a query for each or a recordset in VBA and recordset.count, or I could use domain aggregates as suggested earlier.
If I were to just do it all in VBA using recordsets I wouldn't have so many queries, but it would be a bit more involved to set things up.
In relation to recordsets and as already been mentioned, it depends on how you implement the recordset. You don't need a Recordset.RecordCount to count the number of records in a table or query. Galaxiom also mentioned that recordsets (especially ADO) are versatile.
Based on what has been mentioned your main question should be the best approach to retrieve summarised because the summarising of data is not the issue and a query and a recordset cannot be compared - they do different things and a recordset.
So the issue here is what is the best method for retrieving the aggregated value.
A query is best for performing the calculation (as you will see below) and a recordset is best for retrieving the value. If you were to compare a recordset to a subform, my preference would be a subform bound to the query.
Here's a quick way of summarising data and returning the value using a recordset (aircode):
Code:
dim recCount as long
recCount = currentdb.openrecordset("select count([Field]) from tablename", dbopenforwardonly, dbreadonly).fields(0)
Note here I'm not using a saved query (which would have been a better option), It's merely "running the SQL in the background" and returning the aggregated value (one value), so in essence the options dbOpenForwardOnly and dbReadOnly don't have much of a bearing in this instance but it helps a bit.
You would have noticed the highlighted bullet points above, here's how you would get their values in one recordset:
Code:
dim rs as dao.recordset
dim countAll as long
dim countChk3 as long
set rs = db.openrecordset(select count(*), count(iif(chk1 And chk2 And chk3, 1, Null)) from tablename", dbopensnapshot, dbreadonly)
countAll = rs.Fields(0)
countChk3 = rs.Fields(1)
To summarise, the lessons learned are:
1. Group similar calculations in one query/SQL
2. Perform the aggregation in a query (whether in a query object or through code)
3. Retrieve the value using a recordset or a subform.
NB: Note I've used the words SQL and query interchangeably.
Thanks vbaInet, very helpful as always. I'll take a look at those 2 methods. I started doing the summary page yesterday using DCount and it's working fine so far.
Thanks to David as well, I won't need a record of these details, I just need a view of progress we've made on the work being done through this DB.
This is part of the code that made up my summary page. There were another 9 controls to populate in the same manner. It worked fine in testing, but when the application was deployed we found the summary page took 45-60 seconds to populate, and the application hung while it was executing. Not good.
I set up queries to summarise the data and used a recordset to pull those totals and populate the controls. This runs much quicker, a split second for this sample in isolation, compared to 11 seconds for the above.
New code:
Code:
Set rst = CurrentDb.OpenRecordset("SELECT numEntries, totalAmount FROM qry_summaryTotals", dbOpenSnapshot, dbReadOnly)
Me.txt_totalEntries = rst.Fields(0)
Me.txt_totalEntries2 = rst.Fields(1)
Set rst = CurrentDb.OpenRecordset("SELECT numEntries, totalAmount FROM qry_summaryUnworked", dbOpenSnapshot, dbReadOnly)
Me.txt_unworkedEntries = rst.Fields(0)
Me.txt_unworkedEntries2 = rst.Fields(1)
