473,320 Members | 2,098 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Interesting approach for compacting on close

To further follow up on my last post regarding the docmd.quit vs.
Application.quit using access 2007, I noticed that docmd.quit will correctly
compact the database (program file) if you have the "Compact on Close"
option set for the current database.

However, even with Compact on Close set, if you use the Application.Quit
acQuitSaveNone, the compacting will not fire.

This behavior actually seems desirable, as you can now make a program
smarter upon exiting your program. If you have an exit button that performs
some housekeeping type procs, backup, etc., then exits the access program,
you can take advantage of the behavior above (docmd.quit vs.
application.quit) to determine if you should compact.

To do this, make sure "Compact on Close" is set for the current database
(your access program file), then run the following function on your exit
button.

Hope this helps people who might have been looking for a similar solution.
This behavior works in Access 2007, but I'm not sure about 2003.
Public Function ExitButton() As Boolean
Dim progsize As Long, compactyn As Integer
progsize = FileLen(CurrentDb.Name)
If progsize 40000000 Then '=40 MB. Set a reasonable number where you
think the program should be compacted
compactyn = -1
Else
compactyn = 0
End If
'MsgBox "Program size is now: " & progsize & " bytes. Setting compact on
close to: " & compactyn
'Call AutomatedBU(0) 'run any of your procs, etc.
'Call CloseOpenTabs 'a routine to close out all open forms
'
Exit_btnExit_Click:
'
If compactyn = -1 Then
DoCmd.Quit 'the docmd.quit will correctly run the compact on close
Else
Application.Quit acQuitSaveNone 'For whatever reason, this command
Ignores the "Compact on Close" setting
End If
'
' Exit Function

Err_btnExit_Click:
If Err = 2450 Then 'user mistankingly closed while previewing a report
Exit Function
ElseIf Err = 2501 Then
Resume Next
Else
MsgBox Err.Description & " - " & Err.Number & ", Function:
ExitButton", vbCritical
Resume Exit_btnExit_Click
End If
End Function

Oct 30 '08 #1
32 2291
Andy wrote:
To further follow up on my last post regarding the docmd.quit vs.
Application.quit using access 2007, I noticed that docmd.quit will
correctly compact the database (program file) if you have the
"Compact on Close" option set for the current database.
Most experienced developers consider Compact-On-Close to be at best, useless
and more often, a pretty bad idea.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Oct 30 '08 #2
I appreciate the comment and have read similar comments before, however, if
anything happens to the front-end on the compact, worst case is you grab a
new copy of the FE.

Compact on close has no affect on the BE, it only operates on the file you
have opened, not linked tables, etc.

Can you give examples of why it's a bad idea?

"Rick Brandt" <ri*********@hotmail.comwrote in message
news:WY****************@flpi147.ffdc.sbc.com...
Andy wrote:
>To further follow up on my last post regarding the docmd.quit vs.
Application.quit using access 2007, I noticed that docmd.quit will
correctly compact the database (program file) if you have the
"Compact on Close" option set for the current database.

Most experienced developers consider Compact-On-Close to be at best,
useless and more often, a pretty bad idea.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Oct 30 '08 #3
"Andy" <PC*****@PCESoft.invalidwrote in message
news:IN****************@flpi150.ffdc.sbc.com...
To further follow up on my last post regarding the docmd.quit vs.
Application.quit using access 2007, I noticed that docmd.quit will
correctly compact the database (program file) if you have the "Compact on
Close" option set for the current database.

However, even with Compact on Close set, if you use the Application.Quit
acQuitSaveNone, the compacting will not fire.
Hmm ... I've just tried it in A2003 and it *does* work. Are you up to date
with SPs and the like?

Keith.
www.keithwilby.com

Oct 30 '08 #4
I'm using access 2007, with service pack 1.

Maybe Microsoft planned it so that the application.quit with the nosave
option ignores the compact?

