where will I notice negative side effects of bloat?

[mirandacascade@yahoo.com]

Question toward the bottom of this post....backgro und information
immediately below.

Access 97
SQL Server 2000

Please note: although the subject line uses the word 'bloat', this
post is NOT a "what can I do to prevent bloat?" inquiry. When I
searched the postings in this group for information about bloat, I
believe I gained a rudimentary understanding of some of the things
that contribute to bloat. I recognize that my application is doing
something that contributes to bloat: it sets up a querydef at the
beginning of a session and then deletes that querydef at the end of
the session. The posts in this group suggest that over time, that
will cause bloat, and that seems to be consistent with what I've
observed.

My application (a reporting system) does the following:

1) at the beginning of a session, sets up a querydef
2) when a user requests a report the application sets up the querydef;
all of the information for all of the reports reside on a SQL Server
2000 database, so the querydef is a pass-thru query. The .SQL
property of the querydef is a string, the contents of which is a call
to a stored proc
3) the Access application calls the stored proc
4) the stored proc returns a result set
5) the Access application takes the result set and uses it as the
recordsource for an Access report object.
6) at the end of the session, the application 'cleans up', i.e. it
deletes the querydef that it established at the beginning of the
session

I recognize that this will, over time cause bloat, and that is, in
fact, consistent with with I've observed. When compacted the
application is about 8MB. Over time, it has grown to 20MB before I've
compacted it again. I'm guessing that if I hadn't compacted it, it
would have continued to accumulate bloat beyond the 20MB level. I
know for a fact that:
a) the application never adds/deletes/modifies records in any of the
Access tables
b) the application never creates/deletes temp tables
So, when one looks at the database window in the 8MB app, one sees the
same objects as when one looks at the database window in the 20MB
app.

So, finally, my questions...the y have a theme of 'where will I notice
the bad side effects?"
1) will it take longer to load the 20MB bloated app than it will to
load the 8MB compacted app?
2) will it take longer to a load form in the 20MB app than it will to
load the same form in the 8MB app?
3) will it take longer to load a report in the 20MB app than it will
to load the same report (with the same result set as recordsource) in
the 8MB app?
4) I understand that a 20MB bloated app occupies more space on disk,
but are there other negative side effects of having a bloated app?
5) Does everything run slower in a bloated app, or is the slow down
(if there is, in fact, a slow down) happen only when the application
is first loaded?

Thank you.

[Albert D. Kallal]

Re: where will I notice negative side effects of bloat?

<mirandacascade @yahoo.comwrote in message
news:155f6bdf-8194-4760-8a46-109133131078@t1 g2000pra.google groups.com...

Question toward the bottom of this post....backgro und information

My application (a reporting system) does the following:
>
1) at the beginning of a session, sets up a querydef
2) when a user requests a report the application sets up the querydef;

Perhaps you just bind the report to a linked table and pass the parameters
using a where clause? A linked odbc table should run about as fast anyway.
(unless there is multi-table joins).

5) the Access application takes the result set and uses it as the
recordsource for an Access report object.

Are you assigning query in the reports open event? (not clear how you doing
the above). Remember most bloat occurs when you modify objects in design
mode and then save them. So, if your modifying the report, you want to avoid
this.

6) at the end of the session, the application 'cleans up', i.e. it
deletes the querydef that it established at the beginning of the
session

Actually why delete it? That just creates a hole like deleting a file on you
hard disk that then needs defragmenting. Simply leave the query in place.
And, just modify it for next time. You can often reduce bloat this way.

a) the application never adds/deletes/modifies records in any of the
Access tables

Fine, but does it open any object like a form, or report, or query in design
mode and then save it?

I would suggest you run the application as a mde (that way, code can't
become un-compiled, and you reduce chances of bloat that way. it is a MUST
to install the jet sp3 serviced pack, and the sr2b update to office. Both of
these can significantly reduce your bloating problems, and in some case near
eliminate them.

b) the application never creates/deletes temp tables

It not just tables, but any object you flip into design mode or modify and
save will cause bloat.

So, when one looks at the database window in the 8MB app, one sees the
same objects as when one looks at the database window in the 20MB
app.
>
So, finally, my questions...the y have a theme of 'where will I notice
the bad side effects?"
1) will it take longer to load the 20MB bloated app than it will to
load the 8MB compacted app?

For the most part no. ms-access only loads the parts it using. So, if you
have a table with 1 record in it, or 300,000 records it in it , and you
simply load the ms-access application, that whole table is NOT loaded into
memory. So, thus the load time access is should be the same.

2) will it take longer to a load form in the 20MB app than it will to
load the same form in the 8MB app?

Again, no. I suppose perhaps over a LONG period of time it might slow down a
bit, but for the most part the load time should remain fairly constant.

3) will it take longer to load a report in the 20MB app than it will
to load the same report (with the same result set as recordsource) in
the 8MB app?

Once again...no it should not.

4) I understand that a 20MB bloated app occupies more space on disk,
but are there other negative side effects of having a bloated app?

Yes, you substantially increase the chances of corruption as now there are
1000's of objects in the access application that must be maintained. It much
like driving in rush hour traffic, you much increase your chances of a
accident since your dealing with more cars (or in our case objects). You can
run an applcation with 1 user for 5 years, but if you have 5 users running
it, then you have 5 times the chance of a problem. So, a table with 1
million reocrds has more chances of being damanged then a table with 2
reocrds. It quesiton of probablbiy and chance. So, more stuff and more bloat
means that the system is stressed more in terms of the number of objects.

5) Does everything run slower in a bloated app, or is the slow down
(if there is, in fact, a slow down) happen only when the application
is first loaded?

Well it should not, but it likely does a small amount. (generally not be too
noticed). There will be more "objects" to deal with in the application, but
access does a very good job of ignoring that extra stuff (and, we not really
processing bound these days).

One way to ensure code does not un-compile is to use a mde. And, mde's tend
to run a bit faster also. Note that a great side effect of a mde is that
un-handled code errors don't blow out local, or even global variables (your
application just keeps chucking along). So, mde's tend to make your
application more reliable.

Like anything else, if you don't change the oil in your car, it will not
blow up, but eventually it good idea to adopt some type of maintains
schedule for tat car, or in his case regular compacting of the front end. I
have some clients that go for 6 months, but then again I taken care to not
have any bloat...and usually a new update is issued to them before a
compact/repair is needed anyway.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[lyle fairfield]

Re: where will I notice negative side effects of bloat?

the Access application takes the result set and uses it as the

recordsource for an Access report object.

Please, tell us how your Access 97 application does this. Either I am old
and stupid or you have accomplished the impossible. (OK ... maybe all
three!)

Have you tested your application omitting or commenting out the
procedures which you believe cause the observed bloat? Can you verify
that there is no bloat when this is done?

As to your questions about speed, I am almost certain that the correct
answer to each is, "Maybe!". The workings of Access are quite mysterious.

BTW, ADPs are wonderfully small and TTBOMK, do not bloat.


mirandacascade@ yahoo.com wrote in news:155f6bdf-8194-4760-8a46-
109133131078@t1 g2000pra.google groups.com:

