I need to (programmatically) drop a table from a Microsoft Access database (.mdb file in 2002-2003 format). When I drop the table, I need to drop all the foreign keys. The foreign keys were created with different names, so I need a way to iterate over them to drop them.
FireDAC has a TFDMetaInfoQuery component with a MetaInfoKind of mkForeignKeys. I can get that to execute, but it never returns any rows, either for the entire database or if I specify an .ObjectName. As a test, I switched to mkTables and I do get data returned.
I can query the relationships in Access by using the MSysRelationships table, but when I try to query that directly from FireDAC, I get an error message:
Could not read definitions; no read definitions permission for table or query 'MSYSRELATIONSHIPS'
Is there a way to:
Drop a table in Microsoft Access and get it to drop the foreign keys at the same time?
Iterate over the foreign keys that are pointed to a given table so that I can drop them myself?
Or, is there any other way of dropping this table that I am not considering? If need be, I can get a TADOConnection, but I didn't see any way that would help, either.