VB and VBA Users Source Code: Return table names from Access/SQL Server
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Return table names from Access/SQL Server
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Friday, July 07, 2000
Hits:
690
Category:
Database/SQL/ADO
Article:
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 'Author : Andrew Baker 'Date : 07/07/2000 20:40 'Notes : 'Revisions : 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
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
