Microsoft Access VBA Macro Programming

To run this from within VBA, you can use the DoCmd object (see Chapter 16 for more

information on this object):

Sub DeleteQuery()

DoCmd.SetWarnings False

DoCmd.OpenQuery "MyDeleteQuery"

DoCmd.SetWarnings True

End Sub

Notice that the DoCmd object has been used to switch the warning messages off and then

switch them back on afterward. This is because by running a Delete query the warning

message will normally be displayed to tell you that you are about to delete records. If you are

running this as part of a VBA procedure, you will not want this message appearing and

halting the execution of your code.

Do not forget to set the warnings back again. If you do not, you will receive no warnings

at any point within your Access database. If, for example, you decide to edit a query and then

close it, you will no longer get the warning message “Do you want to save this?” It will

default to “Save,” which could be disastrous if you have made changes that no longer work!

You can also run this by using the Execute method. You can either call the query or the

actual SQL:

CurrentDb.Execute "delete * from MyTable"

Do not forget that when deleting records memory is not released until the database is

compacted. If you do not do this, it will keep growing until you reach the 2Gb limit where it

will be corrupted.

Make Table Query

The Make Table query creates a new table based on the query. If a table already exists with

the same name, it will be overwritten, but you will see warning messages about this. Because

it does not return records, it is run as a command:

In its simplest form, it appears as:

SELECT* INTO MyNewTable

FROM MyTable;

This creates a copy of MyTable, calling it MyNewTable. You can also use criteria:

SELECT* INTO MyNewTable

FROM MyTable

where CustomerName="Richard"

158 Microsoft Access 2010 VBA Macro Programming