Compact a MDB file on Form button

[Proditech Orion]

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

 

[Rickster57]

a link

See if this helps.

http://www.thescripts.com/forum/thread208170.html

Good night.

 

[boblarson]

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:
        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:
        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.

 

[Proditech Orion]

Invalid use of property

See if this helps.

http://www.thescripts.com/forum/thread208170.html

Good night.

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

 

[Proditech Orion]

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

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

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

SendKeys "%(TDC)", False

to

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.

 

[boblarson]

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:

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

Then, I call from my button:

Private Sub YourButtonName_Click()
    CompactData
End Sub

 

[Proditech Orion]

Bob,
I created a module called CompactData. Inside, I created the procedure

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

On my form About, I created a button

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

 

[boblarson]

You shouldn't name the module the same thing as the sub. Rename the module.

 

[Proditech Orion]

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...

 

[boblarson]

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.

 

[Proditech Orion]

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