3

Seems such a simple thing, but I can't find the answer anywhere.

Every time I Compact & Repair, my Autoexec macro runs again. Is there a way to prevent it from running here, as part of the reason for compacting is to get the file size back down again after the Autoexec has deleted a lot of rows.

HansUp
  • 95,961
  • 11
  • 77
  • 135
Wilskt
  • 337
  • 2
  • 9
  • 24

2 Answers2

3

You could rename the macro when you don't want it to operate as an autoexec macro.

DoCmd.Rename "disableAutoExec", acMacro, "AutoExec"

And rename it back to AutoExec when you do want it to behave as an autoexec macro.

If that suggestion isn't satisfactory, you could have the macro check for the presence of a file, disableAutoExec.txt, and do nothing when that file exists.

Delete the file when you do want AutoExec to do everything it's set up to do.

Or instead of a file as a flag to control whether AutoExec should proceed, you could store the flag value in a table.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • That's what I've been doing up until now (renaming Autoexec), but I'm trying to get rid of all the clunkiness and workarounds in the system if possible. Ideally I want to C&R on closedown each time, as per the menu option, so don't think renaming/coding would work... – Wilskt Apr 23 '13 at 14:19
  • An AutoExec macro tells Access *"do this thing every time this db is opened".* You seem to want "*do this thing every time this db is opened **unless** the db open is due to compact".* Access doesn't have a built-in method to detect when the db open occurs as a result of compact. – HansUp Apr 23 '13 at 14:35
  • I don't understand why when the database is already open, and I run a compact, the autoexec runs again. Does Compact & Repair close down and re-open the database somehow behind the scenes? There's an option in the menu system to 'Compact on close' - it seems counterintuitive that it would want to re-run the Autoexec again at this stage. – Wilskt Apr 23 '13 at 14:39
  • When you compact the current db from the UI, Access creates a new db file to hold the compacted version, then deletes the old db file, renames the new file to the old name, and opens that new file. Since the db is opened again, AutoExec runs. With "Compact on Close" set to yes, when you close the db, Access creates the new compacted version of the db file but does not open it, so AutoExec does not run again (until you next open the db). – HansUp Apr 23 '13 at 14:45
  • Ah, that's different to how I remembered it - I though with 'Compact on Close' set to yes it still went through the macro afterwards, but it doesn't. SO that could solve the problem. :) – Wilskt Apr 23 '13 at 14:52
  • 2
    @Wilskt Remember, too, that if you are doing an on-demand Compact & Repair while in Access you can prevent the AutoExec macro from running when the file reloads by holding the [Shift] key down. – Gord Thompson Apr 23 '13 at 14:56
2

an old one that works (in MS Access 2010 and before at least) is to hold down the shift key when opening the database

user3484244
  • 21
  • 2