When reading an MS Access db with several tables with relationships between them, can pypyodbc read the relationship between the tables?
Asked
Active
Viewed 224 times
0
-
It depends... Were the relationships documented within Access using the Relationships tool? Then your ODBC driver will let you query Access' msysRelationships hidden table to retrieve what was documented inside that tool. – Chris Maurer Nov 23 '21 at 06:12
-
@ChrisMaurer Yes, the relationships are documented in Access. How do I query them with pypyodbc? Do you have a minimal example? – Pete Nov 25 '21 at 00:39
-
@ChrisMaurer To be clear: If I run `cursor.execut("SELECT * FROM MSysRelationships")` I get: `[Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'MSysRelationships'.")` The same query works in Access – Pete Nov 25 '21 at 16:49
-
related: https://stackoverflow.com/q/15504588/2144390 – Gord Thompson Nov 25 '21 at 20:47
-
@GordThompson I've seen that. When I try to execute `cursor.execute("GRANT SELECT ON MSysRelationships TO Admin")` I get `[ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.")` – Pete Nov 26 '21 at 02:26
-
https://stackoverflow.com/a/18124087/2144390 – Gord Thompson Nov 26 '21 at 12:23
-
@GordThompson Can I do this in python from pyodbc? Or would I have to use another interface? – Pete Nov 26 '21 at 20:36
-
You can't do it with ODBC, but you can use Python to retrieve the information by inspecting the ACE.DAO `Relations` collection as illustrated in [this answer](https://stackoverflow.com/a/68591019/2144390). – Gord Thompson Nov 28 '21 at 19:04