"Keith Wilby" <he**@there.comwrote in message
news:49********@glkas0286.greenlnk.net...
"Andy" <PC*****@PCESoft.invalidwrote in message
news:IN****************@flpi150.ffdc.sbc.com...
>To further follow up on my last post regarding the docmd.quit vs.
Application.quit using access 2007, I noticed that docmd.quit will
correctly compact the database (program file) if you have the "Compact on
Close" option set for the current database.

However, even with Compact on Close set, if you use the Application.Quit
acQuitSaveNone, the compacting will not fire.

Hmm ... I've just tried it in A2003 and it *does* work. Are you up to
date with SPs and the like?

Keith.
www.keithwilby.com
Oct 30 '08 #5
"Andy" <PC*****@PCESoft.invalidwrote in message
news:Ba*****************@flpi150.ffdc.sbc.com...
>I appreciate the comment and have read similar comments before, however, if
anything happens to the front-end on the compact, worst case is you grab a
new copy of the FE.
Why not then just grab a new copy of the FE with each new session? It's not
rocket science and has many benefits, most notably here the mitigation of
the compact on close issue.

Keith.

Oct 30 '08 #6
Mainly because this is a shareware runtime app that a user can download. I
think setting up a batch file, and automating that into the program
launching shortcut might be a more difficult thing in my case.

"Keith Wilby" <he**@there.comwrote in message
news:49**********@glkas0286.greenlnk.net...
"Andy" <PC*****@PCESoft.invalidwrote in message
news:Ba*****************@flpi150.ffdc.sbc.com...
>>I appreciate the comment and have read similar comments before, however,
if anything happens to the front-end on the compact, worst case is you
grab a new copy of the FE.

Why not then just grab a new copy of the FE with each new session? It's
not rocket science and has many benefits, most notably here the mitigation
of the compact on close issue.

Keith.
Oct 30 '08 #7
Andy wrote:
I appreciate the comment and have read similar comments before,
however, if anything happens to the front-end on the compact, worst
case is you grab a new copy of the FE.

Compact on close has no affect on the BE, it only operates on the
file you have opened, not linked tables, etc.

Can you give examples of why it's a bad idea?
First off I see almost no reason a front end should ever need a compact.
Second there are cases where compacting messes up the file. For the (more
reasonable) occassional compact I would initiate it manually rather than
blindly doing it every time my app runs.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Oct 30 '08 #8
I wish it never needed a compact. This programs is quite large fully
compacted, and makes extensive use of temp tables, which tends to bloat the
FE significantly. I even tried off-loading the temp tables to a
"workspace.accdb" local file, and it didn't solve the FE bloat. Years ago I
went over everything, and all recordsets are properly closed after they are
used, and the database variable set to nothing, so my theory has always been
the temp tables angle as the reason for the bloat.

I used to put up a message if the FE had bloated 3-4X the normal size, and
normal users would never see the message. Power users, on the other hand,
could get the message after a day or 2 of usage.
"Rick Brandt" <ri*********@hotmail.comwrote in message
news:sz*****************@nlpi065.nbdc.sbc.com...
Andy wrote:
>I appreciate the comment and have read similar comments before,
however, if anything happens to the front-end on the compact, worst
case is you grab a new copy of the FE.

Compact on close has no affect on the BE, it only operates on the
file you have opened, not linked tables, etc.

Can you give examples of why it's a bad idea?

First off I see almost no reason a front end should ever need a compact.
Second there are cases where compacting messes up the file. For the (more
reasonable) occassional compact I would initiate it manually rather than
blindly doing it every time my app runs.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Oct 30 '08 #9
"Andy" <PC*****@PCESoft.invalidwrote in
news:Ba*****************@flpi150.ffdc.sbc.com:
I appreciate the comment and have read similar comments before,
however, if anything happens to the front-end on the compact,
worst case is you grab a new copy of the FE.
Which accounts for the "useless" part of what Rick said.

And given the possibility of corrupting a front end (which shouldn't
ever need to be compacted in the first place), why would you want to
have to deal with helping the end user recover a working copy of the
front end if something goes wrong?

Compact On Close is a stupid feature, as badly implemented as it
could possibly be.

No one, NOT ONE USER, should ever have it turned on in any
circumstances.

But I have no strong opinions on the subject, myself.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 30 '08 #10
"Andy" <PC*****@PCESoft.invalidwrote in
news:6G*****************@flpi150.ffdc.sbc.com:
I wish it never needed a compact.
Why not stop wishing, and instead, design your app properly in the
first place?
This programs is quite large fully
compacted, and makes extensive use of temp tables, which tends to
bloat the FE significantly.
That's why temp tables should never be stored in the front end, but
in an independent temp table. The topic has been discussed
frequently in this and many other Access forums. My approach is to
keep a backup copy of the tmp.mdb named tmp.bak and copy it over top
of tmp.mdb at appropriate times. This instantly produces a compacted
tmp.mdb. Others (Tony Toews is one) use code to create the temp
database on the fly.
I even tried off-loading the temp tables to a
"workspace.accdb" local file, and it didn't solve the FE bloat.
Then you have other design errors in your front end besides storing
temp tables there.
Years ago I
went over everything, and all recordsets are properly closed after
they are used, and the database variable set to nothing, so my
theory has always been the temp tables angle as the reason for the
bloat.
Temp tables don't belong in the front end.
I used to put up a message if the FE had bloated 3-4X the normal
size, and normal users would never see the message. Power users,
on the other hand, could get the message after a day or 2 of
usage.
Fix the design errors causing the bloat. Then there will be no need
for either the brain-dead stupid COMPACT ON CLOSE or to notify the
users to compact.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 30 '08 #11
On 30 Oct 2008 21:08:28 GMT, "David W. Fenton" <XX*******@dfenton.com.invalid>
wrote:
>"Andy" <PC*****@PCESoft.invalidwrote in
news:6G*****************@flpi150.ffdc.sbc.com:
>I wish it never needed a compact.

Why not stop wishing, and instead, design your app properly in the
first place?
>This programs is quite large fully
compacted, and makes extensive use of temp tables, which tends to
bloat the FE significantly.

That's why temp tables should never be stored in the front end, but
in an independent temp table. The topic has been discussed
frequently in this and many other Access forums. My approach is to
keep a backup copy of the tmp.mdb named tmp.bak and copy it over top
of tmp.mdb at appropriate times. This instantly produces a compacted
tmp.mdb. Others (Tony Toews is one) use code to create the temp
database on the fly.
If the On Click event of a Cmd button that closes the database has included in
the code to run a delete query that deletes all the records in a Temp table
will you still get bloat? The table remains, just no data in it.

Chuck
>I even tried off-loading the temp tables to a
"workspace.accdb" local file, and it didn't solve the FE bloat.

Then you have other design errors in your front end besides storing
temp tables there.
> Years ago I
went over everything, and all recordsets are properly closed after
they are used, and the database variable set to nothing, so my
theory has always been the temp tables angle as the reason for the
bloat.

Temp tables don't belong in the front end.
>I used to put up a message if the FE had bloated 3-4X the normal
size, and normal users would never see the message. Power users,
on the other hand, could get the message after a day or 2 of
usage.

Fix the design errors causing the bloat. Then there will be no need
for either the brain-dead stupid COMPACT ON CLOSE or to notify the
users to compact.
Oct 31 '08 #12
"Andy" <PC*****@PCESoft.invalidwrote in message
news:6G*****************@flpi150.ffdc.sbc.com...
makes extensive use of temp tables,
There lies your problem.

Oct 31 '08 #13
"Andy" <PC*****@PCESoft.invalidwrote in message
news:u5**************@flpi144.ffdc.sbc.com...
>
those with large FE/BE applications with 100's of forms, queries, reports,
I would suggest that an app with that magnitude of objects needs a serious
re-think.

Keith.

Oct 31 '08 #14
Not exactly. The program has about 15 main screens and tools and screens
(Customers, Suppliers, Parts, Quotes, Invoices, Payments, Service,
Purchases, Reports, Recurring Billing, etc., most selection lists are
customizable, so each has their own 'edit/add' form, then there's popup
screens for options, confirmations, etc. Screens for mass updating parts,
importing parts, customers, suppliers, e-mailing, etc. Queries that drive
all the screens, reports, etc. Roughly 90 reports. Extensive use of VBA.
It's basically an app that will run a person's business. It's just a big,
comprehensive app.

"Keith Wilby" <he**@there.comwrote in message
news:49**********@glkas0286.greenlnk.net...
"Andy" <PC*****@PCESoft.invalidwrote in message
news:u5**************@flpi144.ffdc.sbc.com...
>>
those with large FE/BE applications with 100's of forms, queries,
reports,

I would suggest that an app with that magnitude of objects needs a serious
re-think.

Keith.
Oct 31 '08 #15
Chuck <li*****@schoollink.netwrote in
news:4h********************************@4ax.com:
On 30 Oct 2008 21:08:28 GMT, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:
>>"Andy" <PC*****@PCESoft.invalidwrote in
news:6G*****************@flpi150.ffdc.sbc.com:
>>I wish it never needed a compact.

Why not stop wishing, and instead, design your app properly in the
first place?
>>This programs is quite large fully
compacted, and makes extensive use of temp tables, which tends
to bloat the FE significantly.

That's why temp tables should never be stored in the front end,
but in an independent temp table. The topic has been discussed
frequently in this and many other Access forums. My approach is to
keep a backup copy of the tmp.mdb named tmp.bak and copy it over
top of tmp.mdb at appropriate times. This instantly produces a
compacted tmp.mdb. Others (Tony Toews is one) use code to create
the temp database on the fly.
If the On Click event of a Cmd button that closes the database has
included in the code to run a delete query that deletes all the
records in a Temp table will you still get bloat? The table
remains, just no data in it.
Of course you will, because the delete only marks the data pages the
records occupied as unused -- it doesn't actually recover them.
Those pages might be re-used later (since they are marked as unused,
they can be re-used by other data), but it will definitey cause
bloat.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 31 '08 #16
"Andy" <PC*****@PCESoft.invalidwrote in
news:_p****************@flpi144.ffdc.sbc.com:
I just recently changed my post name
from ARC to Andy
Aha! You've emerged from my killfile!

Well, thanks for admitting it, so I can put you back there.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 31 '08 #17
lyle fairfield <ly******@yah00.cawrote in
news:Xn**********************@216.221.81.119:
I've taken the disapproval of MVPs and other experienced
developers for "Compact On Close" to be just another one of the
superstitions they promulgate
Obviously, you've never encountered a situation where a previously
readable MDB in a suspect state lost data after a compact. I've
experienced that and it's not predictable. Because of that, I don't
think taking the risk of COMPACT ON CLOSE is worth it.

Secondly, it accomplishes nothing worthwhile in a properly designed
app (i.e., split), because it's compacting something that really has
not need to be compacted at all (the front end), and certainly not
on a daily basis.

Now, if it had been designed differently, I would feel differently.
Specifically, it if automatically backed up your file before the
compact, or if it could be configured to prompt you before it
compacted, then I'd say it was useful.

On the other hand, both of these features are something that are
quite easily programmed for your own app if you need to compact on a
regular basis, so I don't see why anyone should put up with the
danger (and annoyance) of the feature that MS chose to implement.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 31 '08 #18
On Oct 31, 5:21*pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
"Andy" <PCES...@PCESoft.invalidwrote innews:_p****************@flpi144.ffdc.sbc.com:
Aha! You've emerged from my killfile!
Well, thanks for admitting it, so I can put you back there.
And don't go thinking you're special because David has kill-filed you
twice, Andy. You still have 17 times to go before you catch up to me!
Oct 31 '08 #19
lol!! You're all right, Lyle. Hey, I'd really like to take a close look at
where I'm using temp tables, and see if there are alternatives. In my last
post, I listed a handful of areas where I'm using them, and was wondering if
there are alternatives. I can certainly post more examples of where I use
temp tables.

Thanks! Have a good Halloween.

And, David, I see your 4 posts, which I'll read shortly. Maybe I'll be
catching up to Lyle??

Thanks all,

"lyle fairfield" <ly************@gmail.comwrote in message
news:fa**********************************@f37g2000 pri.googlegroups.com...
On Oct 31, 5:21 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
"Andy" <PCES...@PCESoft.invalidwrote
innews:_p****************@flpi144.ffdc.sbc.com:
Aha! You've emerged from my killfile!
Well, thanks for admitting it, so I can put you back there.
And don't go thinking you're special because David has kill-filed you
twice, Andy. You still have 17 times to go before you catch up to me!

Oct 31 '08 #20
I don't think compacting the program .mde / .accde FE on close would cause
corruption in the BE database, would it? It won't compact the BE database,
that's one thing that's certain. I'm definitely not considering compacting
the BE on close, the only interest is ensuring the smallest, most optimized
program file possible. Another thing I've often wondered is flaky things
bloating the FE, like use of an active x, tree view control, etc. I'm going
to do some serious testing to see if I can pin-point if there's a certain
screen that causes the bloating over others.

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@74.209.1 36.93...
lyle fairfield <ly******@yah00.cawrote in
news:Xn**********************@216.221.81.119:
>I've taken the disapproval of MVPs and other experienced
developers for "Compact On Close" to be just another one of the
superstitions they promulgate

Obviously, you've never encountered a situation where a previously
readable MDB in a suspect state lost data after a compact. I've
experienced that and it's not predictable. Because of that, I don't
think taking the risk of COMPACT ON CLOSE is worth it.

Secondly, it accomplishes nothing worthwhile in a properly designed
app (i.e., split), because it's compacting something that really has
not need to be compacted at all (the front end), and certainly not
on a daily basis.

Now, if it had been designed differently, I would feel differently.
Specifically, it if automatically backed up your file before the
compact, or if it could be configured to prompt you before it
compacted, then I'd say it was useful.

On the other hand, both of these features are something that are
quite easily programmed for your own app if you need to compact on a
regular basis, so I don't see why anyone should put up with the
danger (and annoyance) of the feature that MS chose to implement.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 31 '08 #21
Darn, now I have to work on getting windows mail to generate a random "from
name"... work never ends... :(
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@74.209.1 36.93...
"Andy" <PC*****@PCESoft.invalidwrote in
news:_p****************@flpi144.ffdc.sbc.com:
>I just recently changed my post name
from ARC to Andy

Aha! You've emerged from my killfile!

Well, thanks for admitting it, so I can put you back there.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 31 '08 #22
"Keith Wilby" <he**@there.comwrote:
>those with large FE/BE applications with 100's of forms, queries, reports,

I would suggest that an app with that magnitude of objects needs a serious
re-think.
Not so. My Granite Fleet Manager has 150 queries, 110 forms and 30 reports for a
total of about 280 objects. And I've got lots more functionality to add so I can
see that doubling in size.

The biggest app I've built had 1200 queries, 450 forms and 350 reports.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Nov 1 '08 #23
"Andy" <PC*****@PCESoft.invalidwrote:
>Hmmmm...interesting. I do make changes to the forms on the fly when they
load. I have a routine, "SetCurrencyandDates" that goes through a form, and
sets the proper date format that the user selects in the Setup screen (3
letter month, vs. 2 number month designation), and set's currency fields to
the proper regional setting.
Ahh, can you do that once when the new FE is installed on the users system?
>Now this is what I call constructive stuff.
Yes, well sometimes David does get a bit irritable..

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Nov 1 '08 #24
"Andy" <PC*****@PCESoft.invalidwrote:
>Maybe my approach isn't sound on 1 & 2?
I agree with having separate temp MDB/ACCDB for 1 & 2.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Nov 1 '08 #25
"Andy" <PC*****@PCESoft.invalidwrote:
>Darn, now I have to work on getting windows mail to generate a random "from
name"... work never ends... :(
<chuckle>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Nov 1 '08 #26
"Andy" <PC*****@PCESoft.invalidwrote in
news:m1****************@nlpi064.nbdc.sbc.com:
Hmmmm...interesting. I do make changes to the forms on the fly
when they load. I have a routine, "SetCurrencyandDates" that goes
through a form, and sets the proper date format that the user
selects in the Setup screen (3 letter month, vs. 2 number month
designation), and set's currency fields to the proper regional
setting. I found years ago, that if you just set a field for
Currency, it would show the US dollar in England, for whatever
reason. So on the form's load or open (on format for reports),
setting the field to currency then properly uses the local
currency symbol. This could be fixed in Access 2007, but the
legacy code is still there
You're doing this in design view? Why? Why not just change the
runtime formats?
I am offering a "Disable compact on close" option, just in the
event it causes trouble. In my testing, setting a threshold on the
size of the program .accde, then only firing the compact on close
if it exceeds, seems to work like a charm.
I'd replace that with my own code, code that prompted the user and
that made a backup before compacting.
Now this is what I call constructive stuff. I should comment out
the SetCurrencyandDates function that runs when a form is opened
and see if that has an affect on bloat (since I've already
off-loaded the big / frequently used temp tables to a separate
database).
I see no reason to make those changes to the form design and saving
them. Making them at runtime in the form's properties (each time the
form opens) should not cause bloat, so if that's what you're doing,
then it's clearly not contributing to bloat.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Nov 1 '08 #27
I'm not changing the form's design at runtime, however, I am cycling through
all controls in the on-open event of the form, and running the code shown
below:

