Compacting an Access Database

[wally world]

OK - I don't know much about VB in Access. When I have a bunch of queries that I run in a row, I use a macro. I know people here have said Access macros suck before, but I don't know how to do this another way.

Anyways, inside this particular database I am running, I have about 20 or so queries that are get run in a certain order, some creating new tables, and then others paring down the information in those tables to just the records I need. In the end, I'd like to automatically Compact the database after everything has run to save space on the network.

How do I do this? In the macro Design box, there is an option for RunCommand, then CompactDatabase, but when I tried that, it prompts me for a database to Compact From. Any way I can just get it to run and compact the currently activated database.

Another question while I'm here.....many of these queries are queries on Oracle database tables that I am linked to via an ODBC connection. Problem is, I have to supply a password when I run a query (actually just the first query). Anyway I can set up the query so that I have the password in there and don't have to be prompted for it each time?

Thanks.

[Ron Weasley]

Another question while I'm here.....many of these queries are queries on Oracle database tables that I am linked to via an ODBC connection. Problem is, I have to supply a password when I run a query (actually just the first query). Anyway I can set up the query so that I have the password in there and don't have to be prompted for it each time?


This alone is worth learning a little VBA coding. The VBA commands to run the queries you've already created and now use macros for will simply be the repetition of one or two statements for each query (not much to learn) and then you can add items like this after you get the simplest stuff worked out.

[Elisha]

Ron,

This may not be the answer you're looking for but try this.

Tools -> Database Utilities - > Compact Database

[Ron Weasley]

What was my question?

[Elisha]

In the end, I'd like to automatically Compact the database after everything has run to save space on the network.

Umm...I think this is the important part of Q1? If it's no problem to do 3 mouse clicks after running your macro, just compact outside of the macro after you run it - like I first suggested.

[Actuary321]

In the end, I'd like to automatically Compact the database after everything has run to save space on the network.

Umm...I think this is the important part of Q1? If it's no problem to do 3 mouse clicks after running your macro, just compact outside of the macro after you run it - like I first suggested.
I think Ron was trying to tell you that it wasn't his question, even though you responded to him.

As for compacting, I suspect it is not so much that he can't do three mouse clicks as much as it is he doesn't want to have to remember to do them every time.

As for the answer the Access help says that the 'Compactdatabase' method: Copies and compacts a closed database, and gives you the option of changing its version, collating order, and encryption. (Microsoft Jet workspaces only). So you would not be able to use this in a macro in the current database on the current database.

I don't know a lot of Access but could you create a new database that had in it only an autoexecute macro that would compact your database and then open the compacted database. Then from the current database when you want to do the compaction open this new database. Like I said, I don't know if this is possible or how to do it if it is.

[Kitten]

What he said...I don't think you can compact a database unless it's closed.

[Guyute]

Try the 'SendKeys' macro command. I have some databases where I do the same thing. In the Action Arguments (bottom of the macro) use the keystrokes '%tdc'. The '%' is the same as the <Alt> key so any menu item that has a hot key can be performed using this command.

You may also be able to use this same command to solve your password dilemma.

[wally world]

The reason I want to automate this is that I indeed often forget to do it after my weekly report is finished. If I forget to do it so many weeks in a row, I will max out the database size, and I don't want that happening.

I think you are correct that, from a macro you can only compact a closed database, but if you just go to Tools->Database Utilities->Compact Database, you don't have to close the database first. It will just compact the currently open database. That's why I figured you had to be able to program it somehow.

[GadgetGeek]

When you Tools->Database Utilities->Compact Database, it actually closes the database, compacts it, then reopens it. As for whether or not you can put it in a macro, I don't know.

[Old Timer]

From the Access 2000 help files:
===============================================
Compact an Access database or Access project automatically when you close it

Microsoft Access can automatically compact a Microsoft Access database or Microsoft Access project every time you close it.

1. Open the Access database or Access project that you want Microsoft Access to compact automatically.

2. On the Tools menu, click Options.

3. Click the General tab.

4. Select the Compact On Close check box.

Note: Compacting does not occur if you close a multiuser (shared) database while another user has it open.
===============================================

[Still Smiling]

Actually, you can compact the database from VBA control. The solution we used was to move the code to Excel, and then compact the database from an instance of VBA running there; you will still need to close the database, but it won't matter.

Under Office 97, choosing the Compact Database option from the menu's actually DOES close the database - the mechanics are just well disguised from the user.

[Gates Is Antichrist]

http://www.trigeminal.com/usenet/usenet023.asp?1033 gives reliable and informative context on the issue of compacting via code.

Their second item relates to shelling a command, a la the following (which is on one line, in case it looks split)...
C:\program files\Microsoft Office\msaccess.exe" foo.mdb /compact

However I saw no mention there of Bill the AntiChrist's own retort, which is a standalone .EXE. ...
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q273956

This latter link provides a Microsoft utility which I have found generally useful; I recommend that you give it a try. Of course, considering the URL, it should be "safe" - at least from shenanigans ... and hopefully from MS developer incompetence :)