Alter a "found" ADO Statement get table names 2

[timhans]

Hello,I "found" this from googling, it prints to the imediate window all the table names and there field names.
I have a need for this but for all the table names to fill a combobox and the field names per table to fill there own combobox. This past my skill level. 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

 

[kjv1611]

Well, there may be a better way, such as creating a query to keep you updated on a regular basis without having to loop through them all like that (but I don't know that's the case - it's just a supposition at this point).

However, I can tell you a way to do it via the looping method each time, if necessary:

1. Build a local table to store the values (assuming you want to keep this data a little more permanently).
2. Use recordset object to add the records to the table based on the SQL results.

So, let's assume your new table is tblSqlTables, and the column titles are TableName and Column.

The code with the recorset usage:

Sub ListTableandColumNamesADOX()
  Dim Conn As New ADODB.Connection
  Dim Table As ADOX.Table, Column As ADOX.Column
  Dim Catalog As New ADOX.Catalog
[blue][b]  Dim db as DAO.Database
  Dim rs as DAO.Recordset
  Dim strSQL as String[/b][/blue]

  Set db = CurrentDb
  Set rs = db.OpenRecordset("tblSqlTables")

[green]  'Open connection you want To get database objects[/green]
  Conn.Provider = "MSDASQL"

  Conn.Open "DSN=...;Database=...;", "UID", "PWD"
  
[green]    'Create catalog object[/green]

    Set Catalog.ActiveConnection = Conn
  
  [green]'First, clear out previous values, as to avoid duplicate data.[/green]
[b][blue]   strSQL = "DELETE * FROM tblSqlTables"
   DoCmd.SetWarnings False
   DoCmd.RunSQL strSQL
   DoCmd.SetWarnings True[/blue][/b]

 [green] 'List tables And columns[/green]

  For Each Table In Catalog.Tables
    For Each Column In Table.Columns
[blue][b]      rs.AddNew
      rs.Field("TableName") = Table.Name
      rs.Field("Column") = Column.Name
      rs.Update[/b][/blue]
    Next
  Next
End Sub

See if that works for you. I didn't debug the code, just typed here, so let us know if any errors.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dhookom]

You should only need one line of code to do this.
Create your tables combo box:
[tt][blue]
Name: cboTable
Row Source: SELECT Name FROM Msysobjects where Type=1 AND Name not like "msys*" ORDER BY Name;
After Update: [Event Procedure]
[/blue][/tt]

Private Sub cboTable_AfterUpdate()
    Me.cboField.RowSource = Me.cboTable
End Sub

[tt][blue]
Name: cboField
Row Source Type: Field List
Row Source:
[/blue][/tt]


Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

timhans,

See, I told you there should be an easier way than what I put.

Give Duane's method a try. Post back with any questions/errors.

--

"If to err is human, then I must be some kind of human!" -Me

 

[timhans]

Kjv1611 and dhookom, thank you for your reply's, I tryed dhookom's code and know have combobox filled with table names. When I select a table thought I get method or data member not found. Is this possibly a library reference I need to install ?. In gerneral how do you know what libraries need to be installed when using ADO.

 

[timhans]

O.K never mind my last post I got it to work, its very nice.
Thank you both