ADO: Get table name / exclude name 1

[timhans]

Good Morning, The code below collects table names and column names and fills combo boxes with them(I did not write it, just using it), can the code be altered so that a table name can be excluded from from being captured. Thanks


Sub ListTableandColumNamesADOX()
Dim Conn As New ADODB.Connection

'Open connection you want To get database objects
Conn.Provider = "MSDASQL"
Conn.Open "DSN=...;Database=...;", "UID", "PWD"

'Create catalog object
Dim Catalog As New ADOX.Catalog
Set Catalog.ActiveConnection = Conn

'List tables And columns
Dim Table As ADOX.Table, Column As ADOX.Column
For Each Table In Catalog.Tables
For Each Column In Table.Columns
Debug.Print Table.Name & ", " & Column.Name
Next
Next
End Sub

Name: cboTable 'combobox for table names
Row Source: SELECT Name FROM Msysobjects where Type=1 AND Name not like "msys*" ORDER BY Name;

Name: cboField 'combobox for column names
Row Source Type: Field List

After Update: [Event Procedure]
CODE
Private Sub cboTable_AfterUpdate()
Me.cboField.RowSource = Me.cboTable

 

[formerTexan]

You can amend your query to exclude tables by name:

SELECT Msysobjects.Name
FROM Msysobjects
WHERE (((Msysobjects.Name) Not Like "msys*" And (Msysobjects.Name)<>"tblWHATEVER") AND ((Msysobjects.Type)=1))
ORDER BY Msysobjects.Name;

Or you can exclude certain tables when you loop through the ADOX collection.

If table.name <> "tblWHATEVER" then

End If

Cheers, Bill

 

[timhans]

Thank you formerTexan, much appriciated