Code For Compact On Close?

I would like to compact on close only if the database size goes over a
certain amount, rather than each time. Thus, I'd like to check the file size
and then perform the compact through code as the mdb's closing. Is that
possible?

I suppose one option would be to set the Compact On Close option in the
switchboard's On Close event, and then clear it whenever the database is
opened. That would probably work. But I'd prefer a cleaner solution, where I
can just call the compact function as the database is closing (I'm guessing
this isn't possible...).

Thanks.

Re: Code For Compact On Close?

Hi

Someone gave me this function:

Public Function AutoCompactApplication()

Dim s
Dim strProjectPath As String, strProjectName As String

strProjectPath = Application.CurrentProject.Path
strProjectName = Application.CurrentProject.Name
filespec = strProjectPath & "\" & strProjectName

s = CLng(FileLen(filespec) / 1000000) 'convert size

If s > 20 Then 'edit the 20 (Mb's) to the max size you want to allow
your app to grow.

Application.SetOption ("Auto Compact"), 1 'compact app

Else

Application.SetOption ("Auto Compact"), 0 'no don't compact app

End If

End Function

Call the function when your DB closes down.


Neil wrote:
>I would like to compact on close only if the database size goes over a
>certain amount, rather than each time. Thus, I'd like to check the file size
>and then perform the compact through code as the mdb's closing. Is that
>possible?
>
>I suppose one option would be to set the Compact On Close option in the
>switchboard's On Close event, and then clear it whenever the database is
>opened. That would probably work. But I'd prefer a cleaner solution, where I
>can just call the compact function as the database is closing (I'm guessing
>this isn't possible...).
>
>Thanks.

--
Message posted via http://www.accessmonster.com

Re: Code For Compact On Close?

suggest you declare the variable "s" as a Long data type, as

Dim s As Long

leaving it undeclared means it is the default data type Variant, which is
less effecient.

hth


"biganthony via AccessMonster.com" wrote in message
news:7a5ae5bec164f@uwe...
> Hi
>
> Someone gave me this function:
>
> Public Function AutoCompactApplication()
>
> Dim s
> Dim strProjectPath As String, strProjectName As String
>
> strProjectPath = Application.CurrentProject.Path
> strProjectName = Application.CurrentProject.Name
> filespec = strProjectPath & "\" & strProjectName
>
> s = CLng(FileLen(filespec) / 1000000) 'convert size
>
> If s > 20 Then 'edit the 20 (Mb's) to the max size you want to allow
> your app to grow.
>
> Application.SetOption ("Auto Compact"), 1 'compact app
>
> Else
>
> Application.SetOption ("Auto Compact"), 0 'no don't compact app
>
> End If
>
> End Function
>
> Call the function when your DB closes down.
>
>
> Neil wrote:
> >I would like to compact on close only if the database size goes over a
> >certain amount, rather than each time. Thus, I'd like to check the file
size
> >and then perform the compact through code as the mdb's closing. Is that
> >possible?
> >
> >I suppose one option would be to set the Compact On Close option in the
> >switchboard's On Close event, and then clear it whenever the database is
> >opened. That would probably work. But I'd prefer a cleaner solution,
where I
> >can just call the compact function as the database is closing (I'm
guessing
> >this isn't possible...).
> >
> >Thanks.
>
> --
> Message posted via http://www.accessmonster.com
>

Re: Code For Compact On Close?

answered in comp.databases.ms-access.


"Neil" wrote in message
news:PZTUi.1252$yV6.390@newssvr25.news.prodigy.net...
> I would like to compact on close only if the database size goes over a
> certain amount, rather than each time. Thus, I'd like to check the file
size
> and then perform the compact through code as the mdb's closing. Is that
> possible?
>
> I suppose one option would be to set the Compact On Close option in the
> switchboard's On Close event, and then clear it whenever the database is
> opened. That would probably work. But I'd prefer a cleaner solution, where
I
> can just call the compact function as the database is closing (I'm
guessing
> this isn't possible...).
>
> Thanks.
>
>

Re: Code For Compact On Close?

Thanks.

I wonder why not just use currentdb.name for filespec?


"biganthony via AccessMonster.com" wrote in message
news:7a5ae5bec164f@uwe...
> Hi
>
> Someone gave me this function:
>
> Public Function AutoCompactApplication()
>
> Dim s
> Dim strProjectPath As String, strProjectName As String
>
> strProjectPath = Application.CurrentProject.Path
> strProjectName = Application.CurrentProject.Name
> filespec = strProjectPath & "\" & strProjectName
>
> s = CLng(FileLen(filespec) / 1000000) 'convert size
>
> If s > 20 Then 'edit the 20 (Mb's) to the max size you want to allow
> your app to grow.
>
> Application.SetOption ("Auto Compact"), 1 'compact app
>
> Else
>
> Application.SetOption ("Auto Compact"), 0 'no don't compact app
>
> End If
>
> End Function
>
> Call the function when your DB closes down.
>
>
> Neil wrote:
>>I would like to compact on close only if the database size goes over a
>>certain amount, rather than each time. Thus, I'd like to check the file
>>size
>>and then perform the compact through code as the mdb's closing. Is that
>>possible?
>>
>>I suppose one option would be to set the Compact On Close option in the
>>switchboard's On Close event, and then clear it whenever the database is
>>opened. That would probably work. But I'd prefer a cleaner solution, where
>>I
>>can just call the compact function as the database is closing (I'm
>>guessing
>>this isn't possible...).
>>
>>Thanks.
>
> --
> Message posted via http://www.accessmonster.com
>

Re: Code For Compact On Close?

To set the Compact on Close option, you must deal with menu options, but you
can use the following to compact during the close event of the last form
open.

http://www.mvps.org/access/general/gen0041.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Neil" wrote in message
news:PZTUi.1252$yV6.390@newssvr25.news.prodigy.net...
>I would like to compact on close only if the database size goes over a
>certain amount, rather than each time. Thus, I'd like to check the file
>size and then perform the compact through code as the mdb's closing. Is
>that possible?
>
> I suppose one option would be to set the Compact On Close option in the
> switchboard's On Close event, and then clear it whenever the database is
> opened. That would probably work. But I'd prefer a cleaner solution, where
> I can just call the compact function as the database is closing (I'm
> guessing this isn't possible...).
>
> Thanks.
>

Re: Code For Compact On Close?

Thanks! That's good to know. I'd never seen the menu bars accessed that way,
so that's good to know too. (And a very creative use of the line break
character as well! :-) ) Thanks!

"Arvin Meyer [MVP]" wrote in message
news:%23F4Hu%23YGIHA.4628@TK2MSFTNGP02.phx.gbl...
> To set the Compact on Close option, you must deal with menu options, but
> you can use the following to compact during the close event of the last
> form open.
>
> http://www.mvps.org/access/general/gen0041.htm
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
> "Neil" wrote in message
> news:PZTUi.1252$yV6.390@newssvr25.news.prodigy.net...
>>I would like to compact on close only if the database size goes over a
>>certain amount, rather than each time. Thus, I'd like to check the file
>>size and then perform the compact through code as the mdb's closing. Is
>>that possible?
>>
>> I suppose one option would be to set the Compact On Close option in the
>> switchboard's On Close event, and then clear it whenever the database is
>> opened. That would probably work. But I'd prefer a cleaner solution,
>> where I can just call the compact function as the database is closing
>> (I'm guessing this isn't possible...).
>>
>> Thanks.
>>
>
>

Re: Code For Compact On Close?

Thanks again, Arvin. Now I need one for setting the Break On Unhandled
Errors option in VBA Tools | Options? Do you know of anything? Thanks!

Neil


"Arvin Meyer [MVP]" wrote in message
news:%23F4Hu%23YGIHA.4628@TK2MSFTNGP02.phx.gbl...
> To set the Compact on Close option, you must deal with menu options, but
> you can use the following to compact during the close event of the last
> form open.
>
> http://www.mvps.org/access/general/gen0041.htm
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
> "Neil" wrote in message
> news:PZTUi.1252$yV6.390@newssvr25.news.prodigy.net...
>>I would like to compact on close only if the database size goes over a
>>certain amount, rather than each time. Thus, I'd like to check the file
>>size and then perform the compact through code as the mdb's closing. Is
>>that possible?
>>
>> I suppose one option would be to set the Compact On Close option in the
>> switchboard's On Close event, and then clear it whenever the database is
>> opened. That would probably work. But I'd prefer a cleaner solution,
>> where I can just call the compact function as the database is closing
>> (I'm guessing this isn't possible...).
>>
>> Thanks.
>>
>
>

Re: Code For Compact On Close?

answered in thread "Code for Break on Unhandled Errors", dated 10/31/07, in
this newsgroup.


"Neil" wrote in message
news:p7aWi.13094$4V6.11033@newssvr14.news.prodigy.net...
> Thanks again, Arvin. Now I need one for setting the Break On Unhandled
> Errors option in VBA Tools | Options? Do you know of anything? Thanks!
>
> Neil
>
>
> "Arvin Meyer [MVP]" wrote in message
> news:%23F4Hu%23YGIHA.4628@TK2MSFTNGP02.phx.gbl...
> > To set the Compact on Close option, you must deal with menu options, but
> > you can use the following to compact during the close event of the last
> > form open.
> >
> > http://www.mvps.org/access/general/gen0041.htm
> > --
> > Arvin Meyer, MCP, MVP
> > http://www.datastrat.com
> > http://www.mvps.org/access
> > http://www.accessmvp.com
> >
> > "Neil" wrote in message
> > news:PZTUi.1252$yV6.390@newssvr25.news.prodigy.net...
> >>I would like to compact on close only if the database size goes over a
> >>certain amount, rather than each time. Thus, I'd like to check the file
> >>size and then perform the compact through code as the mdb's closing. Is
> >>that possible?
> >>
> >> I suppose one option would be to set the Compact On Close option in the
> >> switchboard's On Close event, and then clear it whenever the database
is
> >> opened. That would probably work. But I'd prefer a cleaner solution,
> >> where I can just call the compact function as the database is closing
> >> (I'm guessing this isn't possible...).
> >>
> >> Thanks.
> >>
> >
> >
>
>

Re: Code For Compact On Close?

If only I'd waited another hour!... Thanks for your reply there (and here!).

"tina" wrote in message
news:dwbWi.310118$ax1.2938@bgtnsc05-news.ops.worldnet.att.ne t...
> answered in thread "Code for Break on Unhandled Errors", dated 10/31/07,
> in
> this newsgroup.
>
>
> "Neil" wrote in message
> news:p7aWi.13094$4V6.11033@newssvr14.news.prodigy.net...
>> Thanks again, Arvin. Now I need one for setting the Break On Unhandled
>> Errors option in VBA Tools | Options? Do you know of anything? Thanks!
>>
>> Neil
>>
>>
>> "Arvin Meyer [MVP]" wrote in message
>> news:%23F4Hu%23YGIHA.4628@TK2MSFTNGP02.phx.gbl...
>> > To set the Compact on Close option, you must deal with menu options,
>> > but
>> > you can use the following to compact during the close event of the last
>> > form open.
>> >
>> > http://www.mvps.org/access/general/gen0041.htm
>> > --
>> > Arvin Meyer, MCP, MVP
>> > http://www.datastrat.com
>> > http://www.mvps.org/access
>> > http://www.accessmvp.com
>> >
>> > "Neil" wrote in message
>> > news:PZTUi.1252$yV6.390@newssvr25.news.prodigy.net...
>> >>I would like to compact on close only if the database size goes over a
>> >>certain amount, rather than each time. Thus, I'd like to check the file
>> >>size and then perform the compact through code as the mdb's closing. Is
>> >>that possible?
>> >>
>> >> I suppose one option would be to set the Compact On Close option in
>> >> the
>> >> switchboard's On Close event, and then clear it whenever the database
> is
>> >> opened. That would probably work. But I'd prefer a cleaner solution,
>> >> where I can just call the compact function as the database is closing
>> >> (I'm guessing this isn't possible...).
>> >>
>> >> Thanks.
>> >>
>> >
>> >
>>
>>
>
>

Re: Code For Compact On Close?

you're welcome. and btw, my previous post was not a scolding of any kind; i
posted it for the benefit of others who might read this thread.


"Neil" wrote in message
news:UedWi.3325$%Y6.799@nlpi061.nbdc.sbc.com...
> If only I'd waited another hour!... Thanks for your reply there (and
here!).
>
> "tina" wrote in message
> news:dwbWi.310118$ax1.2938@bgtnsc05-news.ops.worldnet.att.ne t...
> > answered in thread "Code for Break on Unhandled Errors", dated 10/31/07,
> > in
> > this newsgroup.
> >
> >
> > "Neil" wrote in message
> > news:p7aWi.13094$4V6.11033@newssvr14.news.prodigy.net...
> >> Thanks again, Arvin. Now I need one for setting the Break On Unhandled
> >> Errors option in VBA Tools | Options? Do you know of anything? Thanks!
> >>
> >> Neil
> >>
> >>
> >> "Arvin Meyer [MVP]" wrote in message
> >> news:%23F4Hu%23YGIHA.4628@TK2MSFTNGP02.phx.gbl...
> >> > To set the Compact on Close option, you must deal with menu options,
> >> > but
> >> > you can use the following to compact during the close event of the
last
> >> > form open.
> >> >
> >> > http://www.mvps.org/access/general/gen0041.htm
> >> > --
> >> > Arvin Meyer, MCP, MVP
> >> > http://www.datastrat.com
> >> > http://www.mvps.org/access
> >> > http://www.accessmvp.com
> >> >
> >> > "Neil" wrote in message
> >> > news:PZTUi.1252$yV6.390@newssvr25.news.prodigy.net...
> >> >>I would like to compact on close only if the database size goes over
a
> >> >>certain amount, rather than each time. Thus, I'd like to check the
file
> >> >>size and then perform the compact through code as the mdb's closing.
Is
> >> >>that possible?
> >> >>
> >> >> I suppose one option would be to set the Compact On Close option in
> >> >> the
> >> >> switchboard's On Close event, and then clear it whenever the
database
> > is
> >> >> opened. That would probably work. But I'd prefer a cleaner solution,
> >> >> where I can just call the compact function as the database is
closing
> >> >> (I'm guessing this isn't possible...).
> >> >>
> >> >> Thanks.
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Re: Code For Compact On Close?

On Oct 28, 12:11 am, "Neil" wrote:
> I would like to compact on close only if the database size goes over a
> certain amount, rather than each time. Thus, I'd like to check the file size
> and then perform the compact through code as the mdb's closing. Is that
> possible?
>
> I suppose one option would be to set the Compact On Close option in the
> switchboard's On Close event, and then clear it whenever the database is
> opened. That would probably work. But I'd prefer a cleaner solution, where I
> can just call the compact function as the database is closing (I'm guessing
> this isn't possible...).

Perhaps, for beginners it's worthwhile ot point out why (IMO) this
capability (check size and if big, compact) isn't built in to Access.
Most enterprise Access applications are built on the front-end, back-
end model. I expect others might disagree, but in my experience a well
designed front-end will not grow in size, because no data is held
there, and no operations that require temporary data objects will run
there. So the front-end will need compacting no more often than the
user has some spare time and says, "Gee, maybe I should compact this
thing." The back end can be compacted regularly; we only have to
ascertain that no one has opened it exclusivley before we do so; this
can be built into code rather easily.

Re: Code For Compact On Close?

I completely disagree with you. One of the advantages of using an Access MDB
file over, say, an ADP, is the ability to use temporary tables for report
generation and other ad-hoc uses.

Also, some implementations of client-server apps using an MDB front end will
copy static lookup tables from the server machine when the database is
opened, and then use those local tables for lookup, instead of making calls
across the network for lookup tables.

So I disagree that "a well designed front-end will not grow in size."


"lyle" wrote in message
news:1194006436.839478.206280@19g2000hsx.googlegroups.com...
> On Oct 28, 12:11 am, "Neil" wrote:
>> I would like to compact on close only if the database size goes over a
>> certain amount, rather than each time. Thus, I'd like to check the file
>> size
>> and then perform the compact through code as the mdb's closing. Is that
>> possible?
>>
>> I suppose one option would be to set the Compact On Close option in the
>> switchboard's On Close event, and then clear it whenever the database is
>> opened. That would probably work. But I'd prefer a cleaner solution,
>> where I
>> can just call the compact function as the database is closing (I'm
>> guessing
>> this isn't possible...).
>
> Perhaps, for beginners it's worthwhile ot point out why (IMO) this
> capability (check size and if big, compact) isn't built in to Access.
> Most enterprise Access applications are built on the front-end, back-
> end model. I expect others might disagree, but in my experience a well
> designed front-end will not grow in size, because no data is held
> there, and no operations that require temporary data objects will run
> there. So the front-end will need compacting no more often than the
> user has some spare time and says, "Gee, maybe I should compact this
> thing." The back end can be compacted regularly; we only have to
> ascertain that no one has opened it exclusivley before we do so; this
> can be built into code rather easily.
>
>

Re: Code For Compact On Close?

If you're using temporary tables in an MDB, you should put them in a
temporary database so that the front-end doesn't increase significantly in
size.

See the example Tony Toews has at
http://www.granite.ab.ca/access/temptables.htm

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Neil" wrote in message
news:hYTWi.3008$%Z2.2544@nlpi068.nbdc.sbc.com...
>I completely disagree with you. One of the advantages of using an Access
>MDB file over, say, an ADP, is the ability to use temporary tables for
>report generation and other ad-hoc uses.
>
> Also, some implementations of client-server apps using an MDB front end
> will copy static lookup tables from the server machine when the database
> is opened, and then use those local tables for lookup, instead of making
> calls across the network for lookup tables.
>
> So I disagree that "a well designed front-end will not grow in size."
>
>
> "lyle" wrote in message
> news:1194006436.839478.206280@19g2000hsx.googlegroups.com...
>> On Oct 28, 12:11 am, "Neil" wrote:
>>> I would like to compact on close only if the database size goes over a
>>> certain amount, rather than each time. Thus, I'd like to check the file
>>> size
>>> and then perform the compact through code as the mdb's closing. Is that
>>> possible?
>>>
>>> I suppose one option would be to set the Compact On Close option in the
>>> switchboard's On Close event, and then clear it whenever the database is
>>> opened. That would probably work. But I'd prefer a cleaner solution,
>>> where I
>>> can just call the compact function as the database is closing (I'm
>>> guessing
>>> this isn't possible...).
>>
>> Perhaps, for beginners it's worthwhile ot point out why (IMO) this
>> capability (check size and if big, compact) isn't built in to Access.
>> Most enterprise Access applications are built on the front-end, back-
>> end model. I expect others might disagree, but in my experience a well
>> designed front-end will not grow in size, because no data is held
>> there, and no operations that require temporary data objects will run
>> there. So the front-end will need compacting no more often than the
>> user has some spare time and says, "Gee, maybe I should compact this
>> thing." The back end can be compacted regularly; we only have to
>> ascertain that no one has opened it exclusivley before we do so; this
>> can be built into code rather easily.
>>
>>
>
>

Re: Code For Compact On Close?

lyle wrote:

>Most enterprise Access applications are built on the front-end, back-
>end model.

I would say all but there are a few idiots out there.

>I expect others might disagree, but in my experience a well
>designed front-end will not grow in size, because no data is held
>there, and no operations that require temporary data objects will run
>there.

It's been my experience that the FE will grow by about 10% or 20% in the first few
days and thereafter stay the same size roughly. I've had clients who haven't
replaced or compacted the FE for a year or two.

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/

Re: Code For Compact On Close?

"Neil" wrote in
news:hYTWi.3008$%Z2.2544@nlpi068.nbdc.sbc.com:

> I completely disagree with you. One of the advantages of using an
> Access MDB file over, say, an ADP, is the ability to use temporary
> tables for report generation and other ad-hoc uses.
>
> Also, some implementations of client-server apps using an MDB
> front end will copy static lookup tables from the server machine
> when the database is opened, and then use those local tables for
> lookup, instead of making calls across the network for lookup
> tables.
>
> So I disagree that "a well designed front-end will not grow in
> size."

any front end that does either of the things you describe is one
that I would classify as poorly-designed.

Temporary data belongs in a temporary database, not in your front
end. Any other approach is a substandard design.

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

Re: Code For Compact On Close?

"Tony Toews [MVP]" wrote in
news:jl9pi31ilkqk3s18c37tj03c8rineisj1p@4ax.com:

> It's been my experience that the FE will grow by about 10% or 20%
> in the first few days and thereafter stay the same size roughly.
> I've had clients who haven't replaced or compacted the FE for a
> year or two.

It's important, however, to compact your front end if the
proportions of the data tables in your back end change. If, for
instance, your queries in your front end are compiled when you have
1000 records in one table and 1000 records in another, and your
database grows to 2000 in one and 500,000 in the other, it's likely
that the compilation for queries using those two tables will be
sub-optimal. A compact will flag all queries for recompiling using
current table statistics the next time those queries are executed.

But it's very seldom that back end data changes so drastically in a
way that makes the original compilation plan inefficient. But it's
also important to remember that it's possible for that to happen.

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

Re: Code For Compact On Close?

And the temporary database would still need to be compacted occasionally.
Remember, the person I was replying to wrote:

>>> Most enterprise Access applications are built on the front-end, back-
>>> end model. I expect others might disagree, but in my experience a well
>>> designed front-end will not grow in size, because no data is held
>>> there, and no operations that require temporary data objects will run
>>> there.

In other words, his argument was that all data would be in the back end, and
there would be no data in the front end (whether in the main or a temporary
database) to grow the database. That's what I was saying was wrong.


"Douglas J. Steele" wrote in message
news:e6MlXqgHIHA.2100@TK2MSFTNGP03.phx.gbl...
> If you're using temporary tables in an MDB, you should put them in a
> temporary database so that the front-end doesn't increase significantly in
> size.
>
> See the example Tony Toews has at
> http://www.granite.ab.ca/access/temptables.htm
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Neil" wrote in message
> news:hYTWi.3008$%Z2.2544@nlpi068.nbdc.sbc.com...
>>I completely disagree with you. One of the advantages of using an Access
>>MDB file over, say, an ADP, is the ability to use temporary tables for
>>report generation and other ad-hoc uses.
>>
>> Also, some implementations of client-server apps using an MDB front end
>> will copy static lookup tables from the server machine when the database
>> is opened, and then use those local tables for lookup, instead of making
>> calls across the network for lookup tables.
>>
>> So I disagree that "a well designed front-end will not grow in size."
>>
>>
>> "lyle" wrote in message
>> news:1194006436.839478.206280@19g2000hsx.googlegroups.com...
>>> On Oct 28, 12:11 am, "Neil" wrote:
>>>> I would like to compact on close only if the database size goes over a
>>>> certain amount, rather than each time. Thus, I'd like to check the file
>>>> size
>>>> and then perform the compact through code as the mdb's closing. Is that
>>>> possible?
>>>>
>>>> I suppose one option would be to set the Compact On Close option in the
>>>> switchboard's On Close event, and then clear it whenever the database
>>>> is
>>>> opened. That would probably work. But I'd prefer a cleaner solution,
>>>> where I
>>>> can just call the compact function as the database is closing (I'm
>>>> guessing
>>>> this isn't possible...).
>>>
>>> Perhaps, for beginners it's worthwhile ot point out why (IMO) this
>>> capability (check size and if big, compact) isn't built in to Access.
>>> Most enterprise Access applications are built on the front-end, back-
>>> end model. I expect others might disagree, but in my experience a well
>>> designed front-end will not grow in size, because no data is held
>>> there, and no operations that require temporary data objects will run
>>> there. So the front-end will need compacting no more often than the
>>> user has some spare time and says, "Gee, maybe I should compact this
>>> thing." The back end can be compacted regularly; we only have to
>>> ascertain that no one has opened it exclusivley before we do so; this
>>> can be built into code rather easily.
>>>
>>>
>>
>>
>
>

Re: Code For Compact On Close?

I would consider a temporary database (MDB file) a part of the front end,
even if not the main file. The SQL database would be the back end. So, if
you mean that temporary data should go in a separate MDB file on the client
machine, then you're agreeing with me that his argument that all data would
reside in the back end was wrong.

Whether in the main file or in a separate MDB file on the client machine,
the point I was making was that there was a need for temporary front end
(client) data in a well-designed system, and not all data would reside in
the back end.


"David W. Fenton" wrote in message
news:Xns99DDDC34A6FA9f99a49ed1d0c49c5bbb2@216.196.97.142...

> any front end that does either of the things you describe is one
> that I would classify as poorly-designed.
>
> Temporary data belongs in a temporary database, not in your front
> end. Any other approach is a substandard design.
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: Code For Compact On Close?

The temporary database would technically be a second back-end, not a
front-end.

And since it's a temporary table, you'd delete the temporary database when
done with the table, not worry about whether or not it needs compacting.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Neil" wrote in message
news:vkiXi.690$RR6.378@newssvr22.news.prodigy.net...
> And the temporary database would still need to be compacted occasionally.
> Remember, the person I was replying to wrote:
>
>>>> Most enterprise Access applications are built on the front-end, back-
>>>> end model. I expect others might disagree, but in my experience a well
>>>> designed front-end will not grow in size, because no data is held
>>>> there, and no operations that require temporary data objects will run
>>>> there.
>
> In other words, his argument was that all data would be in the back end,
> and there would be no data in the front end (whether in the main or a
> temporary database) to grow the database. That's what I was saying was
> wrong.
>
>
> "Douglas J. Steele" wrote in message
> news:e6MlXqgHIHA.2100@TK2MSFTNGP03.phx.gbl...
>> If you're using temporary tables in an MDB, you should put them in a
>> temporary database so that the front-end doesn't increase significantly
>> in size.
>>
>> See the example Tony Toews has at
>> http://www.granite.ab.ca/access/temptables.htm
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "Neil" wrote in message
>> news:hYTWi.3008$%Z2.2544@nlpi068.nbdc.sbc.com...
>>>I completely disagree with you. One of the advantages of using an Access
>>>MDB file over, say, an ADP, is the ability to use temporary tables for
>>>report generation and other ad-hoc uses.
>>>
>>> Also, some implementations of client-server apps using an MDB front end
>>> will copy static lookup tables from the server machine when the database
>>> is opened, and then use those local tables for lookup, instead of making
>>> calls across the network for lookup tables.
>>>
>>> So I disagree that "a well designed front-end will not grow in size."

Re: Code For Compact On Close?

Well, if by that all data would be in the back end, that poster included a
temporary MDB file stored on the client's machine as part of the "back end,"
then I don't have a problem with what he wrote. But if he meant (as I
originally thought) that in a client/server situation any temporary data
would only be in the server database, then I would still disagree with him.
But you're probably right re. what he meant.


"Douglas J. Steele" wrote in message
news:e9k%23D9tHIHA.1204@TK2MSFTNGP03.phx.gbl...
> The temporary database would technically be a second back-end, not a
> front-end.
>
> And since it's a temporary table, you'd delete the temporary database when
> done with the table, not worry about whether or not it needs compacting.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Neil" wrote in message
> news:vkiXi.690$RR6.378@newssvr22.news.prodigy.net...
>> And the temporary database would still need to be compacted occasionally.
>> Remember, the person I was replying to wrote:
>>
>>>>> Most enterprise Access applications are built on the front-end, back-
>>>>> end model. I expect others might disagree, but in my experience a well
>>>>> designed front-end will not grow in size, because no data is held
>>>>> there, and no operations that require temporary data objects will run
>>>>> there.
>>
>> In other words, his argument was that all data would be in the back end,
>> and there would be no data in the front end (whether in the main or a
>> temporary database) to grow the database. That's what I was saying was
>> wrong.
>>
>>
>> "Douglas J. Steele" wrote in message
>> news:e6MlXqgHIHA.2100@TK2MSFTNGP03.phx.gbl...
>>> If you're using temporary tables in an MDB, you should put them in a
>>> temporary database so that the front-end doesn't increase significantly
>>> in size.
>>>
>>> See the example Tony Toews has at
>>> http://www.granite.ab.ca/access/temptables.htm
>>>
>>> --
>>> Doug Steele, Microsoft Access MVP
>>> http://I.Am/DougSteele
>>> (no private e-mails, please)
>>>
>>>
>>> "Neil" wrote in message
>>> news:hYTWi.3008$%Z2.2544@nlpi068.nbdc.sbc.com...
>>>>I completely disagree with you. One of the advantages of using an Access
>>>>MDB file over, say, an ADP, is the ability to use temporary tables for
>>>>report generation and other ad-hoc uses.
>>>>
>>>> Also, some implementations of client-server apps using an MDB front end
>>>> will copy static lookup tables from the server machine when the
>>>> database is opened, and then use those local tables for lookup, instead
>>>> of making calls across the network for lookup tables.
>>>>
>>>> So I disagree that "a well designed front-end will not grow in size."
>
>

Re: Code For Compact On Close?

"Neil" wrote in
news:qriXi.691$RR6.219@newssvr22.news.prodigy.net:

> I would consider a temporary database (MDB file) a part of the
> front end, even if not the main file. The SQL database would be
> the back end. So, if you mean that temporary data should go in a
> separate MDB file on the client machine, then you're agreeing with
> me that his argument that all data would reside in the back end
> was wrong.
>
> Whether in the main file or in a separate MDB file on the client
> machine, the point I was making was that there was a need for
> temporary front end (client) data in a well-designed system, and
> not all data would reside in the back end.

How is that position relevant to the issue of compacting a front
end? Keep in mind that Tony's solution creates the temp database on
the fly each time so it doesn't require compacting. Thus, even if
you count the temp database as part of your front end, there is
still never any need to compact the front end.

Which was, I thought, the issue in question, not whether or not "all
data would reside in the back end," which, so far as I can tell, no
one was really arguing in the sense that you are construing it.

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

Re: Code For Compact On Close?

"Douglas J. Steele" wrote in
news:e9k#D9tHIHA.1204@TK2MSFTNGP03.phx.gbl:

> The temporary database would technically be a second back-end, not
> a front-end.
>
> And since it's a temporary table, you'd delete the temporary
> database when done with the table, not worry about whether or not
> it needs compacting.

Tony recreates his on the fly when needed. I keep an empty template,
tmp.bak, and copy it over top of the tmp.mdb when I want to replace
it.

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

Re: Code For Compact On Close?

"Neil" wrote in
news:vhjXi.2712$yV6.2465@newssvr25.news.prodigy.net:

> Well, if by that all data would be in the back end, that poster
> included a temporary MDB file stored on the client's machine as
> part of the "back end," then I don't have a problem with what he
> wrote. But if he meant (as I originally thought) that in a
> client/server situation any temporary data would only be in the
> server database, then I would still disagree with him. But you're
> probably right re. what he meant.

And none of that has anything to do with the question of compacting
the "front end," regardless of which way you define it. You're
trying to change the terms of the question in order to avoid
admitting that you were wrong.

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

Re: Code For Compact On Close?

No, not really. The original point that I was making was that data needs to
be stored on the client's machine. As I said in the message you replied to,
if the person I was replying to meant that temporary data could be stored in
a temporary file on the client machine, and that temporary file on the
client machine was part of the "back end" that he was referring to, then,
yes, I was wrong, because I misunderstood his point. But if he meant that
all data in a well-designed system would be stored in the server db, then
that was the original point I was countering.

In other words, there are two distinct issues here: first the compacting
issue, and second the "all data in the back end" issue, which is what I was
countering (since I thought that meant all data in the server db). If the
person had said, "You don't need a compact because temporary data could be
placed in a temp db on the client machine," then I wouldn't have disagreed
with him. But I thought he was saying that all temp data should go in the
server db, and that was why I countered his statement.


"David W. Fenton" wrote in message
news:Xns99DE91E013B5f99a49ed1d0c49c5bbb2@216.196.97.142...
> "Neil" wrote in
> news:vhjXi.2712$yV6.2465@newssvr25.news.prodigy.net:
>
>> Well, if by that all data would be in the back end, that poster
>> included a temporary MDB file stored on the client's machine as
>> part of the "back end," then I don't have a problem with what he
>> wrote. But if he meant (as I originally thought) that in a
>> client/server situation any temporary data would only be in the
>> server database, then I would still disagree with him. But you're
>> probably right re. what he meant.
>
> And none of that has anything to do with the question of compacting
> the "front end," regardless of which way you define it. You're
> trying to change the terms of the question in order to avoid
> admitting that you were wrong.
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: Code For Compact On Close?

Do you perform a Kill first, or do you just copy it over on top of it?


"David W. Fenton" wrote in message
news:Xns99DE91BAF1D19f99a49ed1d0c49c5bbb2@216.196.97.142...
> "Douglas J. Steele" wrote in
> news:e9k#D9tHIHA.1204@TK2MSFTNGP03.phx.gbl:
>
>> The temporary database would technically be a second back-end, not
>> a front-end.
>>
>> And since it's a temporary table, you'd delete the temporary
>> database when done with the table, not worry about whether or not
>> it needs compacting.
>
> Tony recreates his on the fly when needed. I keep an empty template,
> tmp.bak, and copy it over top of the tmp.mdb when I want to replace
> it.
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: Code For Compact On Close?

Well, first, I hadn't reviewed Tony's solution when I made the statement.
So, yes, you're right about that. So, with Tony's solution (and not the
fixed-temp-db solution that I was thinking of), there would be no need for
compacting.

So, to put this issue to rest, let me say once and for all: I agree with
you. OK? There is no need for compacting if you delete the temporary db and
recreate it on the fly. The only point I was making (which I thought the
person wasn't saying) was that you need temp data on the client machine,
even in a well-designed system, and you can't always use the server db for
temp data. That was the only point I was making, really.



"David W. Fenton" wrote in message
news:Xns99DE9192B1015f99a49ed1d0c49c5bbb2@216.196.97.142...
> "Neil" wrote in
> news:qriXi.691$RR6.219@newssvr22.news.prodigy.net:
>
>> I would consider a temporary database (MDB file) a part of the
>> front end, even if not the main file. The SQL database would be
>> the back end. So, if you mean that temporary data should go in a
>> separate MDB file on the client machine, then you're agreeing with
>> me that his argument that all data would reside in the back end
>> was wrong.
>>
>> Whether in the main file or in a separate MDB file on the client
>> machine, the point I was making was that there was a need for
>> temporary front end (client) data in a well-designed system, and
>> not all data would reside in the back end.
>
> How is that position relevant to the issue of compacting a front
> end? Keep in mind that Tony's solution creates the temp database on
> the fly each time so it doesn't require compacting. Thus, even if
> you count the temp database as part of your front end, there is
> still never any need to compact the front end.
>
> Which was, I thought, the issue in question, not whether or not "all
> data would reside in the back end," which, so far as I can tell, no
> one was really arguing in the sense that you are construing it.
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: Code For Compact On Close?

"Neil" wrote in
news:H0wXi.18858$JD.18192@newssvr21.news.prodigy.net:

> So, to put this issue to rest, let me say once and for all: I
> agree with you. OK? There is no need for compacting if you delete
> the temporary db and recreate it on the fly. The only point I was
> making (which I thought the person wasn't saying) was that you
> need temp data on the client machine, even in a well-designed
> system, and you can't always use the server db for temp data. That
> was the only point I was making, really.

Well, why were you making that point? It was orthogonal to what
seems to me to be the point of discussion. Server or client has
nothing to do with it -- the main issue is whether temp data belongs
in the front end (strictly defined as the Access application).

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

Re: Code For Compact On Close?

"Neil" wrote in
news:H_vXi.18857$JD.17353@newssvr21.news.prodigy.net:

> Do you perform a Kill first, or do you just copy it over on top of
> it?

In apps where I've done this, I copy over top of it in the
application close or startup. In a few cases, I've asked the user if
they want to keep their temp data between sessions. And in either
case, there's error handling in case the file is in use.

But in most of my apps, I don't do anything to it at all -- just
leave it alone, as I see no problem with letting the temp MDB bloat.

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

Re: Code For Compact On Close?

"Neil" wrote in
news:HZvXi.18856$JD.189@newssvr21.news.prodigy.net:

> there are two distinct issues here: first the compacting
> issue, and second the "all data in the back end" issue

The second one was all in your head.

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

Re: Code For Compact On Close?

OK, apparently so. Still, that's what I was discussing.

"David W. Fenton" wrote in message
news:Xns99DFCB793303Bf99a49ed1d0c49c5bbb2@216.196.97.142...
> "Neil" wrote in
> news:HZvXi.18856$JD.189@newssvr21.news.prodigy.net:
>
>> there are two distinct issues here: first the compacting
>> issue, and second the "all data in the back end" issue
>
> The second one was all in your head.
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: Code For Compact On Close?

OK, one more time (with feeling...). I thought the person was saying that in
a well-designed system, all data, including temp data, would go in the
server db. I did not realize that by "back end" he was including a temp db
on the client machine. I thought by "back end" he meant strictly the server
db. So I was making the point that temp data would go on the client machine,
and not in the server db. That was the only point I was making.

The further discussions in this thread made the distinction between putting
the temp data in the application file on the client machine vs. in a temp
file on the client machine, and I don't disagree with that the temp file
approach is a superior method. But I thought he was saying that temp data
would go into the server db, and that was the point I was disagreeing with.


"David W. Fenton" wrote in message
news:Xns99DFCB1C1B72Ff99a49ed1d0c49c5bbb2@216.196.97.142...
> "Neil" wrote in
> news:H0wXi.18858$JD.18192@newssvr21.news.prodigy.net:
>
>> So, to put this issue to rest, let me say once and for all: I
>> agree with you. OK? There is no need for compacting if you delete
>> the temporary db and recreate it on the fly. The only point I was
>> making (which I thought the person wasn't saying) was that you
>> need temp data on the client machine, even in a well-designed
>> system, and you can't always use the server db for temp data. That
>> was the only point I was making, really.
>
> Well, why were you making that point? It was orthogonal to what
> seems to me to be the point of discussion. Server or client has
> nothing to do with it -- the main issue is whether temp data belongs
> in the front end (strictly defined as the Access application).
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/