none
Delete a table if it exists RRS feed

  • Question

  • I have an Access Database that I connect to with an OleDb connection.  I was wanting to create a query that will determine if a table exists and if it does, deletes that table.

    Something like:

    IF EXISTS (SELECT COUNT(*) FROM MSYSObjects WHERE Type = 1 AND [Name]='MY_TABLE')

    DROP TABLE MY_TABLE;

    Any ideas on how to make this work?

    Thanks!

    Friday, February 25, 2011 7:05 PM

Answers

  • Access does not really support programming in its SQL statements.  You can detect the presence or absence of the table in a SQL statement but you cannot execute a ddl command inside it.

    The only way I can think of to use a SQL statement to delete the table would be to have a public function in the Access database that deletes the table and returns and empty string and to execute the following SQL:

    SELECT IIF(0<Dcount("*", "MSysObjects","(([Name]='TableToDelete') AND ([Type]=1))"),KillTable("TableToDelete") ,"") as DUMMY

    You can only invoke a public function from a SQL statement and there aren't any built-in ones that will delete a table.

    The other choice is to use:
    SELECT Dcount("*", "MSysObjects","(([Name]='TableToDelete') AND ([Type]=1))") as [Switch];
    Check the returned value to see if it is a 0 or a 1 and if it is a 1 then issue the Drop Table command.

    • Proposed as answer by Bruce Song Friday, March 4, 2011 8:23 AM
    • Marked as answer by cwang733t Monday, March 7, 2011 1:35 PM
    Tuesday, March 1, 2011 4:47 AM

