Happy Codings - Programming Code Examples
Html Css Web Design Sample Codes CPlusPlus Programming Sample Codes JavaScript Programming Sample Codes C Programming Sample Codes CSharp Programming Sample Codes Java Programming Sample Codes Php Programming Sample Codes Visual Basic Programming Sample Codes


Visual Basic Programming Code Examples

Visual Basic > Database SQL Stuff Code Examples

Return table names from Access-SQL Server

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60
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