Creating a View Based on a Table with SQL command : View « Access « VBA / Excel / Access / Word






  1. VBA / Excel / Access / Word
  2. Access
  3. View

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








Related examples in the same category

1.Create view
2.Generating a List of Saved Views
3.Deleting a View with SQL command
4.Execute the view