Return table names from Access-SQL Server

The function below uses ADOX (Microsoft ADO Ext 2.5 for DLL and Security) to return all the table names for a given connection or connection string.

Purpose : Returns all the tables in an Access or SQL Server database
Inputs : A valid connection string or ADO Connection
Outputs : A collection of Table Names



CODE :

Public Function TableNames(Optional sConnectionString As String, Optional cCN As ADODB.Connection) As Collection
Dim oCatalog As New ADOX.Catalog, colTableNames As New Collection
Dim oTables As ADOX.Tables, oTable As ADOX.Table
Dim oConnection As New ADODB.Connection

On Error GoTo ExitSub
If Len(sConnectionString) Then
oConnection.ConnectionString = sConnectionString
oConnection.Open sConnectionString
Else
Set oConnection = cCN
End If
Set oCatalog.ActiveConnection = oConnection
Set oTables = oCatalog.Tables

For Each oTable In oTables
colTableNames.Add oTable.Name
Next
Set TableNames = colTableNames

ExitSub:
On Error Resume Next
If Len(sConnectionString) Then
'Close Temporary Connection
If oConnection.State <> 0 Then
oConnection.Close
End If
End If
Set oConnection = Nothing
Set oCatalog = Nothing
Set oTable = Nothing
Set oTables = Nothing
Exit Function
ErrFailed:
Debug.Print Err.Description
Debug.Assert False
Resume ExitSub
End Function


'Example
Private Sub Form_Load()
Dim colTableNames As Collection, vTable As Variant
Set colTableNames = TableNames("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\GFX-IT\database\support.mdb; Persist Security Info=False")
For Each vTable In colTableNames
Debug.Print "Table Name: " & vTable
Next
End Sub