Question toward the bottom of this post....backgro und information
immediately below.
>
Access 97
SQL Server 2000
>
Please note: although the subject line uses the word 'bloat', this
post is NOT a "what can I do to prevent bloat?" inquiry. When I
searched the postings in this group for information about bloat, I
believe I gained a rudimentary understanding of some of the things
that contribute to bloat. I recognize that my application is doing
something that contributes to bloat: it sets up a querydef at the
beginning of a session and then deletes that querydef at the end of
the session. The posts in this group suggest that over time, that
will cause bloat, and that seems to be consistent with what I've
observed.
>
My application (a reporting system) does the following:
>
1) at the beginning of a session, sets up a querydef
2) when a user requests a report the application sets up the querydef;
all of the information for all of the reports reside on a SQL Server
2000 database, so the querydef is a pass-thru query. The .SQL
property of the querydef is a string, the contents of which is a call
to a stored proc
3) the Access application calls the stored proc
4) the stored proc returns a result set
5) the Access application takes the result set and uses it as the
recordsource for an Access report object.
6) at the end of the session, the application 'cleans up', i.e. it
deletes the querydef that it established at the beginning of the
session
>
I recognize that this will, over time cause bloat, and that is, in
fact, consistent with with I've observed. When compacted the
application is about 8MB. Over time, it has grown to 20MB before I've
compacted it again. I'm guessing that if I hadn't compacted it, it
would have continued to accumulate bloat beyond the 20MB level. I
know for a fact that:
a) the application never adds/deletes/modifies records in any of the
Access tables
b) the application never creates/deletes temp tables
So, when one looks at the database window in the 8MB app, one sees the
same objects as when one looks at the database window in the 20MB
app.
>
So, finally, my questions...the y have a theme of 'where will I notice
the bad side effects?"
1) will it take longer to load the 20MB bloated app than it will to
load the 8MB compacted app?
2) will it take longer to a load form in the 20MB app than it will to
load the same form in the 8MB app?
3) will it take longer to load a report in the 20MB app than it will
to load the same report (with the same result set as recordsource) in
the 8MB app?
4) I understand that a 20MB bloated app occupies more space on disk,
but are there other negative side effects of having a bloated app?
5) Does everything run slower in a bloated app, or is the slow down
(if there is, in fact, a slow down) happen only when the application
is first loaded?
>
Thank you.
>

[mirandacascade@yahoo.com]

Re: where will I notice negative side effects of bloat?

On Aug 8, 11:23 pm, lyle fairfield <lylef...@yah00 .cawrote:

Please, tell us how your Access 97 application does this. Either I am old
and stupid or you have accomplished the impossible. (OK ... maybe all
three!)

My hunch is that I did a bad job of describing result-set/recordsource
relationship in my original post...I used the following wording in my
original post:

"the Access application takes the result set and uses it as the
recordsource for an Access report object."

Here is the code to which I was referring:

<in the open event of the report object , so "Me." refers to the
report object>

Dim qdf As QueryDef
Dim strConnect as String

strConnect = "ODBC;" & _
"DRIVER=SQL Server;" & _
"SERVER=DummySe rver;" & _
"UID=DummyUseri d;" & _
"DATABASE=Dummy DB;" & _
"Trusted_Connec tion=Yes;" & _
"Network=DBMSSO CN"

' establish a querydef named qryDummy;
Set qdf = CurrentDB.Creat eQueryDef("qryD ummy", "Select * FROM
tblDummy")
qdf.Connect = strConnect
qdf.ODBCTimeout = 60

' uspDummy is the name of a stored proc on the SQL Server 2000
database
qdf.SQL = "EXEC uspDummy"
' set the recordsource of this report object to the name of the
querydef;
' reminder: this code is in the open event of the report object; I
have observed
' in debug mode that sometime after the last statement in the
report open
' and before the first statement in the report activate event, MS
Access will:
' a) run the stored proc uspDummy
' b) take the result set from that stored proc and populate the
controls
' in the report (the ControlSource properties of the
controls correspond
' with the column names in the result set returned by stored
proc)
Me.RecordSource = "qryDummy"

<end of code snippet>

So, that's what I meant by "...uses it as the recordsource for an
Access report object"

[Albert D. Kallal]

Re: where will I notice negative side effects of bloat?

"lyle fairfield" <lylefa1r@yah00 .cawrote in message
news:Xns9AF5184 15B458666664626 1@216.221.81.11 9...

>the Access application takes the result set and uses it as the
>recordsource for an Access report object.

>
Please, tell us how your Access 97 application does this. Either I am old
and stupid or you have accomplished the impossible. (OK ... maybe all
three!)

Actually, this sample been posted on the access web for quite a few years:

