Code to delete objects

  • Thread starter Thread starter Paul Johnson
  • Start date Start date
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
 
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
 
Thanks. That looks like the needle I would have never found in the
haystack.

Paul
 
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
 
Hi Paul,
well, code looks ok. maybe this form already opened, or opened in other
access instance?
 
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
 
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
 
Back
Top