Contents

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

Public Enum ViewOrProc
    View
    Proc
End Enum

Public Sub CreateQuery(ByVal strDBPath As String, ByVal strSql As String, ByVal strQueryName As String, ByVal vpType As ViewOrProc)
    Dim objCat As Object
    Dim objCmd As Object
    
    On Error GoTo exit_point
    
    Set objCat = CreateObject(Class:="ADOX.Catalog")
    objCat.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                              "Data Source=" & strDBPath & ";" & _
                              "Jet OLEDB:Engine Type=5;" & _
                              "Persist Security Info=False;"
    
    Set objCmd = CreateObject(Class:="ADODB.Command")
    objCmd.CommandText = strSql
    
    If vpType = View Then
        Call objCat.Views.Append(Name:=strQueryName, Command:=objCmd)
    ElseIf vpType = Proc Then
        Call objCat.Procedures.Append(Name:=strQueryName, Command:=objCmd)
    End If
    
exit_point:
    Set objCat = Nothing
    
    If Err.Number Then
        Call Err.Raise(Number:=Err.Number, Description:=Err.Description)
    End If
End Sub

Public Enum ViewOrProc

View

Proc

End Enum

Public Sub CreateQuery(ByVal strDBPath As String, ByVal strSql As String, ByVal strQueryName As String, ByVal vpType As ViewOrProc)

Dim objCat As Object

Dim objCmd As Object

On Error GoTo exit_point

Set objCat = CreateObject(Class:="ADOX.Catalog")

objCat.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & strDBPath & ";" & _

"Jet OLEDB:Engine Type=5;" & _

"Persist Security Info=False;"

Set objCmd = CreateObject(Class:="ADODB.Command")

objCmd.CommandText = strSql

If vpType = View Then

Call objCat.Views.Append(Name:=strQueryName, Command:=objCmd)

ElseIf vpType = Proc Then

Call objCat.Procedures.Append(Name:=strQueryName, Command:=objCmd)

End If

exit_point:

Set objCat = Nothing

If Err.Number Then

Call Err.Raise(Number:=Err.Number, Description:=Err.Description)

End If

End Sub

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

Public Enum ViewOrProc
    View
    Proc
End Enum

Public Sub ModifyQuery(ByVal strDBPath As String, ByVal strSql As String, ByVal strQueryName As String)
    Dim objCat As Object
    Dim objCmd As Object
    Dim vpType As ViewOrProc

    On Error GoTo exit_point
    
    Set objCat = CreateObject(Class:="ADOX.Catalog")
    objCat.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                              "Data Source=" & strDBPath & ";" & _
                              "Jet OLEDB:Engine Type=5;" & _
                              "Persist Security Info=False;"
    
    On Error Resume Next
        Set objCmd = objCat.Views(strQueryName).Command
        If Not objCmd Is Nothing Then
            vpType = View
        Else
            Set objCmd = objCat.Procedures(strQueryName).Command
            If Not objCmd Is Nothing Then
                vpType = Proc
            End If
        End If
    On Error GoTo exit_point
    
    If objCmd Is Nothing Then GoTo exit_point
    
    objCmd.CommandText = strSql
    
    If vpType = View Then
        Set objCat.Views(strQueryName).Command = objCmd
    ElseIf vpType = Proc Then
        Set objCat.Procedures(strQueryName).Command = objCmd
    End If
    
exit_point:
    Set objCat = Nothing
    
    If Err.Number Then
        Call Err.Raise(Number:=Err.Number, Description:=Err.Description)
    End If
End Sub

Public Enum ViewOrProc

View

Proc

End Enum

Public Sub ModifyQuery(ByVal strDBPath As String, ByVal strSql As String, ByVal strQueryName As String)

Dim objCat As Object

Dim objCmd As Object

Dim vpType As ViewOrProc

On Error GoTo exit_point

Set objCat = CreateObject(Class:="ADOX.Catalog")

objCat.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & strDBPath & ";" & _

"Jet OLEDB:Engine Type=5;" & _

"Persist Security Info=False;"

On Error Resume Next

Set objCmd = objCat.Views(strQueryName).Command

If Not objCmd Is Nothing Then

vpType = View

Else

Set objCmd = objCat.Procedures(strQueryName).Command

If Not objCmd Is Nothing Then

vpType = Proc

End If

On Error GoTo exit_point

If objCmd Is Nothing Then GoTo exit_point

