ProgrammingMSAccess.com

The ProgrammingMSAccess.COM Site

a pair of procedures for enumerating the user-defined views and their text in a database on a sql server.  update the string assignments for srvname and dbsname  in call_enumerate_views so that they point at the targeted server and database.  these sub procedure require a reference to the sql-dmo object library.  notice that they use the connect_to_server function procedure listed elsewhere on this site.

Sub call_enumerate_views()
Dim srvname As String
Dim dbsname As String

'Set string constants for procedure arguments
srvname = "cabarmada"
dbsname = "Northwind"

'Check for valid server connection before
'attempting to enumerate views
If connect_to_server(srvname) = True Then
enumerate_views dbsname
End If

End Sub


Sub enumerate_views(dbsname As String)
Dim vew1 As SQLDMO.View

'Print message to identify list of views
'with their text
Debug.Print "This is the list of views with their " & _
"SQL text for the " & dbsname & " database on " & _
"the " & srv1.Name; " server." & vbCrLf

'Iterate through user-defined views on targer server
'and print out text for each view
For Each vew1 In srv1.Databases(dbsname, "dbo").Views
If vew1.SystemObject = False Then
Debug.Print "Text for the " & vew1.Name & _
" view is:"
Debug.Print vew1.Text
End If
Next

'Clean up
Set vew1 = Nothing
srv1.Close
Set srv1 = Nothing

End Sub

Want to understand  Microsoft Access 2000 so that you can program it to do more tasks like this?  Get Programming Microsoft Access 2000 by Rick Dobson from Microsoft Press.  Learn more about the book by clicking here.

Copyright 2000 CAB, Inc. All rights reserved. Republication or redistribution of CAB, Inc. content, including by framing or similar means, is expressly prohibited without the prior written consent of CAB, Inc. CAB, Inc. shall not be liable for any errors in the content, or for any actions taken in reliance thereon.