I have connected to a db and iterate through the metadata to get the table names, each time dropping the table. However I get the error message:
pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Microsoft Access Driver] Table 'MSysAccessStorage' does not exist. (-1305) (SQLExecDirectW)")
Which doesn't make sense as I am getting the table name from the database so it has to exist. My connection must be working to get the table names and the other parts of my code such as insert into work. Here is my code:
for row in cursor.tables():
if(str(row.table_name)!="pricesBackup" and str(row.table_name)!="recipesBackup"):
sqlLine="DROP TABLE costingDB1.accdb." row.table_name
print(sqlLine)
cursor.execute(sqlLine)
conn.commit()
This seems very odd to me and wondering how to fix this. Thankyou in advance
CodePudding user response:
Tables whose names start with "MSys" are internal system tables (row.table_type == "SYSTEM TABLE"
) that cannot be dropped. You'll need to restrict your deletions to tables where row.table_type == "TABLE"
.
CodePudding user response:
I have found the solution: I instead made a list from the table names removing the MSys tables and ~TMC tables, so just mine. I then used this list to drop tables. I believe the issue was that the 2 sql queries in the loop were clashing. It was trying to find the tables from the drop table command.