LISTING 2: Return a String of Database Table Names by Using a SQLObjectList Object

Sub CallListOfTables()
Dim str1 As String

'Compile list of table names in database
'with name in str1.
str1 = "Chapter6NorthwindCS"
Debug.Print ListOfTables(str1)
End Sub

Function ListOfTables(DbsName As String) As String
Dim srv1 As SQLDMO.SQLServer
Dim obl1 As SQLDMO.SQLObjectList

'Instantiate server object and
'connect it.
Set srv1 = New SQLDMO.SQLServer
srv1.Connect "Cabxli", "sa", ""

'Save reference to ObjectList object.
Set obl1 = _
    srv1.Databases(DbsName). _
    ListObjects(SQLDMOObj_UserTable)

'Compile list of tables for combo box row source.
For Each obj1 In obl1
    ListOfTables = ListOfTables & obj1.Name & "; "
Next obj1
ListOfTables = Left(ListOfTables, Len(ListOfTables) - 2)

'Clean up objects.
Set obl1 = Nothing
srv1.DisConnect
Set srv1 = Nothing
End Function