objCmd.CommandText = strSql

If vpType = View Then

Set objCat.Views(strQueryName).Command = objCmd

ElseIf vpType = Proc Then

Set objCat.Procedures(strQueryName).Command = objCmd

End If

exit_point:

Set objCat = Nothing

If Err.Number Then

Call Err.Raise(Number:=Err.Number, Description:=Err.Description)

End If

End Sub

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

Public Sub DeleteQuery(ByVal strDBPath As String, ByVal strQueryName As String)
    Dim objCat As Object
    Dim lngCount As Long
    
    Set objCat = CreateObject(Class:="ADOX.Catalog")
    objCat.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                              "Data Source=" & strDBPath & ";" & _
                              "Jet OLEDB:Engine Type=5;" & _
                              "Persist Security Info=False;"
    
    With objCat
        lngCount = .Procedures.Count + .Views.Count
        On Error Resume Next
            Call .Procedures.Delete(strQueryName)
            Call .Views.Delete(strQueryName)
        On Error GoTo exit_point
        If .Procedures.Count + .Views.Count = lngCount Then
            Err.Number = 3265
            Err.Description = "Item cannot be found in the collection corresponding to the requested name or ordinal."
        End If
    End With
    
exit_point:
    Set objCat = Nothing
    
    If Err.Number Then
        Call Err.Raise(Number:=Err.Number, Description:=Err.Description)
    End If
End Sub

Public Sub DeleteQuery(ByVal strDBPath As String, ByVal strQueryName As String)

Dim objCat As Object

Dim lngCount As Long

Set objCat = CreateObject(Class:="ADOX.Catalog")

objCat.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & strDBPath & ";" & _

"Jet OLEDB:Engine Type=5;" & _

"Persist Security Info=False;"

With objCat

lngCount = .Procedures.Count + .Views.Count

On Error Resume Next

Call .Procedures.Delete(strQueryName)

Call .Views.Delete(strQueryName)

On Error GoTo exit_point

If .Procedures.Count + .Views.Count = lngCount Then

Err.Number = 3265

Err.Description = "Item cannot be found in the collection corresponding to the requested name or ordinal."

End If

End With

exit_point:

Set objCat = Nothing

If Err.Number Then

Call Err.Raise(Number:=Err.Number, Description:=Err.Description)

End If

End Sub

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

Public Function RunQuery(ByVal strDBPath As String, ByVal strQueryname As String, ParamArray parArgs() As Variant) As Object
    Dim objCmd As Object
    Dim objRec As Object
    Dim varArgs() As Variant
    Dim lngPrm As Long
    
    Set objCmd = CreateObject("ADODB.Command")
    
    If UBound(parArgs) = -1 Then
        varArgs = VBA.Array("")
    Else
        varArgs = parArgs
    End If
    
    With objCmd
        .ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=" & strDBPath & ";" & _
                            "Jet OLEDB:Engine Type=5;" & _
                            "Persist Security Info=False;"
        .CommandText = strQueryname
        If UBound(parArgs) = -1 Then
            Set objRec = .Execute(Options:=4)
        Else
            varArgs = parArgs
            Set objRec = .Execute(Parameters:=varArgs, Options:=4)
            On Error Resume Next
                For lngPrm = .Parameters.Count - 1 To 0 Step -1
                    Call .Parameters.Delete(lngPrm)
                Next lngPrm
            On Error GoTo 0
        End If
    End With
    
    Set RunQuery = objRec
    
    Set objRec = Nothing
End Function

Public Function RunQuery(ByVal strDBPath As String, ByVal strQueryname As String, ParamArray parArgs() As Variant) As Object

Dim objCmd As Object

Dim objRec As Object

Dim varArgs() As Variant

Dim lngPrm As Long

Set objCmd = CreateObject("ADODB.Command")

If UBound(parArgs) = -1 Then

varArgs = VBA.Array("")

Else

varArgs = parArgs

End If

With objCmd

.ActiveConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & strDBPath & ";" & _

"Jet OLEDB:Engine Type=5;" & _

"Persist Security Info=False;"

.CommandText = strQueryname

If UBound(parArgs) = -1 Then

Set objRec = .Execute(Options:=4)

Else

varArgs = parArgs

Set objRec = .Execute(Parameters:=varArgs, Options:=4)

On Error Resume Next

For lngPrm = .Parameters.Count - 1 To 0 Step -1

Call .Parameters.Delete(lngPrm)

