none
Remove ALL tables in an Access database RRS feed

  • Question

  • I have an Access database I would like to remove all tables from.  I do not know the names of the Tables.

    How would one remove all Tables?

    Monday, April 14, 2014 10:12 PM

Answers

  • Hi,

    >>I do not know the names of the Tables.

    We could use MSysObjects to get all tables' name:

    SELECT Name FROM MSysObjects WHERE Type=1 AND Flags=0

    Before this, an important step is to change user permissions for Access MSysObjects, otherwise the "no permission" exception will occur.

    Refer to this thread: http://social.msdn.microsoft.com/Forums/vstudio/en-US/7522c873-eec8-4fbf-8e30-8d60cefc7428

    Dim ODCmm As New OleDb.OleDbCommand
    Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & _
                    Application.StartupPath & "\data.mdb;" & _
                "Jet OLEDB:System Database=" & Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) & "\Microsoft\Access\System.MDW"
    Dim conn As New OleDbConnection(ConStr)
    ODCmm.Connection = conn
    ODCmm.CommandType = CommandType.Text
    conn.Open()
    
    'Changing user permissions for Access MSysObjects
    ODCmm.CommandText = <sql>GRANT SELECT ON TABLE MSysObjects TO Public</sql>
    ODCmm.ExecuteNonQuery()
    
    ODCmm.CommandText = <sql>SELECT Name FROM MSysObjects WHERE Type=1 AND Flags=0</sql>
    Dim reader As OleDbDataReader = ODCmm.ExecuteReader()
    Dim array(1000) As String
    Dim idx As Integer = 0
    If reader.HasRows Then
           Do While reader.Read()
                 array(idx) = reader.GetString(0)
                 idx += 1
                 Console.WriteLine(reader.GetString(0))
           Loop
    Else
           Console.WriteLine("No Rows Found.")
           conn.Close()
           Exit Sub
    End If
    
    reader.Close()

    Now, we just need to drop tables one by one:

    'Drop tables
    For i As Integer = 0 To idx - 1
           ODCmm.CommandText = "DROP TABLE " & array(i)
           ODCmm.ExecuteNonQuery()
    Next
    
    conn.Close()
    MsgBox("Done!")

    Here is my complete code:

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Try
                Dim ODCmm As New OleDb.OleDbCommand
                Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & _
                    Application.StartupPath & "\data.mdb;" & _
                "Jet OLEDB:System Database=" & Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) & "\Microsoft\Access\System.MDW"
                Dim conn As New OleDbConnection(ConStr)
                ODCmm.Connection = conn
                ODCmm.CommandType = CommandType.Text
                conn.Open()
    
                'Changing user permissions for Access MSysObjects
                ODCmm.CommandText = <sql>GRANT SELECT ON TABLE MSysObjects TO Public</sql>
                ODCmm.ExecuteNonQuery()
    
    
                ODCmm.CommandText = <sql>SELECT Name FROM MSysObjects WHERE Type=1 AND Flags=0</sql>
                Dim reader As OleDbDataReader = ODCmm.ExecuteReader()
                Dim array(1000) As String
                Dim idx As Integer = 0
                If reader.HasRows Then
                    Do While reader.Read()
                        array(idx) = reader.GetString(0)
                        idx += 1
                        Console.WriteLine(reader.GetString(0))
                    Loop
                Else
                    Console.WriteLine("No Rows Found.")
                    conn.Close()
                    Exit Sub
                End If
    
                reader.Close()
    
                'Drop tables
                For i As Integer = 0 To idx - 1
                    ODCmm.CommandText = "DROP TABLE " & array(i)
                    ODCmm.ExecuteNonQuery()
                Next
    
                conn.Close()
    
                MsgBox("Done!")
    
            Catch ex As Exception
                MsgBox(ex.Message)
                Exit Sub
            End Try
    
        End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by Venkat786 Tuesday, April 15, 2014 10:32 AM
    • Marked as answer by Spektre Saturday, April 19, 2014 6:07 PM
    Tuesday, April 15, 2014 10:12 AM
    Moderator