All replies

  • Here is a C# code sample for doing this, but there is one caveat:

    using System.Data.OleDb;

    OleDbConnection conn;
    OleDbDataAdapter dA;

    conn = new OleDbConnection("provider=microsoft.jet.oledb.4.0; Data Source = C:\\Code\\testAcc\\db2test.mdb;Jet OLEDB:database Locking Mode=0;Mode=Share Deny None");

    dA = new OleDbDataAdapter();
    dA.SelectCommand = new OleDbCommand();
    dA.SelectCommand.Connection = conn;

    dA.SelectCommand.CommandText = "Select Count(*) From MSysObjects Where Name = 'Table3'";
    conn.Open();
    int i = (int)dA.SelectCommand.ExecuteScalar();
    if (i > 0)
    {
       dA.SelectCommand.CommandText = "Drop Table Table3";
       dA.SelectCommand.ExecuteNonQuery();
    }
    conn.Close();
    Console.WriteLine("Done!");

    --the caveat is that you need to set the Read permissions on MSysObjects (Tools/Security/User And Group Permissions), select MSysObjects and Click on the Read Option.   I don't have code to perform this operation programmatically (at this time), but if you don't do this -- you won't be able to read MSysObjects (or any other system table).  Note:  the VB.Net code for this is almost exactly the same except for the ; semicolons (VB.Net doesn't use em, \\ for escaping single slashes - don't need to in VB.Net, and declaring vars -- use Dim dA As OleDBDataAdapter, ...)

     

    Friday, February 25, 2011 8:44 PM
  • I was hoping I could write a single statement (or script) then having to return a value.
    Friday, February 25, 2011 10:49 PM
  • I think this is a more reliable way to do it than to instantiate an Access application object so you can loop through the DAO collection and then delete the table.  The Access Application route is more late-binding-ish (and more spaghetti code).  But -- you don't have to deal with MSysObjects table with the Access Application (forgot bout that).  The downside of doing it the way I originally posted is that you need read permissions on MSysObjects, which right now I only know how to set manually from within the Access mdb.   You probably could set that property programatically except that it would involve the Access Application object which - if you are already there - then you might as well loop through the DAO collection of tables/queries.

    Saturday, February 26, 2011 12:02 AM
  • Just issue the DROP TABLE MY_TABLE inside an ignore error wrapper.  If the table exists you will delete it.  If the table doesn't exist you will ignore the error.
    • Proposed as answer by Bruce Song Friday, March 4, 2011 8:23 AM
    Saturday, February 26, 2011 4:54 AM
  • Just issue the DROP TABLE MY_TABLE inside an ignore error wrapper.  If the table exists you will delete it.  If the table doesn't exist you will ignore the error.

    I will check out the Ignore wrapper!  I have never used it before, so I will have to check it out.  Thanks a million!  If this works for me, I'll mark it!
    Monday, February 28, 2011 2:57 PM
  • Here is a slight revision using try/catch (same in VB.Net except for the curly braces {}).   This way you don't have to look at MSysObjects or use the Access.Application object.

    using System.Data.OleDb;

    OleDbConnection conn;
    OleDbDataAdapter dA;

    conn = new OleDbConnection("provider=microsoft.jet.oledb.4.0; Data Source = C:\\Code\\testAcc\\db2test.mdb;Jet OLEDB:database Locking Mode=0;Mode=Share Deny None");

    dA = new OleDbDataAdapter();
    dA.SelectCommand = new OleDbCommand();
    dA.SelectCommand.Connection = conn;

    conn.Open();
    try
    {
       dA.SelectCommand.CommandText = "Drop Table Table3";
       dA.SelectCommand.ExecuteNonQuery();
       Console.WriteLine("Table Deleted!");
    }
    catch (Exception ex){}

    conn.Close();

     

    Monday, February 28, 2011 4:08 PM
  • I seem to be having some issues finding what is valid sql I can execute against an Access DB.

    IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

     

    Monday, February 28, 2011 4:48 PM
  • Here is a slight revision using try/catch (same in VB.Net except for the curly braces {}).   This way you don't have to look at MSysObjects or use the Access.Application object.

    using System.Data.OleDb;

    OleDbConnection conn;
    OleDbDataAdapter dA;

    conn = new OleDbConnection("provider=microsoft.jet.oledb.4.0; Data Source = C:\\Code\\testAcc\\db2test.mdb;Jet OLEDB:database Locking Mode=0;Mode=Share Deny None");

    dA = new OleDbDataAdapter();
    dA.SelectCommand = new OleDbCommand();
    dA.SelectCommand.Connection = conn;

    conn.Open();
    try
    {
       dA.SelectCommand.CommandText = "Drop Table Table3";
       dA.SelectCommand.ExecuteNonQuery();
       Console.WriteLine("Table Deleted!");
    }
    catch (Exception ex){}

    conn.Close();

     


    I was just thinking and he may of mean try catch for the "ignore wrapper".  I hope not.  I seriouly am hoping to have optional statements in the script I am executing against my system.
    Monday, February 28, 2011 6:10 PM
  • Saberman is/was right for older versions, now, it generates a trappable error. Can't you use a try-catch construction (or whatever name is that constructon in VbNet) ?
    • Proposed as answer by monteloro Tuesday, June 17, 2014 9:38 AM
    Monday, February 28, 2011 6:17 PM
    Moderator
  • Access does not really support programming in its SQL statements.  You can detect the presence or absence of the table in a SQL statement but you cannot execute a ddl command inside it.

    The only way I can think of to use a SQL statement to delete the table would be to have a public function in the Access database that deletes the table and returns and empty string and to execute the following SQL:

    SELECT IIF(0<Dcount("*", "MSysObjects","(([Name]='TableToDelete') AND ([Type]=1))"),KillTable("TableToDelete") ,"") as DUMMY

    You can only invoke a public function from a SQL statement and there aren't any built-in ones that will delete a table.

    The other choice is to use:
    SELECT Dcount("*", "MSysObjects","(([Name]='TableToDelete') AND ([Type]=1))") as [Switch];
    Check the returned value to see if it is a 0 or a 1 and if it is a 1 then issue the Drop Table command.

    • Proposed as answer by Bruce Song Friday, March 4, 2011 8:23 AM
    • Marked as answer by cwang733t Monday, March 7, 2011 1:35 PM
    Tuesday, March 1, 2011 4:47 AM
  • Saberman is/was right for older versions, now, it generates a trappable error. Can't you use a try-catch construction (or whatever name is that constructon in VbNet) ?


    I didn't want have to determine if something failed because of a type or because the query was meant to fail (if abort if the table doesn't exist).  There are a lot fo usages for that.

    I guess I just cant do what I was hoping to do.  Thanks anyways!

    Monday, March 7, 2011 1:37 PM