ID: Q154011
The information in this article applies to:
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.
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.
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.
Option Explicit
Public Const strServer = "YourServer" ' Indicate your server.
Public Const strUser = "sa" ' Indicate your user name.
Public Const strPwd = "" ' Indicate your password.
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
?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).
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
?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).
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
?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).
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 : 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowtoLast Reviewed: November 21, 1998