best way to archive AND compacting open dbase 5

[lora]

looking for advice...i have an access database that has one 'data' table that i would need to archive at year end is it best to zip the whole database or export the table as a text file or???<br>
<br>
also is there a way to use vb on close statement to compact the open database? or at put a button on a form that they can click to compact the dbase?<br>
<br>
thanks

 

[elizabeth]

From the Access 2000 documentation, this great new feature:<br>
<br>
Compact an Access database or Access project automatically when you close it.<br>
<br>
Microsoft Access can automatically compact a Microsoft Access database or Microsoft Access project every time you close it. <br>
1. Open the Access database or Access project that you want Microsoft Access to compact automatically.<br>
2. On the Tools menu, click Options.<br>
3. Click the General tab.<br>
4. Select the Compact On Close check box. <br>
5. Note: Compacting does not occur if you close a multiuser (shared) database while another user has it open.<br>
<br>

 

[lora]

unfortunately, i'm still running access 97 and probably will be for some time to come..considering how quickly the files sizes increase, i'm surprised they didn't have this feature in 97...any other ideas???

 

[WP]

Do you really want to compact on exit ? I doubt it or at least I doubt if the users would wear it. I have several dbs that have grown and the compacting can take up to 30 secs. The last thing you want to do when you are leaving at night is to wait 30 secs more while ACCESS quits.<br>
<br>
Why not schedule a job ( assuming you have NT Server ) that compacts the db overnight.<br>
<br>
Opening the db with the /Compact will do this.<br>
<br>
WP <p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[lora]

we do have nt server but i don't believe we were given the ability to schedule jobs...one of the 'real' programmers tried to set something up for me recently and it wasn't on my machine; oh well, looks like i'll have to show the user how to do it manually...thanks for all the advice though

 

[Zelandakh]

Set up a shortcut that has a command line of the following:<br>
<br>
&quot;C:\Program Files\Microsoft Office\Office\MSACCESS.EXE&quot; drive:\path\database.mdb /wrkgrp drive:\path\system.mdw /compact /repair<br>
<br>
This will compact and repair the database assuming no one has it open at the time and comes back with an ok button if successful.

 

[lora]

THANKS, i can create shortcuts and this works great.<br>

 

[waynea]

Speaking of compacting an Access database from within VB, does Access have to be loaded on the machine doing the compacting, or is there a way around this? I know that some of the Access-specific commands don't work in VB without Access being loaded, but so many apps use Access databases, looks like there should be a way to do this.

 

[hoggbottom59]

Access runs Visual Basic for Applications not Visual Basic.
It's a stripped down version for Office, etc.

Any VB wouldn't run without some kind of environment to run in. The mentioned code would need to be compiled to an 'exe' to be able to run outside Access.

Leon.

 

[SQLWilts]

May be a bit late to tell you this now, but Access does NOT have to be loaded onto the machine doing the compacting as there are royalty free &quot;access runtimes&quot; available - too a google search for them, but make sure that they are the same version as your database.

 

[waynea]

TonyWhite:

Yeah, we found one that works, thanks. (Obviously been a while since I looked at this thread...)

hoggbottom59:

I know that Access runs VBA rather than VB. The scenario we were in was running a VB application using an Access database to store the data. The client didn't have Access, so we needed to compact the database from within the VB app.