Compacting Access 97 DB with VBA

[Guest]

I have a databse which creates and drops 15 - 20 large tables each day. I
want to mimic the compact on close feature available on later versions of
Access. I have tried the below at the end of my code which builds and drops
the tables

CommandBars("Menu Bar").Controls("Tools").Controls("Database
utilities").Controls("Compact database").accDoDefaultAction

But this prompts for file names, then does not allow compaction because the
file is open.

Can anyone point me in the right direction

 

[Guest]

Hi, Tony.

I have a databse which creates and drops 15 - 20 large tables each day.

To avoid database bloat, consider creating a temporary database file to
create these 15 - 20 tables in, then link to these tables from the current
database. When finished, delete the temporary file.

But this prompts for file names, then does not allow compaction because the
file is open.

Unlike later versions of Access, one cannot compact the current database via
VBA code from within Access 97. For alternatives, please see the following
Web pages:

Access 97 Compactor Add-in:

http://www.mvps.org/access/modules/mdl0030.htm

VB5 Compact Database utility for Access 97:

http://www.mvps.org/access/modules/mdl0020.htm

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Douglas J Steele]

You could also add MichKa's TSI SOON (Shut One, Open New) database add-in to
the possible solutions:

http://www.trigeminal.com/utility.asp?ItemID=8#8

For an example of using temporary databases, see what Tony Toews has at
http://www.granite.ab.ca/access/temptables.htm

 

['69 Camaro]

Thanks for the links, Doug. I hadn't looked at MichKa's TSI SOON in quite a
while and had forgotten some of its handy uses.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

Many thanks

Will use a temporary Db as you suggest. The current design is not efficient
for Access, I am spliting out some functionality of an existing Access
application, but only using SQL commands, as this part will move to SQL
server (eventually).

I will give the addins a try with some other Access apps

Regards

 

['69 Camaro]

You're welcome. Good luck on the eventual upsizing.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.