ACC: How to Use Automation to List SQL Server Objects

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).