Insert, Update and Delete SQL queries – MS Access

Do you need to use Insert, Update and Delete SQL queries? If so, in this article, I’m going to show you how to apply those types of queries in MS Access.

To start, let’s look at the Insert queries.

Insert Query

Insert queries can be used to insert records from one table into another table, or from a query into a table. The records inserted will be appended into that table (rather than replace the previous records). This feature is especially useful if you want to create an Archive table.

Here is the SQL structure for the Insert queries that you can use in MS Access:

(1) Using Insert Query to insert records from one table into another table (i.e., Inserting the records from Table2 into Table1):

 

INSERT INTO [Table1] ([Field1], [Field2])
SELECT [Table2].[Field1], [Table2].[Field2]
FROM [Table2]

 

(2) Using Insert Query to insert records from a query into a table (i.e., Inserting the records from Query1 into Table1):

 

INSERT INTO [Table1] ([Field1], [Field2])
SELECT [Query1].[Field1], [Query1].[Field2]
FROM [Query1]

 

If you want to keep only the latest records that are inserted into the table, you can first use a Delete query (explained below) to delete all the previous records in the table, and then execute an Insert query to input all the latest records into your desired table.

In the next section, I’m going to cover the Update query.

Update Query

You can use Update queries to update existing records in a table.

Here is the SQL structure for an Update query in MS Access:

(1) Update values in one table based on the values from another table (i.e., update the records in Table1 based on the values from Table2):

 

UPDATE [Table1]
INNER JOIN [Table2]
ON [Table1].[Field1] = [Table2].[Field1]
SET [Table1].[Field2] = [Table2].[Field2],
[Table1].[Field3] = [Table2].[Field3]

 

(2) Update values in one table based on the values from a query (i.e., update the records in Table1 based on the values from Query1):

 

UPDATE [Table1]
INNER JOIN [Query1]
ON [Table1].[Field1] = [Query1].[Field1]
SET [Table1].[Field2] = [Query1].[Field2],
[Table1].[Field3] = [Query1].[Field3]

 

Note that I used ‘Field1’ as the unique key (to connect between the queries and tables).

You can always use a combinations of Insert and Update queries. For example, you can use one query to insert the values into Table2, and then use an Update query to update the records in Table1 based on the values from Table2…

Let’s now look at the final piece of Delete queries.

Delete Query

(1) To start, here is a simple delete query that will delete the entire content of a table:

 

DELETE * FROM Table1

 

(2) You can also delete records from a table based on a condition:

 

DELETE * FROM Table1
WHERE Condition

 

(3) What if you want to Delete on join between 2 tables?
You can accomplish this task by using the following SQL structure:

 

DELETE [Table1].*
FROM [Table1]
WHERE EXISTS (SELECT * From [Table2] WHERE
[Table2].[Field1] = [Table1].[Field1]) = True

 

Note again that I used ‘Field1’ as the unique key (to connect between the tables).

(4) And if you want to Delete on join between a table and a query:

 

DELETE [Table1].*
FROM [Table1]
WHERE EXISTS (SELECT * From [Query1]
WHERE [Query1].[Field1] = [Table1].[Field1]) = True

 

In the next and final section, I’m going to show you some VBAs that you can use to execute the above commands.

VBA to execute Insert, Update and Delete queries in MS Access

You can use simple VBA commands to run the above queries. For example, you may add a button on an Access Form, where a user can simply press that button to execute those commands automatically.

(1) Here is the VBA syntax to execute Insert, Update and Delete queries in MS Access (where the “Query Name” should NOT be inserted within brackets “[]”):

 

DoCmd.OpenQuery "Insert Query Name"
DoCmd.OpenQuery "Update Query Name"
DoCmd.OpenQuery "Delete Query Name"

 

You can use one button to run all of these commands in the sequence you desire.

(2) Note that you can accomplish the same task of deleting all records in a table by using the following VBA:

 

DoCmd.RunSQL "Delete * [Table1]"

 

You can even add a condition as follows:

 

DoCmd.RunSQL "Delete * [Table1] Where Condition"

 

Using a query to update or delete records in a table (rather than using a table to update or delete records in another table) may negatively impact performance, and/or throw you some errors in Access (such as system resource exceeded). At times, it may be more advisable to use an Insert query to insert the values into one table, and then use that table to update and/or delete the records in another table…