VBA/Excel/Access/Word/Access/View

Create view

   <source lang="vb">

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

</source>
   
  

Creating a View Based on a Table with SQL command

   <source lang="vb">

" 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

</source>
   
  

Deleting a View with SQL command

   <source lang="vb">

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

</source>
   
  

Execute the view

   <source lang="vb">

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

</source>
   
  

Generating a List of Saved Views

   <source lang="vb">

" 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

</source>