SHIPP said:
I am using the following code to delete tables and queries in an
external database.
Set dbTmp = wrkDefault.OpenDatabase(strFull)
' Delete all tables
For intLoop = (dbTmp.TableDefs.Count - 1) To 1 Step -1
Set tdf = dbTmp.TableDefs(intLoop)
If (tdf.Attributes And dbSystemObject) = 0 Then
dbTmp.TableDefs.Delete tdf.Name
End If
Next intLoop
' Delete all queries
For intLoop = (dbTmp.QueryDefs.Count - 1) To 1 Step -1
Set qdf = dbTmp.QueryDefs(intLoop)
dbTmp.QueryDefs.Delete qdf.Name
Next intLoop
Does anybody know how to delete all forms, macros, reports and
modules using similiar code? I'm using Access 97 and dao.
I could be wrong, but I don't think you can delete them without opening
a separate instance of Access and automating that instance to open the
database and use that instance's DoCmd.DeleteObject method. You'll also
need to use the Forms, Reports, Scripts, and Modules containers of that
database. Here's a quickie procedure for deleting whatever list of
object types you give it:
'----- start of code -----
Sub DeleteInExternalDB(DBPath As String, ParamArray pObjectType() As
Variant)
On Error GoTo Err_Handler
Dim appAccess As Object
Dim db As DAO.Database
Dim cnt As DAO.Container
Dim strContainer As String
Dim iObjectType As Integer
Dim I As Integer
Dim intDocX As Integer
For I = LBound(pObjectType) To UBound(pObjectType)
Select Case pObjectType(I)
Case "Forms", "Form"
strContainer = "Forms"
iObjectType = acForm
Case "Reports", "Report"
strContainer = "Reports"
iObjectType = acReport
Case "Macros", "Macro", "Scripts", "Script"
strContainer = "Scripts"
iObjectType = acMacro
Case "Modules", "Module"
strContainer = "Modules"
iObjectType = acModule
Case Else
MsgBox "Invalid object type", vbExclamation, "Not
Deleted"
Exit Sub
End Select
If appAccess Is Nothing Then
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase DBPath, True
End If
Set db = appAccess.CurrentDb
Set cnt = db.Containers(strContainer)
With cnt
For intDocX = .Documents.Count - 1 To 0 Step -1
appAccess.DoCmd.DeleteObject iObjectType,
..Documents(intDocX).Name
Next intDocX
End With
Set cnt = Nothing
Next I
Exit_Point:
On Error Resume Next
Set cnt = Nothing
Set db = Nothing
appAccess.CloseCurrentDatabase
appAccess.Quit
Set appAccess = Nothing
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End Sub
'----- end of code -----
The above routine doesn't delete tables or queries, because they can be
deleted using pur DAO, without opening another instance of Access. To
delete forms and reports in the database "C:\Temp\OtherDB.mdb", you
would call the routine like this:
DeleteInExternalDB "C:\Temp\OtherDB.mdb", "Forms", "Reports"
To delete all objects except tables and queries, you'd call it like
this:
DeleteInExternalDB "C:\Temp\OtherDB.mdb", _
"Forms", "Reports", "Macros", "Modules"
Of course, you could add code to the procedure to delete tables and
queries as well, but you'd use the DAO TableDefs and QueryDefs
collections, rather than the document containers.
