2

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:

  1. Drop a table in Microsoft Access and get it to drop the foreign keys at the same time?

  2. 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.

Remy Lebeau
  • 555,201
  • 31
  • 458
  • 770
Mark Elder
  • 3,987
  • 1
  • 31
  • 47
  • Consider [this](https://stackoverflow.com/a/202534/4299358) and other answers as per DB version - if it works for tables then look at the types that aren't 1, 4 or 6. – AmigoJack Jul 09 '21 at 15:56
  • What do you mean by 'drop the foreign keys' - need to just delete field data, not entire record? Why delete a table? What are you going to replace foreign keys with and how? – June7 Jul 09 '21 at 17:28
  • Are you trying to drop FKs that refer to the table being dropped? Or are you trying to drop FKs in the table being stored that refer to other tables? – Remy Lebeau Jul 09 '21 at 19:22
  • @June7 - I don't need to change any data, just the constraints. The database has some design issues and some key values are duplicated in multiple tables, the current keys are changed together instead of all pointed to one primary source table. – Mark Elder Jul 12 '21 at 15:47
  • @RemyLebeau - I'm trying to drop FKs that refer to the table being dropped. – Mark Elder Jul 12 '21 at 15:49
  • @AmigoJack - MSysObjects referenced in that answer has the same permissions issues as MSysRelationships – Mark Elder Jul 12 '21 at 15:56
  • As I recall, somethings are not supported via FireDAC/ODBC, so you'll probably have to use Ado and use AdoDb.OpenSchema(adSchemaForeignKeys, VarArrayOf([Unassigned, Unassigned, EachTableName]), EmptyParam); – Greg Dawson Jul 13 '21 at 21:26

0 Answers0