Visual Basic Programming Code Examples
Visual Basic > Database SQL Stuff Code Examples
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
