Dynamically referencing a recordset?

[Guest]

I have a database that is used to track production on several thousand
records in a second database. The production database is all I am dealing
with here so really you dont have to worry about the second. Anyhow, every
month each office has to supply metrics on how much production was conducted
that month, whether offices are behind at or ahead of schedule and so forth.
Creating these slides (for powerpoint) is time consuming and is prone to lots
of error.

To remedy this, I am creating this database that when the Production Slide
creation procedure is executed it queries each of three tables that hold the
relevant data and inputs a record count of the records that match the query.

When this first started it worked pretty good. I only have a few hundred
records and it took about 2 minutes to do all of the queries (I worked it out
and it is about 1400 queries [12 months x 3 record types x 8 regions x 4 or 5
production types]). After I added the rest of the offices records to the
tables to be queried, it takes about 45 minutes to run the whole procedure.

To remedy this, I am reworking the procedure so that it creates 36 record
sets (12 months x 3 record types). The problem is this:

Is there a way to refer to the recordsets via string variables used in For
Loops? I know that there is not a recordset collection, but I will allude to
one for the purposes of this example...
i.e.
Dim MonthArray(11) as String 'lets assume I fill this array with month names
Dim RecTypeArray(2) as String 'lets assume I fill this array with record
Type names
Dim monthCounter as Integer
Dim RecTypeCounter as Integer

For MonthCounter = 0 to 12
For RecTypeCounter = 0 to 2
RecordSets(MonthArray(MonthCounter) &
RecTypeArray(RecTypeCounter).Open conn, Lock Type, etc.
Next RecTypeCounter
Next MonthCounter


I can handle opening each recordset "manually", but later on in a different
process, I will need the proper recordset queried based on which month and
which record type is being currently measured. Any thoughts?

And please let me know if I made this too confusing, I sometimes have a
difficult time putting my thoughts into words.

Cory

 

[Guest]

I sometimes have a difficult time putting my thoughts into words.

Don't worry about it. Often, my words have no thoughts behind them

I think you are making this harder than it is. What I would suggest is a
naming convention that would be able to identify the query name for each,
then create the name of the recordset to open. You should only have one
recordset open at a time.
There would be no need to have an array of month names, you can use the For
Next Index and the MonthName function to determine the month. You can also
use the Choose function to determine the record type:

For MonthCounter = 1 to 12
strMonthName = MonthName(MonthCounter)
For RecTypeCounter = 1 to 3
strRecType = Choose( RecTypeCounter, "One", "Two", "Three")
strRecordSetName = "qry" & strMonthName & strRecType
"**** Here is where you open the recordset and do your stuff ****
Next RecTypeCounter
Next MonthCounter



--
Dave Hargis, Microsoft Access MVP

I have a database that is used to track production on several thousand
records in a second database. The production database is all I am dealing
with here so really you dont have to worry about the second. Anyhow, every
month each office has to supply metrics on how much production was conducted
that month, whether offices are behind at or ahead of schedule and so forth.
Creating these slides (for powerpoint) is time consuming and is prone to lots
of error.

To remedy this, I am creating this database that when the Production Slide
creation procedure is executed it queries each of three tables that hold the
relevant data and inputs a record count of the records that match the query.

When this first started it worked pretty good. I only have a few hundred
records and it took about 2 minutes to do all of the queries (I worked it out
and it is about 1400 queries [12 months x 3 record types x 8 regions x 4 or 5
production types]). After I added the rest of the offices records to the
tables to be queried, it takes about 45 minutes to run the whole procedure.

To remedy this, I am reworking the procedure so that it creates 36 record
sets (12 months x 3 record types). The problem is this:

Is there a way to refer to the recordsets via string variables used in For
Loops? I know that there is not a recordset collection, but I will allude to
one for the purposes of this example...
i.e.
Dim MonthArray(11) as String 'lets assume I fill this array with month names
Dim RecTypeArray(2) as String 'lets assume I fill this array with record
Type names
Dim monthCounter as Integer
Dim RecTypeCounter as Integer

For MonthCounter = 0 to 12
For RecTypeCounter = 0 to 2
RecordSets(MonthArray(MonthCounter) &
RecTypeArray(RecTypeCounter).Open conn, Lock Type, etc.
Next RecTypeCounter
Next MonthCounter


I can handle opening each recordset "manually", but later on in a different
process, I will need the proper recordset queried based on which month and
which record type is being currently measured. Any thoughts?

And please let me know if I made this too confusing, I sometimes have a
difficult time putting my thoughts into words.

Cory

 

[Guest]

I tried this with a single recordset originally, updating the SQL query
through multiple variables. That operation took 45+ minutes the last time I
ran it, and really I thought that it would only take a minute at most. Is
there something that I am doing wrong? Running 1400 queries on a
(relatively) large table of records proved to be too long. Running 1400
queries on 36 smaller tables *should* in theory improve the run time since it
has to query fewer records. Should I just not even bother creating record
sets at all and instead query the table? in the original operation I was
opening and closing the record set with each query.

Also, specifically how do you use the strRecSetName when opening the record?

Cory

I sometimes have a difficult time putting my thoughts into words.

Don't worry about it. Often, my words have no thoughts behind them

I think you are making this harder than it is. What I would suggest is a
naming convention that would be able to identify the query name for each,
then create the name of the recordset to open. You should only have one
recordset open at a time.
There would be no need to have an array of month names, you can use the For
Next Index and the MonthName function to determine the month. You can also
use the Choose function to determine the record type:

For MonthCounter = 1 to 12
strMonthName = MonthName(MonthCounter)
For RecTypeCounter = 1 to 3
strRecType = Choose( RecTypeCounter, "One", "Two", "Three")
strRecordSetName = "qry" & strMonthName & strRecType
"**** Here is where you open the recordset and do your stuff ****
Next RecTypeCounter
Next MonthCounter



--
Dave Hargis, Microsoft Access MVP

I have a database that is used to track production on several thousand
records in a second database. The production database is all I am dealing
with here so really you dont have to worry about the second. Anyhow, every
month each office has to supply metrics on how much production was conducted
that month, whether offices are behind at or ahead of schedule and so forth.
Creating these slides (for powerpoint) is time consuming and is prone to lots
of error.

To remedy this, I am creating this database that when the Production Slide
creation procedure is executed it queries each of three tables that hold the
relevant data and inputs a record count of the records that match the query.

When this first started it worked pretty good. I only have a few hundred
records and it took about 2 minutes to do all of the queries (I worked it out
and it is about 1400 queries [12 months x 3 record types x 8 regions x 4 or 5
production types]). After I added the rest of the offices records to the
tables to be queried, it takes about 45 minutes to run the whole procedure.

To remedy this, I am reworking the procedure so that it creates 36 record
sets (12 months x 3 record types). The problem is this:

Is there a way to refer to the recordsets via string variables used in For
Loops? I know that there is not a recordset collection, but I will allude to
one for the purposes of this example...
i.e.
Dim MonthArray(11) as String 'lets assume I fill this array with month names
Dim RecTypeArray(2) as String 'lets assume I fill this array with record
Type names
Dim monthCounter as Integer
Dim RecTypeCounter as Integer

For MonthCounter = 0 to 12
For RecTypeCounter = 0 to 2
RecordSets(MonthArray(MonthCounter) &
RecTypeArray(RecTypeCounter).Open conn, Lock Type, etc.
Next RecTypeCounter
Next MonthCounter


I can handle opening each recordset "manually", but later on in a different
process, I will need the proper recordset queried based on which month and
which record type is being currently measured. Any thoughts?

And please let me know if I made this too confusing, I sometimes have a
difficult time putting my thoughts into words.

Cory

 

[Guest]

For the specific:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset(strRecSetName,dbOpenDynaset)

How large is your table? (# of Rows, # columns?)
Are there any Domain Aggragate Functions in your queries?

--
Dave Hargis, Microsoft Access MVP

I tried this with a single recordset originally, updating the SQL query
through multiple variables. That operation took 45+ minutes the last time I
ran it, and really I thought that it would only take a minute at most. Is
there something that I am doing wrong? Running 1400 queries on a
(relatively) large table of records proved to be too long. Running 1400
queries on 36 smaller tables *should* in theory improve the run time since it
has to query fewer records. Should I just not even bother creating record
sets at all and instead query the table? in the original operation I was
opening and closing the record set with each query.

Also, specifically how do you use the strRecSetName when opening the record?

Cory

I sometimes have a difficult time putting my thoughts into words.

Don't worry about it. Often, my words have no thoughts behind them

I think you are making this harder than it is. What I would suggest is a
naming convention that would be able to identify the query name for each,
then create the name of the recordset to open. You should only have one
recordset open at a time.
There would be no need to have an array of month names, you can use the For
Next Index and the MonthName function to determine the month. You can also
use the Choose function to determine the record type:

For MonthCounter = 1 to 12
strMonthName = MonthName(MonthCounter)
For RecTypeCounter = 1 to 3
strRecType = Choose( RecTypeCounter, "One", "Two", "Three")
strRecordSetName = "qry" & strMonthName & strRecType
"**** Here is where you open the recordset and do your stuff ****
Next RecTypeCounter
Next MonthCounter



--
Dave Hargis, Microsoft Access MVP

I have a database that is used to track production on several thousand
records in a second database. The production database is all I am dealing
with here so really you dont have to worry about the second. Anyhow, every
month each office has to supply metrics on how much production was conducted
that month, whether offices are behind at or ahead of schedule and so forth.
Creating these slides (for powerpoint) is time consuming and is prone to lots
of error.

To remedy this, I am creating this database that when the Production Slide
creation procedure is executed it queries each of three tables that hold the
relevant data and inputs a record count of the records that match the query.

When this first started it worked pretty good. I only have a few hundred
records and it took about 2 minutes to do all of the queries (I worked it out
and it is about 1400 queries [12 months x 3 record types x 8 regions x 4 or 5
production types]). After I added the rest of the offices records to the
tables to be queried, it takes about 45 minutes to run the whole procedure.

To remedy this, I am reworking the procedure so that it creates 36 record
sets (12 months x 3 record types). The problem is this:

Is there a way to refer to the recordsets via string variables used in For
Loops? I know that there is not a recordset collection, but I will allude to
one for the purposes of this example...
i.e.
Dim MonthArray(11) as String 'lets assume I fill this array with month names
Dim RecTypeArray(2) as String 'lets assume I fill this array with record
Type names
Dim monthCounter as Integer
Dim RecTypeCounter as Integer

For MonthCounter = 0 to 12
For RecTypeCounter = 0 to 2
RecordSets(MonthArray(MonthCounter) &
RecTypeArray(RecTypeCounter).Open conn, Lock Type, etc.
Next RecTypeCounter
Next MonthCounter


I can handle opening each recordset "manually", but later on in a different
process, I will need the proper recordset queried based on which month and
which record type is being currently measured. Any thoughts?

And please let me know if I made this too confusing, I sometimes have a
difficult time putting my thoughts into words.

Cory

 

[Guest]

There are three tables that will end up being probably 6000 records with say
11 fields. 480 queries to be run on each table (with a grand total of 1440)
to get record counts for criteria (which will never change so it can be
stored in teh procedure in an array). Is this inherantly going to be a time
consuming operation (to perform, not to write) or have I been going about it
in a way that MAKES it time consuming?

Cory

For the specific:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset(strRecSetName,dbOpenDynaset)

How large is your table? (# of Rows, # columns?)
Are there any Domain Aggragate Functions in your queries?

--
Dave Hargis, Microsoft Access MVP

I tried this with a single recordset originally, updating the SQL query
through multiple variables. That operation took 45+ minutes the last time I
ran it, and really I thought that it would only take a minute at most. Is
there something that I am doing wrong? Running 1400 queries on a
(relatively) large table of records proved to be too long. Running 1400
queries on 36 smaller tables *should* in theory improve the run time since it
has to query fewer records. Should I just not even bother creating record
sets at all and instead query the table? in the original operation I was
opening and closing the record set with each query.

Also, specifically how do you use the strRecSetName when opening the record?

Cory

I sometimes have a difficult time putting my thoughts into words.

Don't worry about it. Often, my words have no thoughts behind them

I think you are making this harder than it is. What I would suggest is a
naming convention that would be able to identify the query name for each,
then create the name of the recordset to open. You should only have one
recordset open at a time.
There would be no need to have an array of month names, you can use the For
Next Index and the MonthName function to determine the month. You can also
use the Choose function to determine the record type:

For MonthCounter = 1 to 12
strMonthName = MonthName(MonthCounter)
For RecTypeCounter = 1 to 3
strRecType = Choose( RecTypeCounter, "One", "Two", "Three")
strRecordSetName = "qry" & strMonthName & strRecType
"**** Here is where you open the recordset and do your stuff ****
Next RecTypeCounter
Next MonthCounter



--
Dave Hargis, Microsoft Access MVP


:

I have a database that is used to track production on several thousand
records in a second database. The production database is all I am dealing
with here so really you dont have to worry about the second. Anyhow, every
month each office has to supply metrics on how much production was conducted
that month, whether offices are behind at or ahead of schedule and so forth.
Creating these slides (for powerpoint) is time consuming and is prone to lots
of error.

To remedy this, I am creating this database that when the Production Slide
creation procedure is executed it queries each of three tables that hold the
relevant data and inputs a record count of the records that match the query.

When this first started it worked pretty good. I only have a few hundred
records and it took about 2 minutes to do all of the queries (I worked it out
and it is about 1400 queries [12 months x 3 record types x 8 regions x 4 or 5
production types]). After I added the rest of the offices records to the
tables to be queried, it takes about 45 minutes to run the whole procedure.

To remedy this, I am reworking the procedure so that it creates 36 record
sets (12 months x 3 record types). The problem is this:

Is there a way to refer to the recordsets via string variables used in For
Loops? I know that there is not a recordset collection, but I will allude to
one for the purposes of this example...
i.e.
Dim MonthArray(11) as String 'lets assume I fill this array with month names
Dim RecTypeArray(2) as String 'lets assume I fill this array with record
Type names
Dim monthCounter as Integer
Dim RecTypeCounter as Integer

For MonthCounter = 0 to 12
For RecTypeCounter = 0 to 2
RecordSets(MonthArray(MonthCounter) &
RecTypeArray(RecTypeCounter).Open conn, Lock Type, etc.
Next RecTypeCounter
Next MonthCounter


I can handle opening each recordset "manually", but later on in a different
process, I will need the proper recordset queried based on which month and
which record type is being currently measured. Any thoughts?

And please let me know if I made this too confusing, I sometimes have a
difficult time putting my thoughts into words.

Cory

 

[Guest]

6000 records with 11 fields is really a pretty small table. Even if each of
the 3 is that size, it is still small.
480 queries times 3 tables or 1440 queries is a lot. I don't think I have
ever seen an Access application with over 600 queries.
My quess is you are making it harder than it needs to be. Can you give an
overview of what all these queries do?
--
Dave Hargis, Microsoft Access MVP

There are three tables that will end up being probably 6000 records with say
11 fields. 480 queries to be run on each table (with a grand total of 1440)
to get record counts for criteria (which will never change so it can be
stored in teh procedure in an array). Is this inherantly going to be a time
consuming operation (to perform, not to write) or have I been going about it
in a way that MAKES it time consuming?

Cory

For the specific:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset(strRecSetName,dbOpenDynaset)

How large is your table? (# of Rows, # columns?)
Are there any Domain Aggragate Functions in your queries?

--
Dave Hargis, Microsoft Access MVP

I tried this with a single recordset originally, updating the SQL query
through multiple variables. That operation took 45+ minutes the last time I
ran it, and really I thought that it would only take a minute at most. Is
there something that I am doing wrong? Running 1400 queries on a
(relatively) large table of records proved to be too long. Running 1400
queries on 36 smaller tables *should* in theory improve the run time since it
has to query fewer records. Should I just not even bother creating record
sets at all and instead query the table? in the original operation I was
opening and closing the record set with each query.

Also, specifically how do you use the strRecSetName when opening the record?

Cory

:

I sometimes have a difficult time putting my thoughts into words.

Don't worry about it. Often, my words have no thoughts behind them

I think you are making this harder than it is. What I would suggest is a
naming convention that would be able to identify the query name for each,
then create the name of the recordset to open. You should only have one
recordset open at a time.
There would be no need to have an array of month names, you can use the For
Next Index and the MonthName function to determine the month. You can also
use the Choose function to determine the record type:

For MonthCounter = 1 to 12
strMonthName = MonthName(MonthCounter)
For RecTypeCounter = 1 to 3
strRecType = Choose( RecTypeCounter, "One", "Two", "Three")
strRecordSetName = "qry" & strMonthName & strRecType
"**** Here is where you open the recordset and do your stuff ****
Next RecTypeCounter
Next MonthCounter



--
Dave Hargis, Microsoft Access MVP


:

I have a database that is used to track production on several thousand
records in a second database. The production database is all I am dealing
with here so really you dont have to worry about the second. Anyhow, every
month each office has to supply metrics on how much production was conducted
that month, whether offices are behind at or ahead of schedule and so forth.
Creating these slides (for powerpoint) is time consuming and is prone to lots
of error.

To remedy this, I am creating this database that when the Production Slide
creation procedure is executed it queries each of three tables that hold the
relevant data and inputs a record count of the records that match the query.

When this first started it worked pretty good. I only have a few hundred
records and it took about 2 minutes to do all of the queries (I worked it out
and it is about 1400 queries [12 months x 3 record types x 8 regions x 4 or 5
production types]). After I added the rest of the offices records to the
tables to be queried, it takes about 45 minutes to run the whole procedure.

To remedy this, I am reworking the procedure so that it creates 36 record
sets (12 months x 3 record types). The problem is this:

Is there a way to refer to the recordsets via string variables used in For
Loops? I know that there is not a recordset collection, but I will allude to
one for the purposes of this example...
i.e.
Dim MonthArray(11) as String 'lets assume I fill this array with month names
Dim RecTypeArray(2) as String 'lets assume I fill this array with record
Type names
Dim monthCounter as Integer
Dim RecTypeCounter as Integer

For MonthCounter = 0 to 12
For RecTypeCounter = 0 to 2
RecordSets(MonthArray(MonthCounter) &
RecTypeArray(RecTypeCounter).Open conn, Lock Type, etc.
Next RecTypeCounter
Next MonthCounter


I can handle opening each recordset "manually", but later on in a different
process, I will need the proper recordset queried based on which month and
which record type is being currently measured. Any thoughts?

And please let me know if I made this too confusing, I sometimes have a
difficult time putting my thoughts into words.

Cory

 

[Guest]

Certainly,

This is a database that tracks the production on records in another
database. We update these records every year on a constant basis. There are
three categories of records, each month a report is created by each division
outlining how many records where planned for update that month, how many
where updated in normal production, how many were created, how many where
upated outside the normal production timeline (revisit), and the total number
of records updated that month. Occasionally the information changes (such as
the production plan) so previous months need to be recomputed. These records
that we produce on cover 8 regions and the report has all of the metrics
broken up by region, so 8 Regions X 12 Months X 5 Proudtion types X 3 Record
Types = 1440 queries.

I have started working on a different query module that will have the region
managers compile thier information themselves. This will cut down the
"percieved" time a bit. I am also toying with the recordset.Source and
..requery property and method. I imagine part of the time that was spent was
from creating the recordset object over and over again and setting it to
nothing at the end of the loop. We shall see what happens.

Cory

6000 records with 11 fields is really a pretty small table. Even if each of
the 3 is that size, it is still small.
480 queries times 3 tables or 1440 queries is a lot. I don't think I have
ever seen an Access application with over 600 queries.
My quess is you are making it harder than it needs to be. Can you give an
overview of what all these queries do?
--
Dave Hargis, Microsoft Access MVP

There are three tables that will end up being probably 6000 records with say
11 fields. 480 queries to be run on each table (with a grand total of 1440)
to get record counts for criteria (which will never change so it can be
stored in teh procedure in an array). Is this inherantly going to be a time
consuming operation (to perform, not to write) or have I been going about it
in a way that MAKES it time consuming?

Cory

For the specific:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset(strRecSetName,dbOpenDynaset)

How large is your table? (# of Rows, # columns?)
Are there any Domain Aggragate Functions in your queries?

--
Dave Hargis, Microsoft Access MVP


:

I tried this with a single recordset originally, updating the SQL query
through multiple variables. That operation took 45+ minutes the last time I
ran it, and really I thought that it would only take a minute at most. Is
there something that I am doing wrong? Running 1400 queries on a
(relatively) large table of records proved to be too long. Running 1400
queries on 36 smaller tables *should* in theory improve the run time since it
has to query fewer records. Should I just not even bother creating record
sets at all and instead query the table? in the original operation I was
opening and closing the record set with each query.

Also, specifically how do you use the strRecSetName when opening the record?

Cory

:

I sometimes have a difficult time putting my thoughts into words.

Don't worry about it. Often, my words have no thoughts behind them

I think you are making this harder than it is. What I would suggest is a
naming convention that would be able to identify the query name for each,
then create the name of the recordset to open. You should only have one
recordset open at a time.
There would be no need to have an array of month names, you can use the For
Next Index and the MonthName function to determine the month. You can also
use the Choose function to determine the record type:

For MonthCounter = 1 to 12
strMonthName = MonthName(MonthCounter)
For RecTypeCounter = 1 to 3
strRecType = Choose( RecTypeCounter, "One", "Two", "Three")
strRecordSetName = "qry" & strMonthName & strRecType
"**** Here is where you open the recordset and do your stuff ****
Next RecTypeCounter
Next MonthCounter



--
Dave Hargis, Microsoft Access MVP


:

I have a database that is used to track production on several thousand
records in a second database. The production database is all I am dealing
with here so really you dont have to worry about the second. Anyhow, every
month each office has to supply metrics on how much production was conducted
that month, whether offices are behind at or ahead of schedule and so forth.
Creating these slides (for powerpoint) is time consuming and is prone to lots
of error.

To remedy this, I am creating this database that when the Production Slide
creation procedure is executed it queries each of three tables that hold the
relevant data and inputs a record count of the records that match the query.

When this first started it worked pretty good. I only have a few hundred
records and it took about 2 minutes to do all of the queries (I worked it out
and it is about 1400 queries [12 months x 3 record types x 8 regions x 4 or 5
production types]). After I added the rest of the offices records to the
tables to be queried, it takes about 45 minutes to run the whole procedure.

To remedy this, I am reworking the procedure so that it creates 36 record
sets (12 months x 3 record types). The problem is this:

Is there a way to refer to the recordsets via string variables used in For
Loops? I know that there is not a recordset collection, but I will allude to
one for the purposes of this example...
i.e.
Dim MonthArray(11) as String 'lets assume I fill this array with month names
Dim RecTypeArray(2) as String 'lets assume I fill this array with record
Type names
Dim monthCounter as Integer
Dim RecTypeCounter as Integer

For MonthCounter = 0 to 12
For RecTypeCounter = 0 to 2
RecordSets(MonthArray(MonthCounter) &
RecTypeArray(RecTypeCounter).Open conn, Lock Type, etc.
Next RecTypeCounter
Next MonthCounter


I can handle opening each recordset "manually", but later on in a different
process, I will need the proper recordset queried based on which month and
which record type is being currently measured. Any thoughts?

And please let me know if I made this too confusing, I sometimes have a
difficult time putting my thoughts into words.

Cory

 

[Guest]

Okay, you don't really need as many queries as you think you do. For
example, you said:
8 Regions X 12 Months X 5 Proudtion types X 3 Record Types = 1440 queries.
You actually need either 1 or 3.
You could easily use a parameterized query. That is where you filter by
region, month, and productiontype for sure. If you mean by Record Type, a
different table schema, then you need 3, one for each record type. If Record
Type has a different meaning, you only need 1.

The way to do that would be to put controls on a form for the user to make
those selections, then use the Where argument of the OpenReport method.
For example:

Dim strWhere As String

strWhere = "[Region] = '" & Me.txtRegion & "' And [ProductionMonth] = "
& Month(Me.txtProdMonth) & " And [ProductionType] = " & Me.txtProdType
Docmd.OpenReport "MyReportName, , , strWhere


--
Dave Hargis, Microsoft Access MVP

Certainly,

This is a database that tracks the production on records in another
database. We update these records every year on a constant basis. There are
three categories of records, each month a report is created by each division
outlining how many records where planned for update that month, how many
where updated in normal production, how many were created, how many where
upated outside the normal production timeline (revisit), and the total number
of records updated that month. Occasionally the information changes (such as
the production plan) so previous months need to be recomputed. These records
that we produce on cover 8 regions and the report has all of the metrics
broken up by region, so 8 Regions X 12 Months X 5 Proudtion types X 3 Record
Types = 1440 queries.

I have started working on a different query module that will have the region
managers compile thier information themselves. This will cut down the
"percieved" time a bit. I am also toying with the recordset.Source and
.requery property and method. I imagine part of the time that was spent was
from creating the recordset object over and over again and setting it to
nothing at the end of the loop. We shall see what happens.

Cory

6000 records with 11 fields is really a pretty small table. Even if each of
the 3 is that size, it is still small.
480 queries times 3 tables or 1440 queries is a lot. I don't think I have
ever seen an Access application with over 600 queries.
My quess is you are making it harder than it needs to be. Can you give an
overview of what all these queries do?
--
Dave Hargis, Microsoft Access MVP

There are three tables that will end up being probably 6000 records with say
11 fields. 480 queries to be run on each table (with a grand total of 1440)
to get record counts for criteria (which will never change so it can be
stored in teh procedure in an array). Is this inherantly going to be a time
consuming operation (to perform, not to write) or have I been going about it
in a way that MAKES it time consuming?

Cory

:

For the specific:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset(strRecSetName,dbOpenDynaset)

How large is your table? (# of Rows, # columns?)
Are there any Domain Aggragate Functions in your queries?

--
Dave Hargis, Microsoft Access MVP


:

I tried this with a single recordset originally, updating the SQL query
through multiple variables. That operation took 45+ minutes the last time I
ran it, and really I thought that it would only take a minute at most. Is
there something that I am doing wrong? Running 1400 queries on a
(relatively) large table of records proved to be too long. Running 1400
queries on 36 smaller tables *should* in theory improve the run time since it
has to query fewer records. Should I just not even bother creating record
sets at all and instead query the table? in the original operation I was
opening and closing the record set with each query.

Also, specifically how do you use the strRecSetName when opening the record?

Cory

:

I sometimes have a difficult time putting my thoughts into words.

Don't worry about it. Often, my words have no thoughts behind them

I think you are making this harder than it is. What I would suggest is a
naming convention that would be able to identify the query name for each,
then create the name of the recordset to open. You should only have one
recordset open at a time.
There would be no need to have an array of month names, you can use the For
Next Index and the MonthName function to determine the month. You can also
use the Choose function to determine the record type:

For MonthCounter = 1 to 12
strMonthName = MonthName(MonthCounter)
For RecTypeCounter = 1 to 3
strRecType = Choose( RecTypeCounter, "One", "Two", "Three")
strRecordSetName = "qry" & strMonthName & strRecType
"**** Here is where you open the recordset and do your stuff ****
Next RecTypeCounter
Next MonthCounter



--
Dave Hargis, Microsoft Access MVP


:

I have a database that is used to track production on several thousand
records in a second database. The production database is all I am dealing
with here so really you dont have to worry about the second. Anyhow, every
month each office has to supply metrics on how much production was conducted
that month, whether offices are behind at or ahead of schedule and so forth.
Creating these slides (for powerpoint) is time consuming and is prone to lots
of error.

To remedy this, I am creating this database that when the Production Slide
creation procedure is executed it queries each of three tables that hold the
relevant data and inputs a record count of the records that match the query.

When this first started it worked pretty good. I only have a few hundred
records and it took about 2 minutes to do all of the queries (I worked it out
and it is about 1400 queries [12 months x 3 record types x 8 regions x 4 or 5
production types]). After I added the rest of the offices records to the
tables to be queried, it takes about 45 minutes to run the whole procedure.

To remedy this, I am reworking the procedure so that it creates 36 record
sets (12 months x 3 record types). The problem is this:

Is there a way to refer to the recordsets via string variables used in For
Loops? I know that there is not a recordset collection, but I will allude to
one for the purposes of this example...
i.e.
Dim MonthArray(11) as String 'lets assume I fill this array with month names
Dim RecTypeArray(2) as String 'lets assume I fill this array with record
Type names
Dim monthCounter as Integer
Dim RecTypeCounter as Integer

For MonthCounter = 0 to 12
For RecTypeCounter = 0 to 2
RecordSets(MonthArray(MonthCounter) &
RecTypeArray(RecTypeCounter).Open conn, Lock Type, etc.
Next RecTypeCounter
Next MonthCounter


I can handle opening each recordset "manually", but later on in a different
process, I will need the proper recordset queried based on which month and
which record type is being currently measured. Any thoughts?

And please let me know if I made this too confusing, I sometimes have a
difficult time putting my thoughts into words.

Cory

 

[Guest]

Well I need the procedure to step through all of the choises and run the
queries. The record counts are entered into a table which is accessed by
another query that sends the values for number of records produced into a
number of tables and graphs in PowerPoint. Having the user select the
parameters to use would only cause them to have to manually execute hundreds
of queries since all of the data that those queries retreived would be needed.

Okay, you don't really need as many queries as you think you do. For
example, you said:
8 Regions X 12 Months X 5 Proudtion types X 3 Record Types = 1440 queries.
You actually need either 1 or 3.
You could easily use a parameterized query. That is where you filter by
region, month, and productiontype for sure. If you mean by Record Type, a
different table schema, then you need 3, one for each record type. If Record
Type has a different meaning, you only need 1.

The way to do that would be to put controls on a form for the user to make
those selections, then use the Where argument of the OpenReport method.
For example:

Dim strWhere As String

strWhere = "[Region] = '" & Me.txtRegion & "' And [ProductionMonth] = "
& Month(Me.txtProdMonth) & " And [ProductionType] = " & Me.txtProdType
Docmd.OpenReport "MyReportName, , , strWhere


--
Dave Hargis, Microsoft Access MVP

Certainly,

This is a database that tracks the production on records in another
database. We update these records every year on a constant basis. There are
three categories of records, each month a report is created by each division
outlining how many records where planned for update that month, how many
where updated in normal production, how many were created, how many where
upated outside the normal production timeline (revisit), and the total number
of records updated that month. Occasionally the information changes (such as
the production plan) so previous months need to be recomputed. These records
that we produce on cover 8 regions and the report has all of the metrics
broken up by region, so 8 Regions X 12 Months X 5 Proudtion types X 3 Record
Types = 1440 queries.

I have started working on a different query module that will have the region
managers compile thier information themselves. This will cut down the
"percieved" time a bit. I am also toying with the recordset.Source and
.requery property and method. I imagine part of the time that was spent was
from creating the recordset object over and over again and setting it to
nothing at the end of the loop. We shall see what happens.

Cory

6000 records with 11 fields is really a pretty small table. Even if each of
the 3 is that size, it is still small.
480 queries times 3 tables or 1440 queries is a lot. I don't think I have
ever seen an Access application with over 600 queries.
My quess is you are making it harder than it needs to be. Can you give an
overview of what all these queries do?
--
Dave Hargis, Microsoft Access MVP


:

There are three tables that will end up being probably 6000 records with say
11 fields. 480 queries to be run on each table (with a grand total of 1440)
to get record counts for criteria (which will never change so it can be
stored in teh procedure in an array). Is this inherantly going to be a time
consuming operation (to perform, not to write) or have I been going about it
in a way that MAKES it time consuming?

Cory

:

For the specific:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset(strRecSetName,dbOpenDynaset)

How large is your table? (# of Rows, # columns?)
Are there any Domain Aggragate Functions in your queries?

--
Dave Hargis, Microsoft Access MVP


:

I tried this with a single recordset originally, updating the SQL query
through multiple variables. That operation took 45+ minutes the last time I
ran it, and really I thought that it would only take a minute at most. Is
there something that I am doing wrong? Running 1400 queries on a
(relatively) large table of records proved to be too long. Running 1400
queries on 36 smaller tables *should* in theory improve the run time since it
has to query fewer records. Should I just not even bother creating record
sets at all and instead query the table? in the original operation I was
opening and closing the record set with each query.

Also, specifically how do you use the strRecSetName when opening the record?

Cory

:

I sometimes have a difficult time putting my thoughts into words.

Don't worry about it. Often, my words have no thoughts behind them

I think you are making this harder than it is. What I would suggest is a
naming convention that would be able to identify the query name for each,
then create the name of the recordset to open. You should only have one
recordset open at a time.
There would be no need to have an array of month names, you can use the For
Next Index and the MonthName function to determine the month. You can also
use the Choose function to determine the record type:

For MonthCounter = 1 to 12
strMonthName = MonthName(MonthCounter)
For RecTypeCounter = 1 to 3
strRecType = Choose( RecTypeCounter, "One", "Two", "Three")
strRecordSetName = "qry" & strMonthName & strRecType
"**** Here is where you open the recordset and do your stuff ****
Next RecTypeCounter
Next MonthCounter



--
Dave Hargis, Microsoft Access MVP


:

I have a database that is used to track production on several thousand
records in a second database. The production database is all I am dealing
with here so really you dont have to worry about the second. Anyhow, every
month each office has to supply metrics on how much production was conducted
that month, whether offices are behind at or ahead of schedule and so forth.
Creating these slides (for powerpoint) is time consuming and is prone to lots
of error.

To remedy this, I am creating this database that when the Production Slide
creation procedure is executed it queries each of three tables that hold the
relevant data and inputs a record count of the records that match the query.

When this first started it worked pretty good. I only have a few hundred
records and it took about 2 minutes to do all of the queries (I worked it out
and it is about 1400 queries [12 months x 3 record types x 8 regions x 4 or 5
production types]). After I added the rest of the offices records to the
tables to be queried, it takes about 45 minutes to run the whole procedure.

To remedy this, I am reworking the procedure so that it creates 36 record
sets (12 months x 3 record types). The problem is this:

Is there a way to refer to the recordsets via string variables used in For
Loops? I know that there is not a recordset collection, but I will allude to
one for the purposes of this example...
i.e.
Dim MonthArray(11) as String 'lets assume I fill this array with month names
Dim RecTypeArray(2) as String 'lets assume I fill this array with record
Type names
Dim monthCounter as Integer
Dim RecTypeCounter as Integer

For MonthCounter = 0 to 12
For RecTypeCounter = 0 to 2
RecordSets(MonthArray(MonthCounter) &
RecTypeArray(RecTypeCounter).Open conn, Lock Type, etc.
Next RecTypeCounter
Next MonthCounter


I can handle opening each recordset "manually", but later on in a different
process, I will need the proper recordset queried based on which month and
which record type is being currently measured. Any thoughts?

And please let me know if I made this too confusing, I sometimes have a
difficult time putting my thoughts into words.

Cory

 

[Guest]

Okay, then create a table that contains all the parameter values and read
through that recordset to set the query parameters in VBA. I would not want
to be the one who had to modify 1440 queries if there is a change in the
database schema or the business rules.
--
Dave Hargis, Microsoft Access MVP

Well I need the procedure to step through all of the choises and run the
queries. The record counts are entered into a table which is accessed by
another query that sends the values for number of records produced into a
number of tables and graphs in PowerPoint. Having the user select the
parameters to use would only cause them to have to manually execute hundreds
of queries since all of the data that those queries retreived would be needed.

Okay, you don't really need as many queries as you think you do. For
example, you said:
8 Regions X 12 Months X 5 Proudtion types X 3 Record Types = 1440 queries.
You actually need either 1 or 3.
You could easily use a parameterized query. That is where you filter by
region, month, and productiontype for sure. If you mean by Record Type, a
different table schema, then you need 3, one for each record type. If Record
Type has a different meaning, you only need 1.

The way to do that would be to put controls on a form for the user to make
those selections, then use the Where argument of the OpenReport method.
For example:

Dim strWhere As String

strWhere = "[Region] = '" & Me.txtRegion & "' And [ProductionMonth] = "
& Month(Me.txtProdMonth) & " And [ProductionType] = " & Me.txtProdType
Docmd.OpenReport "MyReportName, , , strWhere


--
Dave Hargis, Microsoft Access MVP

Certainly,

This is a database that tracks the production on records in another
database. We update these records every year on a constant basis. There are
three categories of records, each month a report is created by each division
outlining how many records where planned for update that month, how many
where updated in normal production, how many were created, how many where
upated outside the normal production timeline (revisit), and the total number
of records updated that month. Occasionally the information changes (such as
the production plan) so previous months need to be recomputed. These records
that we produce on cover 8 regions and the report has all of the metrics
broken up by region, so 8 Regions X 12 Months X 5 Proudtion types X 3 Record
Types = 1440 queries.

I have started working on a different query module that will have the region
managers compile thier information themselves. This will cut down the
"percieved" time a bit. I am also toying with the recordset.Source and
.requery property and method. I imagine part of the time that was spent was
from creating the recordset object over and over again and setting it to
nothing at the end of the loop. We shall see what happens.

Cory

:

6000 records with 11 fields is really a pretty small table. Even if each of
the 3 is that size, it is still small.
480 queries times 3 tables or 1440 queries is a lot. I don't think I have
ever seen an Access application with over 600 queries.
My quess is you are making it harder than it needs to be. Can you give an
overview of what all these queries do?
--
Dave Hargis, Microsoft Access MVP


:

There are three tables that will end up being probably 6000 records with say
11 fields. 480 queries to be run on each table (with a grand total of 1440)
to get record counts for criteria (which will never change so it can be
stored in teh procedure in an array). Is this inherantly going to be a time
consuming operation (to perform, not to write) or have I been going about it
in a way that MAKES it time consuming?

Cory

:

For the specific:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset(strRecSetName,dbOpenDynaset)

How large is your table? (# of Rows, # columns?)
Are there any Domain Aggragate Functions in your queries?

--
Dave Hargis, Microsoft Access MVP


:

I tried this with a single recordset originally, updating the SQL query
through multiple variables. That operation took 45+ minutes the last time I
ran it, and really I thought that it would only take a minute at most. Is
there something that I am doing wrong? Running 1400 queries on a
(relatively) large table of records proved to be too long. Running 1400
queries on 36 smaller tables *should* in theory improve the run time since it
has to query fewer records. Should I just not even bother creating record
sets at all and instead query the table? in the original operation I was
opening and closing the record set with each query.

Also, specifically how do you use the strRecSetName when opening the record?

Cory

:

I sometimes have a difficult time putting my thoughts into words.

Don't worry about it. Often, my words have no thoughts behind them

I think you are making this harder than it is. What I would suggest is a
naming convention that would be able to identify the query name for each,
then create the name of the recordset to open. You should only have one
recordset open at a time.
There would be no need to have an array of month names, you can use the For
Next Index and the MonthName function to determine the month. You can also
use the Choose function to determine the record type:

For MonthCounter = 1 to 12
strMonthName = MonthName(MonthCounter)
For RecTypeCounter = 1 to 3
strRecType = Choose( RecTypeCounter, "One", "Two", "Three")
strRecordSetName = "qry" & strMonthName & strRecType
"**** Here is where you open the recordset and do your stuff ****
Next RecTypeCounter
Next MonthCounter



--
Dave Hargis, Microsoft Access MVP


:

I have a database that is used to track production on several thousand
records in a second database. The production database is all I am dealing
with here so really you dont have to worry about the second. Anyhow, every
month each office has to supply metrics on how much production was conducted
that month, whether offices are behind at or ahead of schedule and so forth.
Creating these slides (for powerpoint) is time consuming and is prone to lots
of error.

To remedy this, I am creating this database that when the Production Slide
creation procedure is executed it queries each of three tables that hold the
relevant data and inputs a record count of the records that match the query.

When this first started it worked pretty good. I only have a few hundred
records and it took about 2 minutes to do all of the queries (I worked it out
and it is about 1400 queries [12 months x 3 record types x 8 regions x 4 or 5
production types]). After I added the rest of the offices records to the
tables to be queried, it takes about 45 minutes to run the whole procedure.

To remedy this, I am reworking the procedure so that it creates 36 record
sets (12 months x 3 record types). The problem is this:

Is there a way to refer to the recordsets via string variables used in For
Loops? I know that there is not a recordset collection, but I will allude to
one for the purposes of this example...
i.e.
Dim MonthArray(11) as String 'lets assume I fill this array with month names
Dim RecTypeArray(2) as String 'lets assume I fill this array with record
Type names
Dim monthCounter as Integer
Dim RecTypeCounter as Integer

For MonthCounter = 0 to 12
For RecTypeCounter = 0 to 2
RecordSets(MonthArray(MonthCounter) &
RecTypeArray(RecTypeCounter).Open conn, Lock Type, etc.
Next RecTypeCounter
Next MonthCounter


I can handle opening each recordset "manually", but later on in a different
process, I will need the proper recordset queried based on which month and
which record type is being currently measured. Any thoughts?

And please let me know if I made this too confusing, I sometimes have a
difficult time putting my thoughts into words.

Cory