The Run query in Access instruction is used to run queries on tables in an Access Database file.
Suppose we have the following fictitious Access Database file. In the Access database, there is a CarSales table that contains data such as Car Make, Car Model, Quantity Sold and RRP.
Configuring the instruction
- First, you will need to have a data step that contains a file upload/download property. Upload an Access file to this property.
- Create an action step to manipulate data
- Select the Run query in Access instruction from the list:
- Link to the Access database file
- Add the query that you want to run.
- Save and Run the step.
The Access file used in the article: CarSalesByModel.accdb
Tips and Tricks
An Update query can be used to change some of the values in an Access table. For Example, the following query will change the Quantity Sold and RRP for the Pontiac Grand Prix car:
An Insert Query can be used to add extra records to the Access table. For example, the following query will add a new record to the CarSales table:
A Delete Query can be used to delete specific records to the Access table. For example, the following query will delete all records where the Car Model is 'Econoline E150':
Create Stored Procedure
A Stored Procedure can also be made with the Run Query in Access step. For Example, the following will store a query in Access called 'SelectPopularCars' that will only show the cars that have 10 or more sales:
Check the number of rows modified to determine if this step was successful
This option on the Run query in Access instruction can be used as a check to see if the expected number of rows were changed. For example, we may wish to include a check that the query will affect at least one record.
The options for this check are "Is exactly", "Is more than" or "Is less than".