Microsoft KB Archive/154011

= ACC: How to Use Automation to List SQL Server Objects =

Article ID: 154011

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q154011



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.

 Install the Microsoft SQL Server 6.0 or 6.5 Workstation/Client Utilities. Open Microsoft Access 97 or 7.0 and create a new module. 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.  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:

  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   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: <pre class="fixed_text"> 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). </li></ol>

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

  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 </li>  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: <pre class="fixed_text"> 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). </li></ol>

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

  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 </li>  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: <pre class="fixed_text"> 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). </li></ol>

<div class="references_section">