Recordset validation in reports

  • Thread starter Thread starter Jason J.
  • Start date Start date
J

Jason J.

Hello everyone,

I have an access database that gets updated with new data once per month.
At that time I have a form that goes through:

1. all of the queries and programmatically adds the new month field to the
SQL strings, and
2. all of the reports, updating the captions, field headings and the field
data sources

The database is native to Access 2000 (I have wide variation in users and
the version they have installed). I myself am on Access 2007.

The problem is that as the database has grown, and as I move further from
Access 2000 the program is taking much, much longer to run. It is now at
about 6 hours (up from 40 minutes about 15 months ago when I was on Access
2003).

Not counting version differences and any issues that may cause, I have a
theory. I think that Access does a validation of the fields in the
recordsource/controlsource for each change I make with the program, causing
the query to run multiple times for each report that I modify. Some of the
queries can take 10-12 minutes to run (the database is huge), eventhough I
use indexes to help with this.

Can anyone confirm this? If this is the case, is there a way to temporarily
disable it?

Any help or other observations of similar behavior would be helpful. I need
to get this back to under an hour if possible.

Thanks in advance,
 
Sorry, Jason, but I am seeing a whole lot of bad design here.
What is the original source table look like? Does it have a column for each
month or does each transaction have a field identifying the month of the
transaction?

If you can tell me a bit more about your tables, perhaps I can suggest some
alternatives that will improve your performance.
 
There is just one primary table with the following 4 key fields (data fields
omitted ... to save time).

Year
Month
Geography
ProductCode

and then data columns

This table has just over 500K records currently. There are a number of
lookup tables in the database too, database size is 106 MB.

The data are not transactions, but rather the result of tabulations
performed by SQL server and my VB developers on a much larger dataset. Once
a month we tabulate and dump the data into access for lower level users to
get at the data (I know there are probably better ways ... legacy system).

So, I manage the Access Db and have to add the new month to several static
reports (and their underlying queries). Once again, the reports are static.
The program with the problem is the one that parses the SQL strings and
Report controls adding the month as it goes. The database design has never
been criticized before even by my own VB/SQL dev's so I've assumed it was OK.
Past performance on other versions seems to indicate a difference in how
2007 performs -vs- the older versions.
 
I should add that only certain queries get updated, those that turn month
across the page (crosstab queries); most reports are set to work with a
specific, or the most current month.
 
Would there be any problem with having the queries static with all the months
defined?
For example, using calculated fields in the query for each month:

Jan: IIf([MonthlyTable].[Month] = 1, [MonthlyTable].[Month], Null)
Feb: IIf([MonthlyTable].[Month] = 2, [MonthlyTable].[Month], Null)
etc.

Then in the report itself you can make the lables and controls invisible
based on the reporting month.
BTW, Month is not a good name because it is an Access reserved word.
 
Right regarding the "month" name, in actuality I use "Yr" & "Period"; I
thought it would make more sense if I told you what Period meant, sorry 'bout
that.

If I understand what you've suggested, I don't think its what I need. I
have 12 columns worth of label and textbox controls defined on a given report
(each in their appropriate section of the report). By changing the controls
datasource property or caption property, my program I moves the leftmost
month column off the report, moves the remaining eleven one postion to the
left and adds the most current month on the right . The report(s) are always
6 or 12 months wide, but with the most recent month in the right column. A
caption might look like "2008/01". A datasource might be "Vol200801" or
"Perc200801".

I have confirmed from another friend this type of behavior in the query
design grid when building queries on queries, but only since my "upgrade" to
office 2007 have I seen it affect the program I run.

Thanks for your time and effort in helping me out, sounds so far like I'm in
for a lot of re-writing/re-designing (or I can be patient and run this
overnight or something).

--
JRJ


Klatuu said:
Would there be any problem with having the queries static with all the months
defined?
For example, using calculated fields in the query for each month:

Jan: IIf([MonthlyTable].[Month] = 1, [MonthlyTable].[Month], Null)
Feb: IIf([MonthlyTable].[Month] = 2, [MonthlyTable].[Month], Null)
etc.

Then in the report itself you can make the lables and controls invisible
based on the reporting month.
BTW, Month is not a good name because it is an Access reserved word.
--
Dave Hargis, Microsoft Access MVP


Jason J. said:
There is just one primary table with the following 4 key fields (data fields
omitted ... to save time).

Year
Month
Geography
ProductCode

and then data columns

This table has just over 500K records currently. There are a number of
lookup tables in the database too, database size is 106 MB.

The data are not transactions, but rather the result of tabulations
performed by SQL server and my VB developers on a much larger dataset. Once
a month we tabulate and dump the data into access for lower level users to
get at the data (I know there are probably better ways ... legacy system).

