ACC: How to Use Automation to List SQL Server Objects

ID: Q154011


The information in this article applies to:
  • Microsoft Access versions 7.0, 97


SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article shows you how to use Automation to list the objects that exist in a Microsoft SQL Server version 6.0 or 6.5 database.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.


MORE INFORMATION

Microsoft SQL Server versions 6.0 and later expose a new object hierarchy called the SQL Server Distributed Management Object Model (SQL-DMO). This consists of a series of collections and objects similar to the Object model of the Microsoft Jet database engine.

Microsoft Access developers can make use of this functionality to query SQL Server for table names, relationships, columns, and so on.

In order to use the SQL-DMO features, you must first install the workstation installation of SQL Server to provide client utilities for working with SQL Server. For more information about installing these utilities, check your Microsoft SQL Server 6.0 or 6.5 documentation.

The following examples show you how to use Visual Basic for Application's code in Microsoft Access 97 or Microsoft Access 7.0 to list the databases, tables, or columns in a Microsoft SQL Server 6.0 or 6.5 table.

  1. Install the Microsoft SQL Server 6.0 or 6.5 Workstation/Client Utilities.


  2. Open Microsoft Access 97 or 7.0 and create a new module.


  3. On the Tools menu, click References, and click to select "Microsoft SQL OLE Object Library." This option will be available after you install the workstation/client utilities for SQL Server.


  4. Type the following lines in the Declarations section:
    
          Option Explicit
          Public Const strServer = "YourServer"  ' Indicate your server.
          Public Const strUser = "sa"            ' Indicate your user name.
          Public Const strPwd = ""               ' Indicate your password. 


Example 1

The following example shows you how to create a list of databases on a Microsoft SQL Server Version 6.0 or 6.5:

  1. Create the following function:
    
          Function EnumSQLDatabases() As Boolean
             On Error GoTo EnumSQLDatabases_Err
             Dim objSQL As Object, objSQLdb As Object
             Dim strMsg As String
             Set objSQL = CreateObject("Sqlole.SQLServer")
             ' Connect to SQL server...
             objSQL.Connect strServer, strUser, strPwd
             ' Print server info.
             Debug.Print "------------------------------------------------"
             Debug.Print "SQL Server: " & strServer
             Debug.Print "------------------------------------------------"
             For Each objSQLdb In objSQL.Databases
                 ' Print database(s) name.
                 Debug.Print "...Database: " & objSQLdb.Name
             Next
             ' Disconnect from SQL server.
             objSQL.DisConnect
             EnumSQLDatabases = True
          EnumSQLDatabases_End:
             Exit Function
          EnumSQLDatabases_Err:
             ' Basic error handling.
             MsgBox Err.Description, vbInformation, "SQL OLE Automation"
             Resume EnumSQLDatabases_End
          End Function 


  2. Type the following line in the Debug window, and then press ENTER:
    ?EnumSQLDatabases()
    Note the list of Databases contained on the SQL Server, for example:
    
           ------------------------------------------------
           SQL Server: MyTestSQLServer
           ------------------------------------------------
           ...Database: pubs
           ......Tables:
           ..........authors
           ..........discounts
           ..........employee
           ..........jobs
           ..........pub_info
           ..........publishers
           ..........roysched
           ..........sales
           ..........stores
           ..........sysalternates
           ..........sysarticles
           ..........syscolumns
           ..........syscomments
           ..........sysconstraints
           ..........sysdepends
           ..........sysindexes
           ..........syskeys
           ..........syslogs
           ..........sysobjects
           ..........sysprocedures
           ..........sysprotects
           ..........syspublications
           ..........sysreferences
           ..........syssegments
           ..........syssubscriptions
           ..........systypes
           ..........sysusers
           ..........titleauthor
           ..........titles
           True 
    Note that your output will be similar (depending on the installed databases in your SQL Server).


Example 2

The following example shows you how to list the tables in a Microsoft SQL Server version 6.0 or 6.5 database:

  1. Create the following Function:
    
          Function EnumSQLTables() As Boolean
             On Error GoTo EnumSQLTables_Err
             Dim objSQL As Object, objSQLdb As Object
             Dim objSQLtbl As Object
             Dim strMsg As String
             Set objSQL = CreateObject("Sqlole.SQLServer")
             ' Connect to SQL server...
             objSQL.Connect strServer, strUser, strPwd
             Debug.Print "------------------------------------------------"
             Debug.Print "SQL Server: " & strServer
             Debug.Print "------------------------------------------------"
             For Each objSQLdb In objSQL.Databases
                 ' Print database(s) name.
                 Debug.Print "...Database: " & objSQLdb.Name
                 Debug.Print "......Tables: "
                 For Each objSQLtbl In objSQLdb.Tables
                     ' Print table name(s).
                     Debug.Print ".........." & objSQLtbl.Name
                 Next
             Next
             ' Disconnect from SQL server...
             objSQL.DisConnect
             EnumSQLTables = True
          EnumSQLTables_End:
             Exit Function
          EnumSQLTables_Err:
             ' Basic error handling.
             MsgBox Err.Description, vbInformation, "SQL OLE Automation"
             Resume EnumSQLTables_End
          End Function 


  2. Type the following line in the Debug window, and then press ENTER:
    ?EnumSQLTables()
    Note that you receive the list of tables in the Specified SQL Server Database, for example:
    
           ------------------------------------------------
           SQL Server: MyTestSQLServer
           ------------------------------------------------
           ...Database: pubs
           ......Tables:
           ..........authors
           ..........discounts
           ..........employee
           ..........jobs
           ..........pub_info
           ..........publishers
           ..........roysched
           ..........sales
           ..........stores
           ..........sysalternates
           ..........sysarticles
           ..........syscolumns
           ..........syscomments
           ..........sysconstraints
           ..........sysdepends
           ..........sysindexes
           ..........syskeys
           ..........syslogs
           ..........sysobjects
           ..........sysprocedures
           ..........sysprotects
           ..........syspublications
           ..........sysreferences
           ..........syssegments
           ..........syssubscriptions
           ..........systypes
           ..........sysusers
           ..........titleauthor
           ..........titles 
    Note that your output will be similar (depending on the installed databases in your SQL Server).


Example 3

The following example shows you how to get a list of columns in a Microsoft SQL Server Table:

  1. Create the following function:
    
          Function EnumSQLColumns() As Boolean
             On Error GoTo EnumSQLColumns_Err
             Dim objSQL As Object, objSQLdb As Object
             Dim objSQLtbl As Object, objSQLfld As Object
             Dim strMsg As String
             Set objSQL = CreateObject("Sqlole.SQLServer")
             ' Connect to SQL server...
             objSQL.Connect strServer, strUser, strPwd
             Debug.Print "------------------------------------------------"
             Debug.Print "SQL Server: " & strServer
             Debug.Print "------------------------------------------------"
             For Each objSQLdb In objSQL.Databases
                 ' Print database(s) name.
                 Debug.Print "...Database: " & objSQLdb.Name
                 Debug.Print "......Tables: "
                 For Each objSQLtbl In objSQLdb.Tables
                     ' Print table name(s).
                     Debug.Print ".........." & objSQLtbl.Name
                     For Each objSQLfld In objSQLtbl.Columns
                         ' Print field name(s).
                         Debug.Print "............." & objSQLfld.Name
                     Next
                 Next
             Next
             ' Dis-connect from SQL server.
             objSQL.DisConnect
             EnumSQLColumns = True
          EnumSQLColumns_End:
             Exit Function
          EnumSQLColumns_Err:
             ' Basic error handling.
             MsgBox Err.Description, vbInformation, "SQL OLE Automation"
             Resume EnumSQLColumns_End
          End Function 


  2. Type the following line in the Debug window, and then press ENTER:
    ?EnumSQLColumns()
    Note that you receive the list of columns in the specified Microsoft SQL Server Table, for example:
    
           ------------------------------------------------
           SQL Server: MyTestSQLServer
           ------------------------------------------------
           ...Database: master
           ...Database: model
           ...Database: msdb
           ...Database: MyTestDB
           ...Database: pubs
           True 
    Note that your output will be similar (depending on the installed databases in your SQL Server).



REFERENCES

For more information about OLE functionality and automation, search the Help Index for Automation, or ask the Microsoft Access 97 Office Assistant.

For further information about the SQL Server Distributed Management Object Model (SQL-DMO), refer to the "Programming SQL Distributed Management Objects" manual included with your SQL Server 6.0 documentation.

Additional query words:

Keywords : kbinterop IntpOlea
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: October 13, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.