Code to delete objects

P

Paul Johnson

I am wondering how to delete unwanted forms and reports in an external
database using VBA. The doCmd.DeleteObject command works in CurrentDb, but
I don't know how to make it work in another db. There must be another
method. Any help is appreciated.

TIA,

Paul Johnson
 
A

Alex Dybenko

you can open other database creating a new access instance, opening there
database and then use doCmd.DeleteObject to delete:

dim add as access.application
set app=createobject("access.application"

app.opencurrentdatabase (...)
app.doCmd.DeleteObject ...

HTH
 
P

Paul Johnson

Thanks. That looks like the needle I would have never found in the
haystack.

Paul
 
P

Paul Johnson

I tried your method, but I get a run-time error 2501, telling me that the
delete action was canceled.

My code:
Sub test()
Dim app As Application
Set app = CreateObject("access.application")
app.OpenCurrentDatabase ("MyDatabasePathAndName.mdb")
app.DoCmd.DeleteObject acForm, "MyFormName"
app.CloseCurrentDatabase
End Sub

Am I missing something else?

TIA
Paul Johnson
 
A

Alex Dybenko

Hi Paul,
well, code looks ok. maybe this form already opened, or opened in other
access instance?
 
P

Paul Johnson

Yes, it was open in instances hidden to my view as I worked with the
application objects in the VBA window. I had to close the open MSAccess
applications in the Task Manager "Processes" window to unlock the target DB.
I also needed to use the Db.Properties.Refresh method to get this sub to
work, maybe because the MSysObjects table was changing with each deletion?

Thanks to your help, here is what I came up with (generic object names
inserted), which is working fine:

Sub CleanDatabase(FileName As String)
Dim app As Application
Set app = CreateObject("access.application")
app.OpenCurrentDatabase (FileName)
With app.DBEngine.OpenDatabase(FileName).OpenRecordset( _
"SELECT Name,IIf(Type = 1, 0, IIf(Type = 5, 1, IIf(Type = -32768, 2,
3))) " & _
"AS ObjectType " & _
"FROM MSysObjects " & _
"WHERE (Type = -32764 And Not Name In
('rptSample1','rptSample2',*etc*)) " & _
"Or (Type = -32768 And Not Name In ('frmSample1','frmSample2',*etc*))
" & _
"Or (Type = 1 And Not (Name Like '*MSys*' Or Name In
('tblSample1',*etc*))) " & _
"Or (Type = 5 And Not (Name Like '~sq*' Or Name In
('qrySample1',*etc*)))")
While Not .EOF
app.DoCmd.DeleteObject !ObjectType, !Name
app.CurrentDb.Properties.Refresh
.MoveNext
Wend
.Close
End With
app.CloseCurrentDatabase
Set app = Nothing
End Sub
 
D

Dale Fye

Paul,

I could not get your code to work, so thought about it a little more and
came up with

Private Sub DeleteTable(TableName as string, _
Optional DatabaseName as string = "")

dim db as dao.database
if len(DatabaseName) = 0 then
set db = Currentdb
else
set db = dbEngine.opendatabase(DatabaseName)
endif

db.execute "DROP TABLE " & TableName

db.close
set db = nothing

exit sub

db.execute
 
Top