I'm not saving changes to the form, just simply changing the .format in some
cases (for date fields), the .visible property for optional features that
they turn off (that's the whole SStrng business below), and finally there's
a function where user's can translate the on-screen labels to a different
language, or different usage (that's the last bit with the CheckTranslate,
etc.)

But I am Not editing the form in design mode and saving, just simply setting
some control prop's in the on-open.

Interestingly enough, when working on this program, Access 2007 things the
below are design changes, so frequently, when I'm testing the forms, and
close them, I'll get a prompt to save changes if there are subforms. Now in
Access 97, for those in the UK, Australia, Canada, etc., I found I had to
set the format to their settings with month showing second, otherwise the US
date format of month first would show. I also give the option of showing
dates as the 3-letter month abbreviation vs. 2 number months.

-------------------------------

For Each ctl In f.Controls
If Not IsNull(f(ctl.Name).Tag) Then
For i = 1 To 13 'turns off optional features
If InStr(1, f(ctl.Name).Tag, SStrng(i), 1) 0 Then
f(ctl.Name).visible = 0
End If
Next i
End If
If ctl.ControlType = acTextBox Then
If f(ctl.Name).Format = "Short Date" Or f(ctl.Name).Format =
"mmm-dd-yyyy" Or f(ctl.Name).Format = "m/d/yyyy" Then
If nomask = 0 Then f(ctl.Name).InputMask = f2!DateInputMask
f(ctl.Name).Format = f2!DateStyle
End If
ElseIf ctl.ControlType = acLabel Or ctl.ControlType = acCommandButton
Then
If TranslateYN = -1 And f(ctl.Name).visible = -1 Then
CurrCap = f(ctl.Name).Caption
criteria = "lblPhrase = " & Chr(34) & CurrCap & Chr(34)
f(ctl.Name).Caption = CheckTranslateDb(rs, criteria, CurrCap)
End If

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@74.209.1 36.93...
"Andy" <PC*****@PCESoft.invalidwrote in
news:m1****************@nlpi064.nbdc.sbc.com:
>Hmmmm...interesting. I do make changes to the forms on the fly
when they load. I have a routine, "SetCurrencyandDates" that goes
through a form, and sets the proper date format that the user
selects in the Setup screen (3 letter month, vs. 2 number month
designation), and set's currency fields to the proper regional
setting. I found years ago, that if you just set a field for
Currency, it would show the US dollar in England, for whatever
reason. So on the form's load or open (on format for reports),
setting the field to currency then properly uses the local
currency symbol. This could be fixed in Access 2007, but the
legacy code is still there

You're doing this in design view? Why? Why not just change the
runtime formats?
>I am offering a "Disable compact on close" option, just in the
event it causes trouble. In my testing, setting a threshold on the
size of the program .accde, then only firing the compact on close
if it exceeds, seems to work like a charm.

I'd replace that with my own code, code that prompted the user and
that made a backup before compacting.
>Now this is what I call constructive stuff. I should comment out
the SetCurrencyandDates function that runs when a form is opened
and see if that has an affect on bloat (since I've already
off-loaded the big / frequently used temp tables to a separate
database).

I see no reason to make those changes to the form design and saving
them. Making them at runtime in the form's properties (each time the
form opens) should not cause bloat, so if that's what you're doing,
then it's clearly not contributing to bloat.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Nov 2 '08 #28
Thanks, Tony!

