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.

Erik A · Answer 1 · 2018-09-24T14:57:07.893

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

edited Sep 24 '18 at 14:57

answered Sep 24 '18 at 08:20

Erik A

31,639
12
42
67

Hi Erik, I tried this as a first approach. This does not work! It does NOT lists external tables, that are connected into microsoft access. It just list internal mdb-tables. – olippuner Sep 24 '18 at 08:27
@olippuner It certainly does. I've tested it. As said, you can connect to tables in different ways. – Erik A Sep 24 '18 at 08:30
I added a screenshot above. The tables marked in green are not listed with the approach suggested by you. Keep in mind, that these tables are just connected, they are not resident in MS-Access. – olippuner Sep 24 '18 at 08:33
I can tell very little by that screenshot. If you'd provide a dump of the MSysObjects table (or the relevant rows referring to those tables) I'd likely be able to tell more (reveal that table by going to navigation options and checking Show system objects) – Erik A Sep 24 '18 at 08:39
Hi Eric, I added addon information you requested above. – olippuner Sep 24 '18 at 14:06
Can you add the _Flags_ column? Odd, entries of type 4 should just be included in the tabledefs collection, thus my answer should work. As a workaround, you could try querying MSysObjects like you did in your example using VBA. I've added an approach based on that to my answer. – Erik A Sep 24 '18 at 14:57

score 0 · Accepted Answer · answered Nov 08 '18 at 08:29

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;

Microsoft Access (mdb) : List connected Oracle tables

2 Answers2