в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:46:48Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Access/View - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия