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>