create a report of the tables and columns in access?

[Guest]

I need to see all of the linked (ODBC from Oracle) columns and table names in
a report format from MS Access. How do I do this?

 

[Ron Hinds]

Create a table to hold the data, let's call it LinkedTables, with two
fields, TableName and ColumnName. Then use the following in code (you'll
need to add error trapping, etc.):

Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim rs As Recordset
Dim strSQL As String

Set db = DBEngine(0)(0)

strSQL = "DELETE FROM LinkedTables"
db.Execute strSQL

Set rs = db.OpenRecordset("LinkedTables")

For Each tdf In db.TableDefs
If InStr(1, tdf.Connect, "ODBC") > 0 Then
For Each fld In tdf.Fields
rs.AddNew
rs!TableName = tdf.Name
rs!ColumnName = fld.Name
rs.Update
Next
End If
Next

Base your report on this table. If you have other ODBC linked databases then
you'll have to modify the InStr function to look for a string that is
specific to the Oracle Connect string.