http://www.mvps.org/access/reports/rpt0014.htm

You can set the sql in the reports on-open event. it just question if you
build a new query (as this poster is doing, **or** you simply stuff in the
sql string right into the reports recordSource property.

So, not, one is NOT building the reocrdset in code and setting a report to
that, but you can build the sql as a string, or query..and set the report to
use that (certainly a bit semantics in difference..and to be fair I suspect
your surprise was that idea that you can assign a recordset to a report
(which you cannot do). So it is a bit of a misunderstand here...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[lyle fairfield]

Re: where will I notice negative side effects of bloat?

Yes, I know that one can set a report's recordsource to an SQL string
in a report's open event. But that is not setting a report's
recordsource to a result set.

As this can be done very simply using a Public Function, what would
the advantage of opening a recordset be?

In ADPs from Access 2002 on, one can set the recordset of a report to
an ADO recordset; this may have some advantages if one wants to
disconnect the recordset and do some modifications to the data that
might be difficult with the SQL. As SQL Server stored procedures can
be as powerful or almost as powerful as VBA procedures, this may be
reudundant.


On Aug 9, 6:13 pm, "Albert D. Kallal" <PleaseNOOOsPAM mkal...@msn.com >
wrote:

"lyle fairfield" <lylef...@yah00 .cawrote in message
>
news:Xns9AF5184 15B458666664626 1@216.221.81.11 9...
>

the Access application takes the result set and uses it as the
recordsource for an Access report object.

>

Please, tell us how your Access 97 application does this. Either I am old
and stupid or you have accomplished the impossible. (OK ... maybe all
three!)

>
Actually, this sample been posted on the access web for quite a few years:
>

http://www.mvps.org/access/reports/rpt0014.htm

>
You can set the sql in the reports on-open event. it just question if you
build a new query (as this poster is doing, **or** you simply stuff in the
sql string right into the reports recordSource property.
>
So, not, one is NOT building the reocrdset in code and setting a report to
that, but you can build the sql as a string, or query..and set the reportto
use that (certainly a bit semantics in difference..and to be fair I suspect
your surprise was that idea that you can assign a recordset to a report
(which you cannot do). So it is a bit of a misunderstand here...
>
--
Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa l...@msn.com

[Albert D. Kallal]

Re: where will I notice negative side effects of bloat?

Seems you got a good handle on this stuff.
(And, it not like your a beginner here, you well down the curve of using
ms-access).

I don't think what you shown should be a "major" cause of bloat.

As mentioned, you could likely just link to a table and pass needed
parameters via the "where" clause.

You can also create one pass-though query where you setup everything in the
query, and then JUST change the sql...as follows:


Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.Query Defs("MyResuabl ePassThroughQue ry")
qdf.SQL = "select * bla bal bal...
qdf.Close
Me.RecordSource = qdf.Name

The above means we not creating a query def from scratch (and, you don't
have all that connection stuff in your code either).

I don't think your use of querfydefs is that large of problem. (it likely
takes a good deal of time to get up to 20 megs -- if it occurs quite
fast..then try using a mde).

So, try the above query re-use idea...it might eliminate the bloat by a
significant margin.

As mentioned, you made it quite clear that nothing else is flipped into
design mode etc. At the end of the day sounds like your just looking for
more information, not that you need to change what you have now. So, take my
comments as some fishing ideas...not that you need to change what you have
or are doing anything wrong......



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[David W. Fenton]

Re: where will I notice negative side effects of bloat?

"Albert D. Kallal" <PleaseNOOOsPAM mkallal@msn.com wrote in
news:alpnk.1719 50$gc5.28376@pd 7urf2no:

You can also create one pass-though query where you setup
everything in the query, and then JUST change the sql...as
follows:
>
>
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.Query Defs("MyResuabl ePassThroughQue ry")
qdf.SQL = "select * bla bal bal...
qdf.Close
Me.RecordSource = qdf.Name

I don't understand why it can't all be done with assigning a SQL
string to the recordsource in the OnOpen event of the report. What's
the point of editing a QueryDef?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Re: where will I notice negative side effects of bloat?

"Albert D. Kallal" <PleaseNOOOsPAM mkallal@msn.com wrote in
news:7Eonk.6919 2$nD.28062@pd7u rf1no:

"lyle fairfield" <lylefa1r@yah00 .cawrote in message
news:Xns9AF5184 15B458666664626 1@216.221.81.11 9...

>>the Access application takes the result set and uses it as the
>>recordsourc e for an Access report object.

>>
>Please, tell us how your Access 97 application does this. Either
>I am old and stupid or you have accomplished the impossible. (OK
>... maybe all three!)

>
Actually, this sample been posted on the access web for quite a
few years:
>
http://www.mvps.org/access/reports/rpt0014.htm

Lyle is correct that what is stated is definitely *not* what is
happening. There is no recordset (or result set) being assigned as
the recordsource, just a SQL string (or a QueryDef).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Albert D. Kallal]

Re: where will I notice negative side effects of bloat?

"David W. Fenton" <XXXusenet@dfen ton.com.invalid wrote in message

>
I don't understand why it can't all be done with assigning a SQL
string to the recordsource in the OnOpen event of the report. What's
the point of editing a QueryDef?

Because then the querydef has **already** been setup and marked as a
pass-through......


On the other hand, I don't think a query as such benefits from being
pass-though as oppose to simply basing the form on a linked table and
stuffing in the sql as you suggest...

anyway...the "goal" here was by editing the query def is to keep/make it
pass-though, but still be able to modify the sql. I think my example is
cleaner since all of the pass-thought + connection stuff simply already need
be setup in the query..and you don't play with the connection string in the
code.

So, the idea here is to be able to edit/change the sql, but keep it pass
though...

I don't really think the extra efforts and code is worth this, and it is
un-clear why a simple standard linked table is not being used. Perhaps the
server (or table) is always changing for the report..but without more
information it is hard to speculate as to why pass-through and a querydef is
needed at all here...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[Timmy!]

Re: where will I notice negative side effects of bloat?

On Aug 9, 3:33 am, mirandacasc...@ yahoo.com wrote:

Please note: although the subject line uses the word 'bloat', this
post is NOT a "what can I do to prevent bloat?" inquiry. When I

Well, my response is perhaps an answer to the question you don't need
addressed, but here's something I do with my apps when i was using A97
and currently in A2003.

I always have a custom menu bar. To create one of the items on it,
when I'm in customize, I hold my control key and drag the Compact (or
in A2003, Compact & Repair) menu item from the standard Tools menu to
my custom tool bar. The control key, if you're not aware of it, just
makes a copy of the menu item in question so you don't lose it from
the main menu.

I give it a bell image (because it looks cool and looks like the
application is ringing to tell you do do something) and change the
text to:

OPTIMIZATION RECOMMENDED!

A user not intricately familiar will understand "optimize" better than
"compact & repair".

I always use a splash page in my apps. In the on open procedure, I do
something like this:

Dim lngAppSize As Long

lngAppSize = filelen(Access. CurrentDb.Name)

'Compare size to "optimum size"
'Optimum size depends on your application - I usually make it
'twice the size of a newly compacted app
'IN the example below, the compact menu item described is 7th in
'the menu controls of mnuMain

If lngAppSize >= 20000000 Then
CommandBars("mn uMain").Control s.Item(7).Visib le = True
Else
CommandBars("mn uMain").Control s.Item(7).Visib le = False
End If

With this running whenever the app opens, if the application is
bloated past the limit you want it, the user sees the bell ringing
"OPTIMIZATI ON RECOMMENDED!" very clearly and will know to click the
menu item. When the app compacts and is below the threshold, the
OPTIMIZATION RECOMMENDED! menu item is then invisible when the splash
form comes on.

This is probably motherhood for most developers here, but I present
here as an option for you to help assuage any fears of users letting
their front ends bloat to the exploding point...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto