Code For Compact On Close?

[Neil]

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.

[biganthony via AccessMonster.com]

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

[tina]

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" <u31673@uwewrote 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

[tina]

answered in comp.databases.ms-access.
"Neil" <no****@nospam.netwrote in message
news:PZ****************@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.

[Neil]

Thanks.

I wonder why not just use currentdb.name for filespec?
"biganthony via AccessMonster.com" <u31673@uwewrote 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

[Arvin Meyer [MVP]]

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" <no****@nospam.netwrote in message
news:PZ****************@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.

[Neil]

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]" <[e-mail] in message
news:%2******************@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" <no****@nospam.netwrote in message
news:PZ****************@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.

[Neil]

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]" <[e-mail] in message
news:%2******************@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" <no****@nospam.netwrote in message
news:PZ****************@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.

[tina]

answered in thread "Code for Break on Unhandled Errors", dated 10/31/07, in
this newsgroup.
"Neil" <no****@nospam.netwrote in message
news:p7*******************@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]" <[e-mail] in message
news:%2******************@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" <no****@nospam.netwrote in message
news:PZ****************@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.

[Neil]

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

"tina" <no****@address.comwrote in message
news:dw*******************@bgtnsc05-news.ops.worldnet.att.net...

answered in thread "Code for Break on Unhandled Errors", dated 10/31/07,
in
this newsgroup.
"Neil" <no****@nospam.netwrote in message
news:p7*******************@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]" <[e-mail] in message
news:%2******************@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" <no****@nospam.netwrote in message
news:PZ****************@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.

[tina]

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" <no****@nospam.netwrote in message
news:Ue****************@nlpi061.nbdc.sbc.com...

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

here!).

>
"tina" <no****@address.comwrote in message
news:dw*******************@bgtnsc05-news.ops.worldnet.att.net...

answered in thread "Code for Break on Unhandled Errors", dated 10/31/07,
in
this newsgroup.
"Neil" <no****@nospam.netwrote in message
news:p7*******************@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]" <[e-mail] in message
news:%2******************@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" <no****@nospam.netwrote in message
news:PZ****************@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.

[lyle]

On Oct 28, 12:11 am, "Neil" <[e-mail]:

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.

[Neil]

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" <ly************@gmail.comwrote in message
news:11**********************@19g2000hsx.googlegro ups.com...

On Oct 28, 12:11 am, "Neil" <[e-mail]:

>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.

[Douglas J. Steele]

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" <no****@nospam.netwrote in message
news:hY*****************@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" <ly************@gmail.comwrote in message
news:11**********************@19g2000hsx.googlegro ups.com...

>On Oct 28, 12:11 am, "Neil" <[e-mail]:

>>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.

[Tony Toews [MVP]]

lyle <ly************@gmail.comwrote:

>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/

[David W. Fenton]

"Neil" <no****@nospam.netwrote in
news:hY*****************@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/

[David W. Fenton]

"Tony Toews [MVP]" <tt****@telusplanet.netwrote in
news:jl********************************@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/

[Neil]

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" <NOSPAM_djsteele@NOSPAM_canada.comwrote in message
news:e6**************@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" <no****@nospam.netwrote in message
news:hY*****************@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" <ly************@gmail.comwrote in message
news:11**********************@19g2000hsx.googlegr oups.com...

>>On Oct 28, 12:11 am, "Neil" <[e-mail]:
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.

[Neil]

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" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@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/

[Douglas J. Steele]

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" <no****@nospam.netwrote in message
news:vk***************@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" <NOSPAM_djsteele@NOSPAM_canada.comwrote in message
news:e6**************@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" <no****@nospam.netwrote in message
news:hY*****************@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."

[Neil]

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" <NOSPAM_djsteele@NOSPAM_canada.comwrote in message
news:e9****************@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" <no****@nospam.netwrote in message
news:vk***************@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" <NOSPAM_djsteele@NOSPAM_canada.comwrote in message
news:e6**************@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" <no****@nospam.netwrote in message
news:hY*****************@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."

[David W. Fenton]

"Neil" <no****@nospam.netwrote in
news:qr***************@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/

[David W. Fenton]

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.comwrote in
news:e9**************@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/

[Neil]

Do you perform a Kill first, or do you just copy it over on top of it?
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.comwrote in
news:e9**************@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/

[Neil]

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" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"Neil" <no****@nospam.netwrote in
news:qr***************@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/

[David W. Fenton]

"Neil" <no****@nospam.netwrote in
news:H0******************@newssvr21.news.prodigy.n et:

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/

[David W. Fenton]

"Neil" <no****@nospam.netwrote in
news:H_******************@newssvr21.news.prodigy.n et:

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/

[David W. Fenton]

"Neil" <no****@nospam.netwrote in
news:HZ****************@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/

[Neil]

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

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"Neil" <no****@nospam.netwrote in
news:HZ****************@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/

[Neil]

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" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"Neil" <no****@nospam.netwrote in
news:H0******************@newssvr21.news.prodigy.n et:

>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/