rms-accessrodbc

how to sqlDrop an existing query (not table!) in R // delete existing query that is stored in an Access database

I often use RODBC to work with MS Access files in R. For removing existing tables sqlDrop works fine, e.g.:

db <- odbcConnectAccess(choose.files(caption="select database"))
sqlDrop(db, "existing_dummy_table")

What I need to do now is to delete an existing query that is stored in the Access database. sqlDrop does only seem to work with tables, not with querys.

sqlDrop(db, "existing_dummy_query")

brings up:

Error in odbcTableExists(channel, sqtable, abort = errors) : 
  ‘existing_dummy_query’: table not found on channel

Is there any solution how to delete/remove existing queries?

Thank you!

Solution

  • After a lot of testing I found a soultion myself:

    sqlQuery(db, "DROP TABLE existing_dummy_query";)

    Maybe its helpful for others. DROP VIEW did not work. I don't know why.