VBA/Excel/Access/Word/Access/View

Create view

 
Sub CreateQuery()
    
    Dim cat As New ADOX.Catalog
    Dim cd As New ADODB.rumand
    Dim sSQL As String
    
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=C:\mydb.mdb;"
    
    sSQL = "SELECT * FROM Newtable"
    
    cd.rumandText = sSQL
    
    cat.Views.Append "Newquery", cd
End Sub

Creating a View Based on a Table with SQL command

 
" set up a reference to the 
" Microsoft ActiveX Data Objects Library 
" in the References dialog box 
Sub Create_View() 
    Dim conn As ADODB.Connection 
    Set conn = CurrentProject.Connection 
    On Error GoTo ErrorHandler 
    conn.Execute "CREATE VIEW vw_Employees AS"& _ 
                 "SELECT Employees.EmployeeId as [Employee Id],"& _ 
                 "FirstName & chr(16) & LastName as [Full Name], " & _ 
                 "Title, ReportsTo, Orders.OrderId as [Order Id] " & _ 
                 "FROM Employees"& _ 
                 "INNER JOIN Orders ON"& _ 
                 "Orders.EmployeeId = Employees.EmployeeId;" 
    Application.RefreshDatabaseWindow 
ExitHere: 
    If Not conn Is Nothing Then 
        If conn.State = adStateOpen Then conn.Close 
    End If 
    Set conn = Nothing 
    Exit Sub 
ErrorHandler: 
    If Err.Number = -2147217900 Then 
        conn.Execute "DROP VIEW vw_Employees" 
        Resume 
    Else 
        Debug.Print Err.Number & ":" & Err.Description 
        Resume ExitHere 
    End If 
End Sub

Deleting a View with SQL command

 
Sub Delete_View() 
    Dim conn As ADODB.Connection 
    Set conn = CurrentProject.Connection 
    On Error GoTo ErrorHandler 
    conn.Execute "DROP VIEW vw_Employees" 
ExitHere: 
    If Not conn Is Nothing Then 
        If conn.State = adStateOpen Then conn.Close 
    End If 
    Set conn = Nothing 
    Exit Sub 
ErrorHandler: 
    If Err.Number = -2147217865 Then 
        MsgBox "The view was already deleted." 
    Exit Sub 
    Else 
        MsgBox Err.Number & ":" & Err.Description 
        Resume ExitHere 
    End If 
End Sub

Execute the view

 
Sub ExecuteView()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.Open "vwClients"
    MsgBox rst.RecordCount
End Sub

Generating a List of Saved Views

 
" set up a reference to the
" Microsoft ADO Ext. 2.7 for DDL and Security
Sub List_Views()
    Dim cat As New ADOX.Catalog
    Dim myView As ADOX.View
    cat.ActiveConnection = CurrentProject.Connection
    For Each myView In cat.Views
        Debug.Print myView.Name
    Next myView
End Sub