Access Queries in Sub Database

Question

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"

Zaider · Accepted Answer · 2015-06-25 13:02:42Z

3

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

edited Jun 25, 2015 at 13:02

answered Jun 25, 2015 at 12:55

Zaider

2,0231 gold badge24 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HansUp · Accepted Answer · 2015-06-25 13:05:06Z

3

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];"

answered Jun 25, 2015 at 13:05

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Collectives™ on Stack Overflow

Access Queries in Sub Database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related