How to Create an MS Access Query that Deletes Data 'ms access delete query'

Microsoft Access provides a powerful feature called queries, which allow users to interact with their database data in various ways. One of these interaction methods is through Delete queries, which enable the removal of specific records from your tables based on certain criteria.

In this article, we will walk you through creating an MS Access Query that deletes data using both Visual Basic for Applications (VBA) and the graphical user interface (GUI).

## Method 1: Using VBA

To create a Delete query using VDAO Object Model in VBA, follow these steps:

### Step 1. Open the Visual Basic Editor

Firstly, open your MS Access application and then click on 'File' > 'Visual Basic'. This will open the Visual Basic Editor.

### Step two: Create an instance of DAO.QueryDef object

Next, you need to create a new DAO QueryDef object called `Qdf` using the following code:

```vb

Dim Qdf As DAO.QueryDelete

Set Qdf = New DAO.QueryDelete

```

### Step 3. Define your Delete query criteria in SQL syntax

Now that we have an instance of our DAO.QueryDef object, let's define what records should be deleted from the table called 'myTable' where the field 'Name' equals 'John Doe'.

```vb

Qdf.SQL = "DELETE * FROM myTable WHERE Name = 'John Doe';&#13&#10"

```

### Step 4. Execute your Delete query

Finally, we can execute our delete query by calling the `Execute` method on our DAO.QueryDef object.

```vb

Qdf.Execute dbOptionImmediate

Set Qdf = Nothing

```

This will permanently delete all records from 'myTable' that meet the criteria specified in step 3.

## Method 2: Using GUI

Alternatively, you can create a Delete query using Access's graphical user interface (GUI). Here are the steps:

### Step 1. Open your MS Access database and navigate to the 'Queries' tab

Double-click on 'Query Design' under the 'Database Objects' section in Object Browser pane.

### Step 2: Select an existing table or create a new one

In this example, we will use an existing table called `Customers`. If you don't have such a table already created within your database, follow these steps:

1. Go to 'File' > 'New' and then click on 'Table.'

2. Name the new table as 'Customers' (without quotes).

3. Close this dialog box.

### Step 4: Open Query Design View

Now that we have our target table selected, let's open up its query design view:

1. Go to 'Query' > 'Delete SQL'.

Alternatively, you can also press Ctrl+Shift+F11 on Windows or Command + Shift + F11 on Mac while in the 'Design' mode.

### Step 5: Design your Delete Query

In this step, we need to specify what records should be deleted from our table. To do so:

1. Double-click anywhere within the SQL text area at top-left corner of query design window.

2. Enter or paste following code into it:

```sql

SELECT * FROM Customers;

```

### Step 6: Run Your Delete Query

Finally, click 'Run' button located above left side panel under 'Query Type' dropdown list (looks like a blue play icon with white arrow pointing rightwards) to execute the delete query. This will permanently remove all records from `Customers` table that match criteria defined in step 5.

By following these steps, you have successfully created an MS Access Query that deletes data using both VBA and GUI methods!

Ms access delete query