473,569 Members | 2,593 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.
Oct 28 '07 #1
29 2754
Hi

Someone gave me this function:

Public Function AutoCompactAppl ication()

Dim s
Dim strProjectPath As String, strProjectName As String

strProjectPath = Application.Cur rentProject.Pat h
strProjectName = Application.Cur rentProject.Nam e
filespec = strProjectPath & "\" & strProjectName

s = CLng(FileLen(fi lespec) / 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.Set Option ("Auto Compact"), 1 'compact app

Else

Application.Set Option ("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

Oct 28 '07 #2
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.c om" <u31673@uwewrot e in message
news:7a5ae5bec1 64f@uwe...
Hi

Someone gave me this function:

Public Function AutoCompactAppl ication()

Dim s
Dim strProjectPath As String, strProjectName As String

strProjectPath = Application.Cur rentProject.Pat h
strProjectName = Application.Cur rentProject.Nam e
filespec = strProjectPath & "\" & strProjectName

s = CLng(FileLen(fi lespec) / 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.Set Option ("Auto Compact"), 1 'compact app

Else

Application.Set Option ("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

Oct 28 '07 #3
answered in comp.databases. ms-access.
"Neil" <no****@nospam. netwrote in message
news:PZ******** ********@newssv r25.news.prodig y.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.


Oct 28 '07 #4
Thanks.

I wonder why not just use currentdb.name for filespec?
"biganthony via AccessMonster.c om" <u31673@uwewrot e in message
news:7a5ae5bec1 64f@uwe...
Hi

Someone gave me this function:

Public Function AutoCompactAppl ication()

Dim s
Dim strProjectPath As String, strProjectName As String

strProjectPath = Application.Cur rentProject.Pat h
strProjectName = Application.Cur rentProject.Nam e
filespec = strProjectPath & "\" & strProjectName

s = CLng(FileLen(fi lespec) / 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.Set Option ("Auto Compact"), 1 'compact app

Else

Application.Set Option ("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

Oct 28 '07 #5
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******** ********@newssv r25.news.prodig y.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.

Oct 28 '07 #6
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]" <a@m.comwrote in message
news:%2******** **********@TK2M SFTNGP02.phx.gb l...
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******** ********@newssv r25.news.prodig y.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.


Oct 29 '07 #7
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]" <a@m.comwrote in message
news:%2******** **********@TK2M SFTNGP02.phx.gb l...
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******** ********@newssv r25.news.prodig y.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.


Nov 1 '07 #8
answered in thread "Code for Break on Unhandled Errors", dated 10/31/07, in
this newsgroup.
"Neil" <no****@nospam. netwrote in message
news:p7******** ***********@new ssvr14.news.pro digy.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]" <a@m.comwrote in message
news:%2******** **********@TK2M SFTNGP02.phx.gb l...
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******** ********@newssv r25.news.prodig y.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.


Nov 1 '07 #9
If only I'd waited another hour!... Thanks for your reply there (and here!).

"tina" <no****@address .comwrote in message
news:dw******** ***********@bgt nsc05-news.ops.worldn et.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******** ***********@new ssvr14.news.pro digy.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]" <a@m.comwrote in message
news:%2******* ***********@TK2 MSFTNGP02.phx.g bl...
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******** ********@newssv r25.news.prodig y.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.



Nov 1 '07 #10

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

Similar topics

35
17010
by: Mike MacSween | last post by:
Is it?
13
5059
by: James Franklin | last post by:
Hi, I have a number of databases in A2K, written on different machines with different installations of Office. I have found that compacting a database while it is open regularly fails, seemingly at random. This is especially annoying as I usually set the Compact on Close setting in Tools/Options to true. Has anyone else experienced this...
13
10404
by: Larry L | last post by:
Access is noted for bloating a database when you add and delete records frequently. I have always had mine set to compact on close, and that works great. Now after everyone's advice I split my database, so the data is in a second (back-end) database with all the tables linked. However, now when I close the database, it compacts the front end,...
3
2369
by: Trevor Hughes | last post by:
Hello All I have a database (Access 2000, running on Win 2000), which suffers from bloat over a period of time. In order to solve the problem I set the option to compact on exit. This however has caused a problem. The permissions of the mdb file which are set to Everyone-Full control, are reset when the database is compacted. The end...
4
2068
by: Wayne | last post by:
Does "Compact On Close" do a "Compact and Repair" or just a compact. Is a compact necessary (or at least a good idea) on a regular basis, say weekly, for a database that has several hundred records per week added to it? TIA.
1
4813
by: robert demo via AccessMonster.com | last post by:
In my startup routine, I have the following code: s = CLng(FileLen(filespec) / 1000000) If s > 5 Then 'FIRST, BACKUP THE FRONT END If BackupFrontEnd = False Then Exit Function End If
3
2168
by: G Gerard | last post by:
Hello The more I use an application ( an mdb) created using MSAccess I notice that the Byte size of the application keeps on increasing. Once in a while I do a Compact and Repair Database (under Tools in MSAccess) on the application and the size then returns to normal.
9
3984
by: Ron | last post by:
New discovery. If I take a perfectly good database, and "compact/repair" on it with Access 2000 (seems to be at multiple sites--I've tried it with my system here, at another office on an entirely different network), it damages the file somehow. The user's machine that did the compact/repair can see the file fine. But any networked user...
1
319
by: Simon | last post by:
Dear reader, If "Compact on close" is set to yes (tickbox) the mdb or mde will be compact by closing. During this process a temporary db1.mdb will be generated and deleted after the compact is finished.
0
7614
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8125
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7676
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6284
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5513
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5219
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3653
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2114
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
938
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.