Clearing Obstacles for Automation in MS Access

There are many obstructive dialogue windows require manual response, which does really slow down the automation process. In one special case, MS Access is used to construct and update a considerable huge data warehouse. The update automation macro consists of over a hundred queries. The dialogue windows effectively destroy the automation itself.

Many of these dialogue windows can be set in Access Options. However, a special window cannot be resolved through setting. The message is “There isn't enough disk space or memory to undo the data changes this action query is about to make.” This is caused by running action queries on a large table.

Support of Microsoft regards this as an error message: http://support.microsoft.com/kb/161329, which also provides a number of solutions. Method 3, Setting the UseTransaction Property in an Action Query is particularily helpful:

If a stored action query causes the error, you can set its UseTransaction property to No. Note that if you do this, you will not able to roll back your changes if there is a problem or an error while the query is executing:

1. Open the query in Design view.
2. On the View menu, click Properties.
3. Click an empty space in the upper half of the query window to display the Query Properties dialog box.
4. Set the UseTransaction property to No.
5. Save the query and close it.

Most important, you would need to set no warnings, as follows:

1. In your first Macro row, right click mouse to insert a new row.
2. Click "Show All Actions" in Show/Hide menu.
3. In Action of your inserted first row, select SetWarnings.
4. Change Arguments value of the first row to "No".
5. Click Save.

Now you are ok to run Macros without any warning.