So, I manage the Access Db and have to add the new month to several static
reports (and their underlying queries). Once again, the reports are static.
The program with the problem is the one that parses the SQL strings and
Report controls adding the month as it goes. The database design has never
been criticized before even by my own VB/SQL dev's so I've assumed it was OK.
Past performance on other versions seems to indicate a difference in how
2007 performs -vs- the older versions.
Click to expand...
Click to expand...
 
Hope I've helped some. Not sure why 2007 is slower. I have only been
playing with it for a short time now.

But as to moving the controls around on your reports, I still think leaving
them in places and making them visible or not and changing the captions
programatically would be faster. This would, of course, mean your queries
would have to support the report design. But, I think it would be faster.
To do what you are having to do requires programmatically opening the report
in design view (probably hidden), repositioning controls and changing
captions, then saving the report, then running the report would be slower
than using the report's open event to change the visible properties and
setting the caption values.

Best of luck.
--
Dave Hargis, Microsoft Access MVP


Jason J. said:
Right regarding the "month" name, in actuality I use "Yr" & "Period"; I
thought it would make more sense if I told you what Period meant, sorry 'bout
that.

If I understand what you've suggested, I don't think its what I need. I
have 12 columns worth of label and textbox controls defined on a given report
(each in their appropriate section of the report). By changing the controls
datasource property or caption property, my program I moves the leftmost
month column off the report, moves the remaining eleven one postion to the
left and adds the most current month on the right . The report(s) are always
6 or 12 months wide, but with the most recent month in the right column. A
caption might look like "2008/01". A datasource might be "Vol200801" or
"Perc200801".

I have confirmed from another friend this type of behavior in the query
design grid when building queries on queries, but only since my "upgrade" to
office 2007 have I seen it affect the program I run.

Thanks for your time and effort in helping me out, sounds so far like I'm in
for a lot of re-writing/re-designing (or I can be patient and run this
overnight or something).

--
JRJ


Klatuu said:
Would there be any problem with having the queries static with all the months
defined?
For example, using calculated fields in the query for each month:

Jan: IIf([MonthlyTable].[Month] = 1, [MonthlyTable].[Month], Null)
Feb: IIf([MonthlyTable].[Month] = 2, [MonthlyTable].[Month], Null)
etc.

Then in the report itself you can make the lables and controls invisible
based on the reporting month.
BTW, Month is not a good name because it is an Access reserved word.
--
Dave Hargis, Microsoft Access MVP


Jason J. said:
There is just one primary table with the following 4 key fields (data fields
omitted ... to save time).

Year
Month
Geography
ProductCode

and then data columns

This table has just over 500K records currently. There are a number of
lookup tables in the database too, database size is 106 MB.

The data are not transactions, but rather the result of tabulations
performed by SQL server and my VB developers on a much larger dataset. Once
a month we tabulate and dump the data into access for lower level users to
get at the data (I know there are probably better ways ... legacy system).

So, I manage the Access Db and have to add the new month to several static
reports (and their underlying queries). Once again, the reports are static.
The program with the problem is the one that parses the SQL strings and
Report controls adding the month as it goes. The database design has never
been criticized before even by my own VB/SQL dev's so I've assumed it was OK.
Past performance on other versions seems to indicate a difference in how
2007 performs -vs- the older versions.



--
JRJ


:

Sorry, Jason, but I am seeing a whole lot of bad design here.
What is the original source table look like? Does it have a column for each
month or does each transaction have a field identifying the month of the
transaction?

If you can tell me a bit more about your tables, perhaps I can suggest some
alternatives that will improve your performance.
--
Dave Hargis, Microsoft Access MVP


:

Hello everyone,

I have an access database that gets updated with new data once per month.
At that time I have a form that goes through:

1. all of the queries and programmatically adds the new month field to the
SQL strings, and
2. all of the reports, updating the captions, field headings and the field
data sources

The database is native to Access 2000 (I have wide variation in users and
the version they have installed). I myself am on Access 2007.

The problem is that as the database has grown, and as I move further from
Access 2000 the program is taking much, much longer to run. It is now at
about 6 hours (up from 40 minutes about 15 months ago when I was on Access
2003).

Not counting version differences and any issues that may cause, I have a
theory. I think that Access does a validation of the fields in the
recordsource/controlsource for each change I make with the program, causing
the query to run multiple times for each report that I modify. Some of the
queries can take 10-12 minutes to run (the database is huge), eventhough I
use indexes to help with this.

Can anyone confirm this? If this is the case, is there a way to temporarily
disable it?

Any help or other observations of similar behavior would be helpful. I need
to get this back to under an hour if possible.

Thanks in advance,
Click to expand...
Click to expand...
Click to expand...
 
I cannot figure out what causes it to be slower either, with much research
across many sites. It is frustrating, but also it is only once a month ...

