CODEWITHSUNDEEP
ms-accessvba
Big Pimpin
Big Pimpin

Reputation: 437

Access Queries in Sub Database

I know how to open a secondary database from a master database, but how can I switch "focus" to the secondary database and scan through the table names to see if a table exists in that database, if it does delete it?

This is how I would open the database but this seems to only check the database I am running the code in, not the secondary database that is opened.

Set qd = CurrentDb.QueryDefs("exec_Dead")
Set ws = DBEngine(0)
Set db = ws.OpenDatabase("C:\Test\Testing.mdb")
On Error Resumse Next
DoCmd.DeleteObject acTable = acDefault, "Late Payments For Today"

Upvotes: 0

Views: 86

Answers (2)

HansUp
HansUp

Reputation: 97131

DoCmd.DeleteObject is a method of Application, so you would need an Application instance for Testing.mdb in order to use DeleteObject.

You can do that, as @Zaider demonstrated. However you could just execute a DROP TABLE statement from db in your existing code:

Set ws = DBEngine(0)
Set db = ws.OpenDatabase("C:\Test\Testing.mdb")
On Error Resumse Next
'DoCmd.DeleteObject acTable = acDefault, "Late Payments For Today"
db.Execute "DROP TABLE [Late Payments For Today];"

Upvotes: 3

Zaider
Zaider

Reputation: 2013

You need to create an Access.Application variable so that you can interact with that instance of Access.

Dim db as DAO.Database
Dim tbl as DAO.TableDef
Dim accDB As Access.Application

Set accDB = New Access.Application
accDB.OpenCurrentDatabase "C:\Test\Testing.mdb", false

For each tbl in accDB.CurrentDb.TableDefs
    If tbl.Name = "The name of the table you want to delete" then
        accDB.DoCmd.DeleteObject acTable, tbl.Name
    End If
Next

accDB.CloseCurrentDatabase
set accDB = nothing

Upvotes: 3

Related Questions