It is an .accde, so at that point, I'm not sure I go through the forms and
make permanent changes, nor sure how & where I would do this, or if it is
even adding to any of the bloat (again, I'd have to comment out the proc to
see if it's having any affect).

I keep thinking the history (invoices / payments) might be one of the key
areas causing the bloat, btw. I dump all payments and all invoices to a
tHistory table, then run reports and the payments/history screen off that. I
really need to look into re-working anything dealing with tHistory as a
Union query (invoices / payments table). tHistory is in a temporary,
separate database other than the FE, btw, but I'm not convinced that the FE
still doesn't need to set aside space to run the results of appending to
tHistory.

"Tony Toews [MVP]" <tt****@telusplanet.netwrote in message
news:19********************************@4ax.com...
"Andy" <PC*****@PCESoft.invalidwrote:
>>Hmmmm...interesting. I do make changes to the forms on the fly when they
load. I have a routine, "SetCurrencyandDates" that goes through a form,
and
sets the proper date format that the user selects in the Setup screen (3
letter month, vs. 2 number month designation), and set's currency fields
to
the proper regional setting.

Ahh, can you do that once when the new FE is installed on the users
system?
>>Now this is what I call constructive stuff.

Yes, well sometimes David does get a bit irritable..

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Nov 2 '08 #29
"Andy" <PC*****@PCESoft.invalidwrote:
>It is an .accde, so at that point, I'm not sure I go through the forms and
make permanent changes, nor sure how & where I would do this, or if it is
even adding to any of the bloat (again, I'd have to comment out the proc to
see if it's having any affect).
Ah, ok. If it's an mde/accde then it's very unlikely you are updating the forms.
>I keep thinking the history (invoices / payments) might be one of the key
areas causing the bloat, btw. I dump all payments and all invoices to a
tHistory table, then run reports and the payments/history screen off that. I
really need to look into re-working anything dealing with tHistory as a
Union query (invoices / payments table). tHistory is in a temporary,
separate database other than the FE, btw, but I'm not convinced that the FE
still doesn't need to set aside space to run the results of appending to
tHistory.
I'm not sure I understand your question here. If the histry table is in a temp
MDB/ACCDB then I don't see it adding much to your bloat.

BTW there might be 4 kb of bloat each time you relink to a table. I vaguelly recall
seeing this one time but mostly ignored it.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Nov 3 '08 #30
"Andy" <PC*****@PCESoft.invalidwrote in message
news:hs*****************@flpi150.ffdc.sbc.com...
Not exactly. The program has about 15 main screens and tools and screens
(Customers, Suppliers, Parts, Quotes, Invoices, Payments, Service,
Purchases, Reports, Recurring Billing, etc., most selection lists are
customizable, so each has their own 'edit/add' form, then there's popup
screens for options, confirmations, etc. Screens for mass updating parts,
importing parts, customers, suppliers, e-mailing, etc. Queries that drive
all the screens, reports, etc. Roughly 90 reports. Extensive use of VBA.
It's basically an app that will run a person's business. It's just a big,
comprehensive app.
Fair enough Andy. The "apps" I've encountered that have so many objects are
usually full of (for example) duplicate form and reports with different
record sources. Plus I was grumpy on Friday :)

Nov 3 '08 #31
No worries... Take care,

Andy
"Keith Wilby" <he**@there.comwrote in message
news:49**********@glkas0286.greenlnk.net...
"Andy" <PC*****@PCESoft.invalidwrote in message
news:hs*****************@flpi150.ffdc.sbc.com...
>Not exactly. The program has about 15 main screens and tools and screens
(Customers, Suppliers, Parts, Quotes, Invoices, Payments, Service,
Purchases, Reports, Recurring Billing, etc., most selection lists are
customizable, so each has their own 'edit/add' form, then there's popup
screens for options, confirmations, etc. Screens for mass updating parts,
importing parts, customers, suppliers, e-mailing, etc. Queries that
drive all the screens, reports, etc. Roughly 90 reports. Extensive use of
VBA. It's basically an app that will run a person's business. It's just a
big, comprehensive app.

Fair enough Andy. The "apps" I've encountered that have so many objects
are usually full of (for example) duplicate form and reports with
different record sources. Plus I was grumpy on Friday :)
Nov 3 '08 #32
You understood it correctly, as it is a temp table in a different /
workspace type database.

I didn't know that about relinking. I really don't relink the tables unless
the user initiates it, or if they have just loaded a new version and the
links are no longe valid.

Thanks again,

Andy
"Tony Toews [MVP]" <tt****@telusplanet.netwrote in message
news:9k********************************@4ax.com...
"Andy" <PC*****@PCESoft.invalidwrote:
>>It is an .accde, so at that point, I'm not sure I go through the forms and
make permanent changes, nor sure how & where I would do this, or if it is
even adding to any of the bloat (again, I'd have to comment out the proc
to
see if it's having any affect).

Ah, ok. If it's an mde/accde then it's very unlikely you are updating the
forms.
>>I keep thinking the history (invoices / payments) might be one of the key
areas causing the bloat, btw. I dump all payments and all invoices to a
tHistory table, then run reports and the payments/history screen off that.
I
really need to look into re-working anything dealing with tHistory as a
Union query (invoices / payments table). tHistory is in a temporary,
separate database other than the FE, btw, but I'm not convinced that the
FE
still doesn't need to set aside space to run the results of appending to
tHistory.

I'm not sure I understand your question here. If the histry table is in
a temp
MDB/ACCDB then I don't see it adding much to your bloat.

BTW there might be 4 kb of bloat each time you relink to a table. I
vaguelly recall
seeing this one time but mostly ignored it.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Nov 3 '08 #33

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: ColinWard | last post by:
Hi I have a database that I would like to be able to compact when the exit button on the main form is clicked. Can anyone help with this? TIA Colin *** Sent via Developersdex...
1
by: Rene Crespo | last post by:
Hello, Following my earlier posting regarding my Error On Exit: --------------------------------------------------------- I get the following error when compacting my MSAccess 2000 database: ...
5
by: Peter Verhelst | last post by:
Hello, Currently I am running an Access database on a windows WTS server, running Microsoft Windows 2000 server 5.00.2195 Service Pack 3. This database should be accessible for everybody in our...
5
by: lappy | last post by:
Hello, I have written a small programme to compact an access 97 database. Dim je As New JRO.JetEngine ' Compacts database Data.Mdb to Data2.mdb. je.CompactDatabase...
11
by: darcykahle | last post by:
I have stumbled upon an interesting issue with regard to compaction of an access frontend database which resides on a DFS target that utilizes FRS to mirror the sharepoint, and I was wondering if...
3
by: Pachydermitis | last post by:
Hi everyone, This is not the forum for Windows permissions, but I figure this has to be an issue one of you access experts has had to deal with. I have an XP system with an access db on it. The...
7
by: DavidB | last post by:
I have a standard FE/BE database. I'd like to be able to have a button on each db that allows the admin to compact the database. I am using the following code to perform the compact. Private...
1
by: Slez via AccessMonster.com | last post by:
I do a routine compact and repair on a back end MS Access 2002 database. This time I had something peculiar occur. The compact started as normal, than the following error message popped up (I...
3
by: DSSMaster | last post by:
I am trying to use windows scheduler to compact a database. In the task run line I have the following: "C:\Program Files\Microsoft Office\OFFICE11 MSACCESS.EXE" _ "C:\Files\Access\Sacroc.mdb"...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.