You nailed it on the open/design/hidden approach, but I do not move any
controls around (I just edit the captions and controlsource properties) and I
always show every data column on the report (6 or 12 months worth depending
on design). So, admittedly, I may not understand how the hidden/visible
approach you've suggested would apply. The reports are "dumb" (no code) and
only change one time per month. Oh and to be absolutely clear, the reports
are not modified on demand, rather in a setup phase before I deploy the
updated versions - I don't know if that came across earlier.

Yes, talking like this ALWAYS helps even if a clear cut and definitive
solution isn't immediately found. Thanks again,
--
JRJ


Klatuu said:
Hope I've helped some. Not sure why 2007 is slower. I have only been
playing with it for a short time now.

But as to moving the controls around on your reports, I still think leaving
them in places and making them visible or not and changing the captions
programatically would be faster. This would, of course, mean your queries
would have to support the report design. But, I think it would be faster.
To do what you are having to do requires programmatically opening the report
in design view (probably hidden), repositioning controls and changing
captions, then saving the report, then running the report would be slower
than using the report's open event to change the visible properties and
setting the caption values.

Best of luck.
--
Dave Hargis, Microsoft Access MVP


Jason J. said:
Right regarding the "month" name, in actuality I use "Yr" & "Period"; I
thought it would make more sense if I told you what Period meant, sorry 'bout
that.

If I understand what you've suggested, I don't think its what I need. I
have 12 columns worth of label and textbox controls defined on a given report
(each in their appropriate section of the report). By changing the controls
datasource property or caption property, my program I moves the leftmost
month column off the report, moves the remaining eleven one postion to the
left and adds the most current month on the right . The report(s) are always
6 or 12 months wide, but with the most recent month in the right column. A
caption might look like "2008/01". A datasource might be "Vol200801" or
"Perc200801".

I have confirmed from another friend this type of behavior in the query
design grid when building queries on queries, but only since my "upgrade" to
office 2007 have I seen it affect the program I run.

Thanks for your time and effort in helping me out, sounds so far like I'm in
for a lot of re-writing/re-designing (or I can be patient and run this
overnight or something).

--
JRJ


Klatuu said:
Would there be any problem with having the queries static with all the months
defined?
For example, using calculated fields in the query for each month:

Jan: IIf([MonthlyTable].[Month] = 1, [MonthlyTable].[Month], Null)
Feb: IIf([MonthlyTable].[Month] = 2, [MonthlyTable].[Month], Null)
etc.

Then in the report itself you can make the lables and controls invisible
based on the reporting month.
BTW, Month is not a good name because it is an Access reserved word.
--
Dave Hargis, Microsoft Access MVP


:

There is just one primary table with the following 4 key fields (data fields
omitted ... to save time).

Year
Month
Geography
ProductCode

and then data columns

This table has just over 500K records currently. There are a number of
lookup tables in the database too, database size is 106 MB.

The data are not transactions, but rather the result of tabulations
performed by SQL server and my VB developers on a much larger dataset. Once
a month we tabulate and dump the data into access for lower level users to
get at the data (I know there are probably better ways ... legacy system).

So, I manage the Access Db and have to add the new month to several static
reports (and their underlying queries). Once again, the reports are static.
The program with the problem is the one that parses the SQL strings and
Report controls adding the month as it goes. The database design has never
been criticized before even by my own VB/SQL dev's so I've assumed it was OK.
Past performance on other versions seems to indicate a difference in how
2007 performs -vs- the older versions.



--
JRJ


:

Sorry, Jason, but I am seeing a whole lot of bad design here.
What is the original source table look like? Does it have a column for each
month or does each transaction have a field identifying the month of the
transaction?

If you can tell me a bit more about your tables, perhaps I can suggest some
alternatives that will improve your performance.
--
Dave Hargis, Microsoft Access MVP


:

Hello everyone,

I have an access database that gets updated with new data once per month.
At that time I have a form that goes through:

1. all of the queries and programmatically adds the new month field to the
SQL strings, and
2. all of the reports, updating the captions, field headings and the field
data sources

The database is native to Access 2000 (I have wide variation in users and
the version they have installed). I myself am on Access 2007.

The problem is that as the database has grown, and as I move further from
Access 2000 the program is taking much, much longer to run. It is now at
about 6 hours (up from 40 minutes about 15 months ago when I was on Access
2003).

Not counting version differences and any issues that may cause, I have a
theory. I think that Access does a validation of the fields in the
recordsource/controlsource for each change I make with the program, causing
the query to run multiple times for each report that I modify. Some of the
queries can take 10-12 minutes to run (the database is huge), eventhough I
use indexes to help with this.

Can anyone confirm this? If this is the case, is there a way to temporarily
disable it?

Any help or other observations of similar behavior would be helpful. I need
to get this back to under an hour if possible.

Thanks in advance,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top