All replies


  • You can generate statement like this: DROP TABLE t1, t2, t3, ... and then use prepared statements to execute it:

    SET FOREIGN_KEY_CHECKS = 0; 
    SET @tables = NULL;
    SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables
      FROM information_schema.tables 
      WHERE table_schema = 'database_name'; -- specify DB name here.
    
    SET @tables = CONCAT('DROP TABLE ', @tables);
    PREPARE stmt FROM @tables;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET FOREIGN_KEY_CHECKS = 1; 

    this will help u to first prepare a table list to drop and execute later with keys check for database integrity

    Monday, April 14, 2014 11:03 PM
  • Thanks for the quick reply but I don't think this is applicable to vb.net.

    Monday, April 14, 2014 11:06 PM
  • Hi,

    >>I do not know the names of the Tables.

    We could use MSysObjects to get all tables' name:

    SELECT Name FROM MSysObjects WHERE Type=1 AND Flags=0

    Before this, an important step is to change user permissions for Access MSysObjects, otherwise the "no permission" exception will occur.

    Refer to this thread: http://social.msdn.microsoft.com/Forums/vstudio/en-US/7522c873-eec8-4fbf-8e30-8d60cefc7428

    Dim ODCmm As New OleDb.OleDbCommand
    Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & _
                    Application.StartupPath & "\data.mdb;" & _
                "Jet OLEDB:System Database=" & Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) & "\Microsoft\Access\System.MDW"
    Dim conn As New OleDbConnection(ConStr)
    ODCmm.Connection = conn
    ODCmm.CommandType = CommandType.Text
    conn.Open()
    
    'Changing user permissions for Access MSysObjects
    ODCmm.CommandText = <sql>GRANT SELECT ON TABLE MSysObjects TO Public</sql>
    ODCmm.ExecuteNonQuery()
    
    ODCmm.CommandText = <sql>SELECT Name FROM MSysObjects WHERE Type=1 AND Flags=0</sql>
    Dim reader As OleDbDataReader = ODCmm.ExecuteReader()
    Dim array(1000) As String
    Dim idx As Integer = 0
    If reader.HasRows Then
           Do While reader.Read()
                 array(idx) = reader.GetString(0)
                 idx += 1
                 Console.WriteLine(reader.GetString(0))
           Loop
    Else
           Console.WriteLine("No Rows Found.")
           conn.Close()
           Exit Sub
    End If
    
    reader.Close()

    Now, we just need to drop tables one by one:

    'Drop tables
    For i As Integer = 0 To idx - 1
           ODCmm.CommandText = "DROP TABLE " & array(i)
           ODCmm.ExecuteNonQuery()
    Next
    
    conn.Close()
    MsgBox("Done!")

    Here is my complete code:

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Try
                Dim ODCmm As New OleDb.OleDbCommand
                Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & _
                    Application.StartupPath & "\data.mdb;" & _
                "Jet OLEDB:System Database=" & Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) & "\Microsoft\Access\System.MDW"
                Dim conn As New OleDbConnection(ConStr)
                ODCmm.Connection = conn
                ODCmm.CommandType = CommandType.Text
                conn.Open()
    
                'Changing user permissions for Access MSysObjects
                ODCmm.CommandText = <sql>GRANT SELECT ON TABLE MSysObjects TO Public</sql>
                ODCmm.ExecuteNonQuery()
    
    
                ODCmm.CommandText = <sql>SELECT Name FROM MSysObjects WHERE Type=1 AND Flags=0</sql>
                Dim reader As OleDbDataReader = ODCmm.ExecuteReader()
                Dim array(1000) As String
                Dim idx As Integer = 0
                If reader.HasRows Then
                    Do While reader.Read()
                        array(idx) = reader.GetString(0)
                        idx += 1
                        Console.WriteLine(reader.GetString(0))
                    Loop
                Else
                    Console.WriteLine("No Rows Found.")
                    conn.Close()
                    Exit Sub
                End If
    
                reader.Close()
    
                'Drop tables
                For i As Integer = 0 To idx - 1
                    ODCmm.CommandText = "DROP TABLE " & array(i)
                    ODCmm.ExecuteNonQuery()
                Next
    
                conn.Close()
    
                MsgBox("Done!")
    
            Catch ex As Exception
                MsgBox(ex.Message)
                Exit Sub
            End Try
    
        End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Proposed as answer by Venkat786 Tuesday, April 15, 2014 10:32 AM
    • Marked as answer by Spektre Saturday, April 19, 2014 6:07 PM
    Tuesday, April 15, 2014 10:12 AM
    Moderator
  • Sounds like it could be easily used maliciously, I'll pass on this one.

    Wednesday, April 16, 2014 4:21 AM
  • it doesn't delete tables that have relationships
    Saturday, June 15, 2019 1:18 PM