Get Table and field names

[abenitez77]

My code is not returning the table and field names as I expected. I want to get the sql linked tables and get the fields and the properties and append the info to a access local table. I will also want to display the table and field names in a listbox.

code:

'Sub ListTablesADOX()
Dim db As Database, td As TableDef
Dim rs As Recordset, rs2 As Recordset
Dim Catalog As New ADOX.Catalog
Dim Table As ADOX.Table, Column As ADOX.Column
Dim Conn As New ADODB.Connection

Set db = CurrentDb
' Trap for any errors.
On Error Resume Next
Set rs2 = db.OpenRecordset("tblFields")

'Open connection you want To get database objects
Conn.Provider = "MSDASQL"
Conn.Open "ODBC;DRIVER=SQL Server;SERVER=USATL02PRSQ70;DATABASE=KROGER_2010_AP;Trusted_Connection=Yes"

'Create catalog object
Set Catalog.ActiveConnection = Conn

'List tables And columns
For Each Table In Catalog.Tables
For Each Column In Table.Columns

rs2.AddNew
rs2!Object = Table.Name
rs2!FieldName = Column.Name
rs2!FieldType = Column.Type
rs2!FieldSize = Column.DefinedSize
rs2!FieldAttributes = Column.Attributes
'rs2!FldDescription = fld.Properties("description")
rs2.Update

' Debug.Print Table.Name & ", " & Column.Name
Next
Next
db.Close
rs2.Close

 

[abenitez77]

I found the error, I removed ODBC from the begining of the string.

Conn.Open "DRIVER=SQL Server;SERVER=USATL02PRSQ70;DATABASE=KROGER_2010_AP;Trusted_Connection=Yes