If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
when to use a recordset?
Perhaps a very simple question:
When and why does one declare a record set? thanks. |
#2
|
|||
|
|||
when to use a recordset?
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. -- Duane Hookom Microsoft Access MVP "patti" wrote: Perhaps a very simple question: When and why does one declare a record set? thanks. |
#3
|
|||
|
|||
when to use a recordset?
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. "Duane Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "patti" wrote: Perhaps a very simple question: When and why does one declare a record set? thanks. |
#4
|
|||
|
|||
when to use a recordset?
On Wed, 30 Sep 2009 16:17:01 -0700, patti
wrote: 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 |
#5
|
|||
|
|||
when to use a recordset?
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. -- Duane Hookom Microsoft Access MVP "Armen Stein" wrote: On Wed, 30 Sep 2009 16:17:01 -0700, patti wrote: 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 |
#6
|
|||
|
|||
when to use a recordset?
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 "Armen Stein" wrote: On Wed, 30 Sep 2009 16:17:01 -0700, patti wrote: 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 |
#7
|
|||
|
|||
when to use a recordset?
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. -- Duane Hookom Microsoft Access MVP "patti" wrote: 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 "Armen Stein" wrote: On Wed, 30 Sep 2009 16:17:01 -0700, patti wrote: 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 |
#8
|
|||
|
|||
when to use a recordset?
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" wrote: 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. -- Duane Hookom Microsoft Access MVP "patti" wrote: 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 "Armen Stein" wrote: On Wed, 30 Sep 2009 16:17:01 -0700, patti wrote: 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 |
#9
|
|||
|
|||
when to use a recordset?
There is a sample Concatenate function at
http://www.rogersaccesslibrary.com/f...sts.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. -- Duane Hookom Microsoft Access MVP "patti" wrote: 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" wrote: 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. -- Duane Hookom Microsoft Access MVP "patti" wrote: 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 "Armen Stein" wrote: On Wed, 30 Sep 2009 16:17:01 -0700, patti wrote: 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 |
#10
|
|||
|
|||
when to use a recordset?
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 "Duane Hookom" wrote: There is a sample Concatenate function at http://www.rogersaccesslibrary.com/f...sts.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. -- Duane Hookom Microsoft Access MVP "patti" wrote: 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" wrote: 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. -- Duane Hookom Microsoft Access MVP "patti" wrote: 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 "Armen Stein" wrote: On Wed, 30 Sep 2009 16:17:01 -0700, patti wrote: 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 |
Thread Tools | |
Display Modes | |
|
|