Compact on close...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am using Access 97 and hence do not have the option of selecting 'Compact
on close.' Is there a way to compact my database everytime it is closed by
running a macro or something?

This database will be shared on a server. Does this complicate issues?

Thanks in advance,

Rookie.
 
Access said:
Hello,

I am using Access 97 and hence do not have the option of selecting
'Compact on close.' Is there a way to compact my database everytime
it is closed by running a macro or something?

This database will be shared on a server. Does this complicate issues?

Thanks in advance,

Rookie.
Click to expand...

Anything Access application beyond a single user "CD Collection" should be a
split application with a separate front end on each user's PC and a separate
data file shared on the network. Since the vast majority of developers use this
approach "Compact on Close" is a fairly dead issue as it will only compact the
front end which is usually not the file that needs it.

In addition, if you have multiple users any compacting attempted by one person
will only be possible when no one else is using the file. This makes
*automatic* compacting a non-starter unless you want to schedule a task to run
after hours.
 
Hey Rick,

Thanks again for your reply...we use Citrix to access the database, so I
guess running the compact as a task would be the best bet.

By the way, how exactly would you do that? I see Access in the scheduled
tasks, but that would probably open Access, wouldn't it?

Not sure how to write a script,

John.
 
Access rookie said:
Hey Rick,

Thanks again for your reply...we use Citrix to access the database, so I
guess running the compact as a task would be the best bet.

By the way, how exactly would you do that? I see Access in the scheduled
tasks, but that would probably open Access, wouldn't it?

Not sure how to write a script,
Click to expand...

You could schedule a batch file that would run the JetComp utility. Also, you
could build a separate Access app that used an AutoExec macro to run code. That
code would use Dir to test for the existence of the ldb file for your back end
file. Non-existence would indicate no one was using the file.

The app could then use the DBEngine.CompactDatabase method to compact the data
file and then close itself. This method cannot compact the file "into itself"
like you can with the GUI so you would need to compact into a temp name and then
use the Name statement to change the name back to the original.
 
Hey Rick,

Thanks for your post; I'll be doing research on how to get this done.

Optimistic,

John.
 
Back
Top