Visual Basic > Database SQL Stuff sample source codes

Visual Basic Code > Return table names from Access-SQL Server

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


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