Microsoft Access (mdb) : List connected Oracle tables

Question

We are developing in Oracle ERP environment. There a quite a few view legacy MDB databases (dozens), that are connceted to Oracle views (hundreds of dependencies). In Oracle there are a custom 800+ views, that are subject to be reworked, set to deprecated and possibly will be deleted in the future.

I can see the connected Oracle DB in MDB-Design view, but I need to write those dependencies into a list. With such a list I could do software maintenance job sketched above.

I have a ADOX-based Metadata-Reader, but this does not list the oracle tables:

Public Sub ADOX_Oracle_Metadata()
'To reference ADO from Microsoft Access
'In Microsoft Access, select or create a module from the Modules tab in the Database window.
'On the Tools menu, select References....
'Verify that at least the following libraries are selected:
'
'Microsoft ActiveX Data Objects x.x Library
'ADO Ext. 2.7 for DDL and Security (ADOX)
'


  Dim cn As ADODB.Connection
  Dim ct As ADOX.Catalog
  Dim tb As ADOX.Table

  Dim strDB As String
  Dim ws As Worksheet

  Set cn = New ADODB.Connection
  Set ct = New ADOX.Catalog

  strDB = "L:\Applikationen\Access\DepreciationOutputMail.mdb"
  cn.ConnectionString = _
     "Provider=Microsoft.ACE.OLEDB.12.0;" & _
     "Data Source=" & strDB & ";"
  cn.Open
  Set ct.ActiveConnection = cn
  For Each tb In ct.Tables
    ' Tables can be of type TABLE, ACCESS TABLE, SYSTEM TABLE or VIEW
      Debug.Print tb.Type & "    " & tb.Name
  Next tb
  cn.Close
  Set ct = Nothing
  Set cn = Nothing
End Sub

Anyhow this does not list the connected oracle tables. Maybe I have just to change the connection string? How do I know the correct connection string? Can I read it somewhere in the computer that runs the MDB? Can you provide a solution?

This is a screenshot of a sample situation:

The tables I need to list are marked in green.

regards, LPNO

Addon information on request of Erik, here an extract of relevant columns of MSYSOBJECTS table, created with

SELECT MSysObjects.Connect, MSysObjects.ForeignName, MSysObjects.Name, MSysObjects.Type INTO Extract_MSYSOBJECTS
FROM MSysObjects
WHERE (((MSysObjects.Connect) Is Not Null));

Actually column NAME already lists the information I looked for. Anyhow a VBA-coding approach would still be appreciated, as there are numerous mdb databases to be checked about this.

Answer 1

searching around I found out that this MDB-Query does exactly what I asekd for:

SELECT MSysObjects.Name, MSysObjects.Type, MSysObjects.Flags
FROM MSysObjects
WHERE (((MSysObjects.Type)=6) AND ((MSysObjects.Flags)=2097152)) OR (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Flags;

Answer 2

Don't use ADOX for this, but use DAO instead.

DAO is more native to Access, and can work with linked tables more easily.

Dim db As DAO.Database
Dim td As DAO.TableDef
Set db = DBEngine.OpenDatabase("L:\Applikationen\Access\DepreciationOutputMail.mdb")
For Each td In db.TableDefs
    Debug.Print td.Name; td.SourceTableName, td.Connect
Next

Do note that Access can also connect to tables/views via queries or directly from code, these wouldn't be listed. You can iterate querydefs to find the queries, but for code it would be substantially more complex.

Alternate approach using the MSysObjects table:

Dim db As DAO.Database
Set db = DBEngine.OpenDatabase("L:\Applikationen\Access\DepreciationOutputMail.mdb")
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT * FROM MSysObjects WHERE Type = 4")
Do While Not rs.EOF
    Debug.Print rs!Name; rs!ForeignName
    rs.MoveNext
Loop