When developing an application based on a SQL Server database, you often encounter information that you need to know about the SQL Server database object. For example, which databases are there in SQL Server, which data tables are in these databases, which fields have each data table? What properties are these fields, etc. When I developed a database introduction program, I met these problems. After some exploration, I solved the above problems, and now I introduce the implementation method to everyone. The program interface is shown below. 1. Developing Environments The code listed herein is debugged in Windows NT 4.0 (SP6), Microsoft Visual Basic 6.0, and Microsoft SQL Server 2000 environments. 2. Realize ideas to use SQL-DMO (Distributed Management Objects) to access the SQL Server database. SQL-DMO is the 32-bit OLE object provided for Windows 9x and Windows NT, which provides an interface with the OLE compatible application, which can directly access the SQL Server data object through DMO. 3. Implement the code in Menu -> Enginee -> References, "Microsoft SqldMo Object Library."
Global variables defined in the following code: Public oSQLServer As SQLDMO.SQLServer 'defines a SQLDMO.SQLServer Object Public oCurrentDB As SQLDMO.Database' defines a SQLDMO.Database Object Public oCurrentTable As SQLDMO.Table 'defines a target Public SERVER_NAME As SQLDMO.Table String 'The SQL Server Server Name PUBLIC UserName AS String' for obtaining the user is used to obtain the username public password as string 'entered by the user to obtain the password entered by the user ... The following is some key program code. : Private Sub cmd_link_Click () 'click the "connect" button ...... Set oSQLServer = CreateObject ( "SQLDMO.SQLServer") oSQLServer.Connect SERVER_NAME, USERNAME, PASSWORD' connect to SQL server server, sERVER _NAME for the server name, USERNAME username, pASSWORD as password FillEmptyDatabaseList (True) If cob_sqldb.ListCount> 0 Then cob_sqldb.ListIndex = 0 Cob_sqldb_Click End If ...... End Sub Private Sub FillEmptyDatabaseList (bFill as Boolean) 'get on the SQL server server All database names If bFill = True Then Dim oDB As SQLDMO.Database Set oDB = New SQLDMO.Database For Each oDB In oSQLServer.Databases If oDB.SystemObject = False Then 'determines it is not the system database cob_sqldb.AddItem oDB.Name' the non- System Database Name Add to Database Information list box End if Next odb end if end su b Private Sub Cob_sqldb_Click () 'click' Database Information "list box ...... Dim oDB As SQLDMO.Database Set oDB = oSQLServer.Databases (cob_sqldb.List (cob_sqldb.ListIndex)) If oCurrentDB Is Nothing Then Set oCurrentDB = oDB Else If oCurrentDB.Name = oDB.Name Then Exit Sub End If Set oCurrentDB = Nothing Set oCurrentDB = oDB End If FillEmptyTableList (True) If cob_sqltable.ListCount> 0 Then cob_sqltable.ListIndex = 0 cob_sqltable_Click End If ...... End Sub Private Sub FilleMptyTableList (bfill as boolean) gets all non-system data table names in the current database IF bfill = true kil otable as sqldmo.table for each otable in occupialdb.tables if otable.systemObject =
