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