ACC: How to List SQL Server Objects Using Automation

• Microsoft Access versions 7.0, 97

SUMMARY

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

This article describes how to use OLE 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 demonstrate how to list the databases, tables, or columns in a Microsoft SQL Server 6.0 or 6.5 table using Visual Basic for Application's code in Microsoft Access 97 or Microsoft Access 7.0.

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 demonstrates 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 demonstrates 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 demonstrates how to obtain 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.

Keywords          : AutoGnrl kbinterop IntpOleA
Technology        : kbole
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto