Create Views/Procedures/Queries Example

This example creates and executes both an action query and a standard select query by creating an ADO command object and appending it to the ADO procedures and views collections.

Notes

Currently creating procedures and views is not supported in ADPs with the version of the SQL provider shipped with Microsoft Access 2000. A reference to "Microsoft ADO ext. 2.1 for DDL and Security" must be set to use this example.

The RunQuery function is required for this procedure to run.

Sub CreateQueryX()
    Dim catNorthwind As New ADOX.Catalog
    Dim cdView As New ADODB.Command
    Dim cdProc As New ADODB.Command
    Dim tblTemp As New ADOX.Table
    
    Set catNorthwind.ActiveConnection = CurrentProject.Connection

    With catNorthwind
        Set cdProc.ActiveConnection = CurrentProject.Connection
        Set cdView.ActiveConnection = CurrentProject.Connection
        
        With tblTemp
            .Name = "tblTemp"
            With .Columns
                .Append "EmpID", adInteger
                .Append "LastName", adVarWChar
                .Append "FirstName", adVarWChar
            End With
        End With
        .Tables.Append tblTemp
        
        ' Create a new action Query.
        cdProc.CommandText = "Insert Into tbltemp " _ & "(EmpID, LastName, FirstName) Select EmployeeID, " _ & "LastName, FirstName From Employees Where Title =" _ & "'Sales Representative'"
        
        ' Append to database
        .Procedures.Append "PopulateTmptbl", cdProc
        
        RunQuery "PopulateTmptbl"
        
        ' Create new select Query
        cdView.CommandText = "SELECT * FROM tblTemp"
        .Views.Append "NewQuery", cdView

        RunQuery "NewQuery"                        
                        
 ' Delete new objects because this is a demonstration.
        .Views.Delete "NewQuery"
        .Procedures.Delete "PopulateTmptbl"
        .Tables.Delete tblTemp.Name
    End With
    Set catNorthwind = Nothing
End Sub

Function RunQuery(qryName As String)
    Dim qryTemp As Object
    Dim cat As New ADOX.Catalog
    Dim intNumRecs As Integer
    
    Set cat.ActiveConnection = CurrentProject.Connection
    
    On Error Resume Next
    Set qryTemp = cat.Procedures(qryName)
    If Err.Number <> 0 Then
        Set qryTemp = cat.Views(qryName)
        Err.Number = 0
    End If
    On Error GoTo 0
    
    If TypeOf qryTemp Is ADOX.View Then
        'If Select query display number of records
        Debug.Print "Number records in " & qryName
          Debug.Print DLookup("Count(*)", qryName)
    Else
        'If Action query display affected records
        qryTemp.Command.Execute intNumRecs
        Debug.Print "Number of Records affected"
          Debug.Pring intNumRecs
    End If
    
    Set cat = Nothing
    Set qryTemp = Nothing

End Function