when to use a recordset?

[patti]

Perhaps a very simple question:
When and why does one declare a record set?

thanks.

 

[Duane Hookom]

Before you use it so that it can be used.

I use them when I need to step through some records to perform somewhat
complex procedures or calculations that are difficult in plain SQL.

 

[patti]

Thanks. I have never declared a recordset and was not sure if it would speed
things up.

We run a report every morning that pulls a lot of patient info for people
that are currently in the hospital. This report runs thru about 5500 names to
spit out 25. Would there be any benefit to using a record set here?

Perhaps you could give me some examples of when you declare them.

 

[Armen Stein]

Thanks. I have never declared a recordset and was not sure if it would speed
things up.

We run a report every morning that pulls a lot of patient info for people
that are currently in the hospital. This report runs thru about 5500 names to
spit out 25. Would there be any benefit to using a record set here?

Perhaps you could give me some examples of when you declare them.

Hi Patti,

Recordsets aren't for speeding up existing functionality. They're for
doing something that is difficult or impossible to do with queries
alone.

For your performance issues, I suggest that you look at the queries
that your report is based on. When you say "run through about 5500
names", what do you mean? Does the report use selection criteria to
select just the 25 records? How fast does the query run by itself?
Have you tried adding indexes to the tables on fields that are used
for sorting and selecting the records?

In other words, recordsets aren't your answer here. But there might
be other things you can do to speed up your report. Maybe we can help
if you post more details.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Duane Hookom]

Armen is correct. Queries are much more efficient when filtering records. If
your report is running slow, you might want to review indexes in your tables.
Also, Page of Pages can really slow down a report formatting.

 

[patti]

The report is based on cascading queries. The query behind the report runs at
about the same speed as the report itself.

Thank you for your help.

patti

 

[Duane Hookom]

If you have slow performing queries, you might want to post your SQL views in
the Queries news group. I would help if you also included the indexed fields.

 

[patti]

Thanks Duane. I will do that when i have time.
These messages were a foray into learning about record sets. Could you give
me a few examples of cases when you utilize them, please?

patti

 

[Duane Hookom]

There is a sample Concatenate function at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16. This would
be a little difficult to do with pure SQL.

I have an application that takes every combination of products that can be
produced on a mfg line at a factory and performs some calculations to
determine how many minutes to schedule for changeovers. This requires several
recordsets since the calculations are fairly complex. The code can also be
commented which isn't possible in Access queries.

 

[patti]

I will look over my queries and when i get time post them into the
appropriate section. Would like opinions on streamlining if possible.
Thanks Duane.

patti