Create, Modify, Delete and Run Access Queries using Excel VBA

Introduction

The focus of this article is to demonstrate how to use ADO/ADOX, in an Excel VBA project, to manipulate Access views and procedures, collectively referred to as ‘queries’. In truth, generally when dealing with Microsoft Access, DAO (Data Access Objects) would be a better object library than ADO. ADOX, however, is extensible to other databases, not just Microsoft Access.

ADOX (Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security ) is an extension to the ADO objects and programming model. ADOX includes objects for schema creation and modification, as well as security. It exposes additional objects for creating, modifying, and deleting schema objects, such as tables, queries and procedures.

ADOX capabilities extend far beyond what is described in this article. For more information on ADOX see the links in References.

Storing long and complicated SQL statements within your Excel VBA code can be messy. It also makes it a little harder to modify, never mind the fact that some SQL procedures could probably be reused, either within the same project or by other projects. It’s a good idea to maintain your SQL queries within the database itself. One should thus use Excel VBA to execute the queries, rather than contain the query code and logic.

The following article aims to illustrate how you can go about creating, modifying, deleting and executing database queries, using Excel VBA. See the examples further down for context on how to implement the following sub-routines.

Create a new View or Procedure

This sub-routine has the following arguments:

  • strDBPath: Required. The path of the directory folder where the Access database is housed.
  • strSql: Required. The SQL of query / procedure that you want to create.
  • vpType: Required. View if you want to add the query to the Views collection, otherwise Procedure to add it to the Procedures collection. Use the Procedures collection for Parameter Queries (Stored Procedures). Use the Views collection for everthing else.

Modify an existing View or Procedure

This sub-routine has the following arguments:

  • strDBPath: Required. The path of the directory folder where the Access database is housed.
  • strSql: Required. The SQL of query / procedure that you want to replace the existing query SQL with.
  • strQueryName: Required. The name of the query (Procedure or View) that you wish to amend.

Delete an existing View or Procedure

This sub-routine has the following arguments:

  • strDBPath: Required. The path of the directory folder where the Access database is housed.
  • strQueryName: Required. The name of the query (Procedure or View) that you wish to delete.

Execute an existing View or Procedure

This sub-routine has the following arguments:

  • strDBPath: Required. The path of the directory folder where the Access database is housed.
  • strQueryName: Required. The name of the query (Procedure or View) that you wish to execute.
  • parArgs: Optional. The Procedure parameters.

Examples

* Note: When the term ‘View’ or ‘Proc’ is used as a sub-routine argument, it is referring to the ViewOrProc Enum (as illustrated in some of the previous procedures).

Create a Simple Query

This will create a new query, in the Views collection, called ‘qrySimple’. A Simple Query (sometimes simply referred to as ‘Select Query’) merely grabs data from one of more tables/views.

Create an Action Query

This will create a new query, in the Views collection, called ‘qryAction’. An Action Query is a type of query that can add, change or delete records or objects.

Create a Parameter Query

This will create a new query, in the Procedures collection, called ‘qryParams’. A Parameter Query (typically referred to as a ‘Stored Procedure’), can be a Simple Query or an Action Query. It is differentiated because it allows the caller to pass one or more parameters. In this exhibit the query will yield a table of results from a table called ‘Personnel’, however only for a particular division. ‘Division’ is the parameter, which is also a column / field in the ‘Personnel’ table.

Modify a Query

This code replaces the SQL contained the ‘qryAction’ with the SQL in strSQL.

Delete a Query

This will delete a query called ‘qryOld’. Once deleted, the action cannot be undone / revoked.

Execute an Action Query

This will run an Action Query called ‘qryAction’. No further code is required, since we do not anticipate any results to be returned.

Execute a Select Query

We expect that executing the query called ‘qrySimple’ should yield a table of results. The results are returned to an ADO Recordset object. This code then illustrates how to drop the results of the recordset to a sheet.

Execute a Parameter Query

This is similar to Execute a Select Query, except in this case we pass a parameter, ‘HR’, to the Query. Thus the results unloaded to the sheet will be filtered for HR division only.

Create a Stored Query / Parameter Query

Parameter Queries can be created by executing SQL on the database, specifically see ‘CREATE PROC’.

Potential Errors

ERRORREMARK
-2147467259
Could not find file strDBPath.
The database directory or filename is not valid, or it is inaccessible.
-2147217816
Object strQueryName already exists.
The query already exists in either the Views collection or Procedures collection.
3265
Item cannot be found in the collection corresponding to the requested name or ordinal.
The query you are trying to modify, delete or run does not exist.
-2147467259
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
The UPDATE or INSERT statement cannot be executed as it will create forbidden duplicates.
-2147217904
Too few parameters. Expected n.
Ensure ALL parameters are passed to the RunQuery functions’ parArgs argument.
-2147217900
Invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT’, or ‘UPDATE’.
The SQL statement passed via CreateQuery or ModifyQuery is invalid.

References