Compact a MDB file on Form button

Proditech Orion

Registered User.
Local time
Today, 15:38
Joined
Sep 28, 2006
Messages
15
Hi,

I have 2 files

db_main.mdb
db_tables.mdb

1 - Can I create a form with a button on db_main.mdb that will compact (and repair) db_tables.mdb without any dialog box or without going to the menu?

Thx in advance for any help.
Cheers
 
Actually, you can compact via code very easily. However, the caveat to this is that it only will work if there are no other people in the database at the time.

Put this code on a button (I've used this in Access 2.0, 95, 97, 2000, 2002, and 2003).

Code:
Code:
        DoCmd.Close acForm, "frmYourFormNameHere", acSaveNo
        SendKeys "%(TDC)", False

change the form name to whatever form you have open. When compacting it will require you to close any open objects, so if you have any other open forms, you may want to modify the above code to:

Code:
Code:
        Dim frm As Form
        For each frm in Forms
            DoCmd.Close acForm, frm.Name, acSaveNo
        Next frm
        SendKeys "%(TDC)", False

I usually don't like using SendKeys. In fact I don't ever use it other than in this piece of code. It has worked like a charm for me for years and years.
 
Invalid use of property

Rickster57 said:

Hi Rickster57, many thx on your help, but I am making some mistake along the way... I copied the code on the page to a module,
then I created a button called Bot_compactDB. Inside I put the code

CompactDB ([name of table]) and it gives me an error -
Invalid use of property

Am I using the function in the wrong way ?
thx for your time
 
boblarson said:
Put this code on a button (I've used this in Access 2.0, 95, 97, 2000, 2002, and 2003).

Code:
Code:
        DoCmd.Close acForm, "frmYourFormNameHere", acSaveNo
        SendKeys "%(TDC)", False

Code:
Code:
        Dim frm As Form
        For each frm in Forms
            DoCmd.Close acForm, frm.Name, acSaveNo
        Next frm
        SendKeys "%(TDC)", False

Hi Bob, thx for your help, your approach it's so much easier BUT again, my lack of abilities prevent me from doing the right move.

Whem I put your code(s) on a button, and click
it gives me the following error

You can't compact the open database while running a macro or Visual Basic code.

Instead of using a macro or code, on the Tools menu, point to database utilities, and then click Compact/Repair Database.

OH... and I can see the error because I changed the code

Code:
SendKeys "%(TDC)", False
to
Code:
SendKeys "%(TDC)", True

I feel that I am very near of having what I need.
thx in advance for any help you can give me.
 
Sorry, I forgot to mention that you have to put the code in a sub in a MODULE and not a Form module. Then, call the sub that you created that is in the module from the button.

So, I have a standard module with a sub I named CompactData:
Code:
Public Sub CompactData()
        DoCmd.Close acForm, "frmYourFormNameHere", acSaveNo
        SendKeys "%(TDC)", False
End Sub

Then, I call from my button:
Code:
Private Sub YourButtonName_Click()
    CompactData
End Sub
 
Bob,
I created a module called CompactData. Inside, I created the procedure

Code:
Public Sub CompactData()
        DoCmd.Close acForm, "About", acSaveNo
        SendKeys "%(TDC)", False
End Sub

On my form About, I created a button

Code:
Private Sub Command16_Click()
    CompactData
End Sub

and
it gives me an error

Compile error:
Expected variable or procedure, not module

Any insight? Am I missing something?
Please advice :)
Again, thx 4 your time
 
You shouldn't name the module the same thing as the sub. Rename the module.
 
I did... now nothing happens...
no error message, no compact

Very strange bc I understand the procedure. (Sending the keys)
oh, well.. sorry to bother you for so long...
 
Is it possible for you to post a stripped down version of your database here so I can take a look? If you zip it, it can be 393Kb large.

Just leave the table and queries that the About form is bound to, if it is bound to anything, and the about form. The table, if it is, can be cleared of data. I just want to see the form. Are any other forms open when you do this? If so, we'll have to use my other code which goes through and closes all open forms before doing the sendkeys action.
 
Last edited:
Bob, I understand now what the problem is :)
You wont believe ehhehe

Your function was not working because I deactivated the ALLOW FULL Menus on the STARTUP submenu on the TOOLS options on the menu bar... With Full menus activated, obviously it works... and beautifully

the problem is that I dont want anyone to access the menus, bc I have a function to disable shift key and lots of code that are not for the preying guys.. yes, I know that anyone (That knows) can import everyting to another database and see all :)

Can I put this to work with full menus OFF ?
thx
Prody
 
Last edited:

Users who are viewing this thread

Back
Top Bottom