How to delete a table in another database

[Stephanpls]

I want to delete a table X in a database Y while the current database is another one. What I do is:

**
Dim Dbase as Database
Set DBase = OpenDatabase("Y.mdb")
DoCmd.DeleteObject acTable, "X"
**

There is a message then that table X is not found (though it exists!). I think Docmd only works in the current database. How to delete a table in another database?

Thanks!

 

[DJkarl]

I want to delete a table X in a database Y while the current database is another one. What I do is:

**
Dim Dbase as Database
Set DBase = OpenDatabase("Y.mdb")
Dbase.TableDefs.Delete("X")
**

There is a message then that table X is not found (though it exists!). I think Docmd only works in the current database. How to delete a table in another database?

Thanks!

Give that a try.

 

[Stephanpls]

It worked! I keep learning. Thanks!

 

[Stephanpls]

Other question. Is it possible to compact a database in VBA?

 

[rapsr59]

Hi,

This is in reference to your "How Do You Compact A database Using VBA

Use

        DoCmd.RunCommand acCmdCompactDatabase

Richard

 

[rapsr59]

This is another method to delete a table X in database Y when running database Z and Z is used to delete the table in Y

First Open database Y (The one that contains the table you want to delete)

Create a module called WhateverYouLike. Not really, name it whatever you like. I named mine KillTable

Create a function called – fncDeleteTable – make sure it is a Public Function

Edit this function to read…

[FONT=Times New Roman][SIZE=3]Public Function fncDeleteTable(strTableName)[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman] DoCmd.DeleteObject acTable, strTableName[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End Function[/SIZE][/FONT]

Save your module and close database Y


Now open database Z
Enter following code in a module, perhaps a click event or wherever you want to place the below code.

[SIZE=3][FONT=Times New Roman]         Call fncDeleteTable (“X”)[/FONT][/SIZE]

Now the fun part. While in the VBA editor…

• Click Tools
• Select References
• Select Browse…
• In the Add Reference window
• Set the Files Of Type to: Microsoft Access Databases (*.mdb)
• Go to the Look in control and look for Database X
• When you find Database X click on Open

You will then be returned to the Reference for database Y and you will note that dbx has been added to your references. Click OK. Now the module KillTable and the fncDeleteTable function in dbx is available to dby.

When you run the code Call funcDeleteTable(“X”) the table will be deleted in dbx

Hey I know this is a little unorthodox but it works and no error messages.

Have fun,


Richard

PS: The only drawback is that you will lock dbx and you will have to close dby in order to work on dby.

 

[boblarson]

And an even simpler way:

Dim db As DAO.Database
Dim strSQL As String
Dim strTableName As String

strTableName = "Whatever"

Set db = OpenDatabase("C:\FolderName\YourDatabaseLocationHere")

strSQL = "DROP TABLE " & strTableName

db.Execute strSQL, dbFailOnError

 

[Stephanpls]

Thanks for all very useful answers!

 

[kathywebster]

an even SIMPLER way:

CurrentDb.Execute "DELETE FROM SomeTable IN 'C:\SomePath\SomeDatabase.mdb' "