How Can I Delete a Table from a Jet Database?

ScriptingGuy1

Hey, Scripting Guy! Can I delete a table from a Jet database using a script?

— AK

Hey, AK. Of course you can delete a table from a Jet database using a script; after all, you can do anything using a script!

Well, OK, maybe not DHCP server stuff. And maybe you can’t change the display resolution of your computer. And maybe not – well, let’s just say that when it comes to databases you can do pretty much anything using a script. Including deleting a table.

Let’s take a look at a script that opens a hypothetical database named Inventory.mdb and then deletes a table named HardwareBackup:

Set objCatalog = CreateObject(“ADOX.Catalog”)
Set objConnection = CreateObject(“ADODB.Connection”)

objConnection.Open _
    “Provider= Microsoft.Jet.OLEDB.4.0; ” & _
        “Data Source=inventory.mdb” 
Set objCatalog.ActiveConnection = objConnection
objCatalog.Tables.Delete “HardwareBackup”
objConnection.Close

You’re right: there isn’t much to it. We begin by creating a pair of object references. The first – objCatalog – represents an instance of the ADOX Catalog object. (ADOX stands for ActiveX Data Objects Extensions for Data Definition Language and Security – trying saying that three times fast. Or try saying it once without taking a breath.)

ADOX, by the way, is an extension to ActiveX Data Objects (ADO) that enables you to do things like, well, delete tables.

The second object reference – objConnection – represents an instance of the ADO Connection object; we’ll use this object to open the database Inventory.mdb. That’s what we do here:

objConnection.Open _
    “Provider= Microsoft.Jet.OLEDB.4.0; ” & _
        “Data Source=inventory.mdb”

Note that because Inventory.mdb is located in the same folder as our script we didn’t have to specify the entire path.

After we make the connection we assign the objConnection reference to the ActiveConnection property of our Catalog object; this lets the Catalog object know which database to work with. We then use the Delete method of the Tables collection to delete the table named HardwareBackup:

objCatalog.Tables.Delete “HardwareBackup”

That’s all there is to it. We close the connection and go home, another job well done.

Disclaimer. Ok, so maybe your boss won’t let you go home after you run this script. But it never hurts to ask, right?

ScriptingGuy1

Follow