Next lngPrm

On Error GoTo 0

End If

End With

Set RunQuery = objRec

Set objRec = Nothing

End Function

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

Public Sub CreateSimpleQuery()
    Const strDB As String = "C:\Demo.accdb"
    Const strSql As String = "SELECT * FROM [Personnel] WHERE [Personnel].[Division]='HR';"
    Const strQueryName As String = "qrySimple"
    
    Call CreateQuery(strDB, strSql, strQueryName, View)
End Sub

Public Sub CreateSimpleQuery()

Const strDB As String = "C:\Demo.accdb"

Const strSql As String = "SELECT * FROM [Personnel] WHERE [Personnel].[Division]='HR';"

Const strQueryName As String = "qrySimple"

Call CreateQuery(strDB, strSql, strQueryName, View)

End Sub

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

Public Sub CreateActionQuery()
    Const strDB As String = "C:\Demo.accdb"
    Const strSql As String = "UPDATE [Personnel] " & _
                             "SET [Personnel].[LAST_NAME] = Replace([LAST_NAME],""-"","" "") " & _
                             "WHERE ((([Personnel].[LAST_NAME]) Like ""*-*""));"
    
    Const strQueryName As String = "qryAction"
    
    Call CreateQuery(strDB, strSql, strQueryName, View)
End Sub

Public Sub CreateActionQuery()

Const strDB As String = "C:\Demo.accdb"

Const strSql As String = "UPDATE [Personnel] " & _

"SET [Personnel].[LAST_NAME] = Replace([LAST_NAME],""-"","" "") " & _

"WHERE ((([Personnel].[LAST_NAME]) Like ""*-*""));"

Const strQueryName As String = "qryAction"

Call CreateQuery(strDB, strSql, strQueryName, View)

End Sub

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

Public Sub CreateParameterQuery()
    Const strDB As String = "C:\Users\Jon von der Heyden\Desktop\Desktop Files\test.accdb"
    Const strSql As String = "PARAMETERS prmDivision LongText; " & _
                             "SELECT * FROM [Personnel] " & _
                             "WHERE [Personnel].[Division] = prmDivision;"
    
    Const strQueryName As String = "qryParams"
    
    Call CreateQuery(strDB, strSql, strQueryName, Proc)
End Sub

Public Sub CreateParameterQuery()

Const strDB As String = "C:\Users\Jon von der Heyden\Desktop\Desktop Files\test.accdb"

Const strSql As String = "PARAMETERS prmDivision LongText; " & _

"SELECT * FROM [Personnel] " & _

"WHERE [Personnel].[Division] = prmDivision;"

Const strQueryName As String = "qryParams"

Call CreateQuery(strDB, strSql, strQueryName, Proc)

End Sub

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

Public Sub ModifyAQuery()
    Const strDB As String = "C:\Demo.accdb"
    Const strSql As String = "UPDATE [Personnel] " & _
                             "SET [Personnel].[LAST_NAME] = Replace([LAST_NAME],""-"","" "") " & _
                             "WHERE ((([Personnel].[LAST_NAME]) Like ""%-%""));"
    
    Const strQueryName As String = "qryAction"
    
    Call ModifyQuery(strDB, strSql, strQueryName)
End Sub

Public Sub ModifyAQuery()

Const strDB As String = "C:\Demo.accdb"

Const strSql As String = "UPDATE [Personnel] " & _

"SET [Personnel].[LAST_NAME] = Replace([LAST_NAME],""-"","" "") " & _

"WHERE ((([Personnel].[LAST_NAME]) Like ""%-%""));"

Const strQueryName As String = "qryAction"

Call ModifyQuery(strDB, strSql, strQueryName)

End Sub

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

Delete a Query

Public Sub DeleteAQuery()
    Const strDB As String = "C:\Demo.accdb"
    Const strQueryName As String = "qryOld"
    
    Call DeleteQuery(strDB, strQueryName)
End Sub

Public Sub DeleteAQuery()

Const strDB As String = "C:\Demo.accdb"

Const strQueryName As String = "qryOld"

Call DeleteQuery(strDB, strQueryName)

End Sub

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

Execute an Action Query

Public Sub RunActionQuery()
    Const strDB As String = "C:\Demo.accdb"
    Const strQueryName As String = "qryAction"
    
    Call RunQuery(strDB, strQueryName)
End Sub

Public Sub RunActionQuery()

Const strDB As String = "C:\Demo.accdb"

Const strQueryName As String = "qryAction"

Call RunQuery(strDB, strQueryName)

End Sub

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

Public Sub RunSelectQuery()
    Dim objRec As Object
    Dim lngField As Long
    
    Const strDB As String = "C:\Demo.accdb"
    Const strQueryName As String = "qrySimple"
    
    On Error Resume Next
        Set objRec = RunQuery(strDB, strQueryName, "HR")
    On Error GoTo 0
    
    If Not objRec Is Nothing Then
        With Sheet1.Range("A1")
            For lngField = 1 To objRec.Fields.Count
                .Cells(1, lngField).Value = objRec.Fields(lngField - 1).Name
            Next lngField
            Call .Offset(1, 0).CopyFromRecordset(objRec)
        End With
    End If
    
    Set objRec = Nothing
End Sub

Public Sub RunSelectQuery()

Dim objRec As Object

Dim lngField As Long

Const strDB As String = "C:\Demo.accdb"

Const strQueryName As String = "qrySimple"

On Error Resume Next

Set objRec = RunQuery(strDB, strQueryName, "HR")

On Error GoTo 0

If Not objRec Is Nothing Then

With Sheet1.Range("A1")

For lngField = 1 To objRec.Fields.Count

.Cells(1, lngField).Value = objRec.Fields(lngField - 1).Name

Next lngField

Call .Offset(1, 0).CopyFromRecordset(objRec)

End With

End If

Set objRec = Nothing

End Sub

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

Public Sub RunParamQuery()
    Dim objRec As Object
    Dim lngField As Long
    
    Const strDB As String = "C:\Demo.accdb"
    Const strQueryName As String = "procPersonnelByDivision"
    
    On Error Resume Next
        Set objRec = RunQuery(strDB, strQueryName, "HR")
    On Error GoTo 0
    
    If Not objRec Is Nothing Then
        With Sheet1.Range("A1")
            For lngField = 1 To objRec.Fields.Count
                .Cells(1, lngField).Value = objRec.Fields(lngField - 1).Name
            Next lngField
            Call .Offset(1, 0).CopyFromRecordset(objRec)
        End With
    End If
    
    Set objRec = Nothing
End Sub

Public Sub RunParamQuery()

Dim objRec As Object

Dim lngField As Long

Const strDB As String = "C:\Demo.accdb"

Const strQueryName As String = "procPersonnelByDivision"

On Error Resume Next

Set objRec = RunQuery(strDB, strQueryName, "HR")

On Error GoTo 0

If Not objRec Is Nothing Then

With Sheet1.Range("A1")

For lngField = 1 To objRec.Fields.Count

.Cells(1, lngField).Value = objRec.Fields(lngField - 1).Name

Next lngField

Call .Offset(1, 0).CopyFromRecordset(objRec)

End With

End If

Set objRec = Nothing

End Sub

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

Public Sub CreateStoredProc()
    Const strDB As String = "C:\Demo.accdb"
    Const strSql As String = "CREATE PROC procPersonnelByDivision(inDivision VARCHAR) " & _
                             "AS SELECT * FROM [Personnel] WHERE [Personnel].[Division] = inDivision;"
    
    Const strQueryName As String = "Temp"
    
    Call CreateQuery(strDB, strSql, strQueryName, Proc)
    Call RunQuery(strDB, strQueryName)
    Call DeleteQuery(strDB, strQueryName)
End Sub

Public Sub CreateStoredProc()

Const strDB As String = "C:\Demo.accdb"

Const strSql As String = "CREATE PROC procPersonnelByDivision(inDivision VARCHAR) " & _

"AS SELECT * FROM [Personnel] WHERE [Personnel].[Division] = inDivision;"

Const strQueryName As String = "Temp"

Call CreateQuery(strDB, strSql, strQueryName, Proc)

Call RunQuery(strDB, strQueryName)

Call DeleteQuery(strDB, strQueryName)

End Sub

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

Potential Errors

ERROR	REMARK
-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.

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

Introduction

Create a new View or Procedure

Modify an existing View or Procedure

Delete an existing View or Procedure

Execute an existing View or Procedure

Examples

Create a Simple Query

Create an Action Query

Create a Parameter Query

Modify a Query

Delete a Query

Execute an Action Query

Execute a Select Query

Execute a Parameter Query

Create a Stored Query / Parameter Query

Potential Errors

References