Function to delete all objects except tables out of a Database

[Fiat77]

We have a Production database we wish to update with changes. The changes are in a separate development database. What i need to do is with a copy of the Production Database execute a function that will delete all objects except the tables out of this database so i do not have to delete them one object at a time. is there an easy way to do this?
Thanks

 

[traingamer]

You could import JUST the tables into a new database. File -> Import, choose database source, select all tables.

OR

You could go into your copy and select the first query and hold down the shift and delete buttons (which will delete without verification), then go to reports, etc. and repeat the process. Of course, be careful not to do this in the original.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Fiat77]

Problem is these are SQL Tables

 

[ProgramError]

hi fiat77

There are hidden tables in the access database which can be displayed by tools/options/view and ticking 'system objects'.
(be careful you dont accidentally alter any of these tables! May I suggest you make a copy of the database and do your tests on the copy!)
Open the 'MSsysobjects' table and scroll to the right.
there you will find a list of all the objects in the database and a field named 'type' which corresponds with the type of object which is in the database. make a note of the 'type' of objects and base a query on them.

Dont forget to work on a copy of the database when doing this.

Alternatively...

split the database to a frontend/backend. that way you will have the tables in one database and all the other objects in the front end. Then its just a matter of renaming the backend and deleting the front end.

Ian M



Program Error
Programmers do it one finger at a time!

 

[VBslammer]

You can call a function to do it as long as it isn't contained by one of the objects to be deleted. The easiest way would be to add a temporary menu command and set its OnAction property to [tt]"=DeleteAllButTheTables()"[/tt]

The code that is running can't be deleted, so the module where the function resides should be named "modCleaner" or similar and it will have to be removed manually afterwards.

[tt]Standard Module (Access 2000 or later): modCleaner[/tt]

Option Compare Database
Option Explicit

Function DeleteAllButTheTables()
On Error GoTo ErrHandler
  Dim obj As AccessObject
  Dim objs() As String
  Dim strOut As String
  Dim index As Long
  
  Const conThisModuleName = "modCleaner"
  
  If Forms.Count > 0 Then
    If MsgBox("Open forms can't be deleted, continue anyway?", _
            vbQuestion + vbOKCancel, "Warning:") = vbCancel Then
      Exit Function
    End If
  End If
  
  If MsgBox("Are you sure you want to delete all database objects (except tables)?", _
            vbQuestion + vbYesNo, "Caution:") = vbNo Then
    Exit Function
  Else
    Screen.MousePointer = 11
    SysCmd acSysCmdSetStatus, "Deleting objects, please wait..."
  End If
  
  If CurrentData.AllQueries.Count > 0 Then
    ReDim objs(CurrentData.AllQueries.Count - 1)
    index = 0
  
    For Each obj In CurrentData.AllQueries
      If obj.IsLoaded Then
        DoCmd.Close acQuery, obj.Name, acSaveNo
      End If
      objs(index) = obj.Name
      index = index + 1
    Next
    
    SysCmd acSysCmdSetStatus, "Deleting queries, please wait..."
    
    For index = 0 To UBound(objs)
      DoCmd.SelectObject acQuery, objs(index), True
      DoCmd.DeleteObject acQuery, objs(index)
      DoEvents
    Next
  End If
  
  If CurrentProject.AllMacros.Count > 0 Then
    ReDim objs(CurrentProject.AllMacros.Count - 1)
    index = 0
    
    For Each obj In CurrentProject.AllMacros
      If obj.IsLoaded Then
        DoCmd.Close acMacro, obj.Name, acSaveNo
      End If
      objs(index) = obj.Name
      index = index + 1
    Next
    
    SysCmd acSysCmdSetStatus, "Deleting macros, please wait..."
    
    For index = 0 To UBound(objs)
      DoCmd.SelectObject acMacro, objs(index), True
      DoCmd.DeleteObject acMacro, objs(index)
      DoEvents
    Next
  End If
  
  If CurrentProject.AllForms.Count > 0 Then
    ReDim objs(CurrentProject.AllForms.Count - 1)
    index = 0
    
    For Each obj In CurrentProject.AllForms
      If obj.IsLoaded Then
        DoCmd.Close acForm, obj.Name, acSaveNo
      End If
      objs(index) = obj.Name
      index = index + 1
    Next
    
    SysCmd acSysCmdSetStatus, "Deleting forms, please wait..."
    
    For index = 0 To UBound(objs)
      DoCmd.SelectObject acForm, objs(index), True
      DoCmd.DeleteObject acForm, objs(index)
      DoEvents
    Next
  End If
  
  If CurrentProject.AllReports.Count > 0 Then
    ReDim objs(CurrentProject.AllReports.Count - 1)
    index = 0
    
    For Each obj In CurrentProject.AllReports
      If obj.IsLoaded Then
        DoCmd.Close acReport, obj.Name, acSaveNo
      End If
      objs(index) = obj.Name
      index = index + 1
    Next
    
    SysCmd acSysCmdSetStatus, "Deleting reports, please wait..."
    
    For index = 0 To UBound(objs)
      DoCmd.SelectObject acReport, objs(index), True
      DoCmd.DeleteObject acReport, objs(index)
      DoEvents
    Next
  End If
  
  If CurrentProject.AllModules.Count > 1 Then
    ReDim objs(CurrentProject.AllModules.Count - 2)
    index = 0
    
    For Each obj In CurrentProject.AllModules
      If obj.Name <> conThisModuleName Then
        If obj.IsLoaded Then
          DoCmd.Close acModule, obj.Name, acSaveNo
        End If
        objs(index) = obj.Name
        index = index + 1
      End If
    Next
    
    SysCmd acSysCmdSetStatus, "Deleting modules, please wait..."
  
    For index = 0 To UBound(objs)
      DoCmd.SelectObject acModule, objs(index), True
      DoCmd.DeleteObject acModule, objs(index)
      DoEvents
    Next
  End If
  
  If CurrentProject.AllDataAccessPages.Count > 0 Then
    ReDim objs(CurrentProject.AllDataAccessPages.Count - 1)
    index = 0
    
    For Each obj In CurrentProject.AllDataAccessPages
      If obj.IsLoaded Then
        DoCmd.Close acDataAccessPage, obj.Name, acSaveNo
      End If
      objs(index) = obj.Name
      index = index + 1
    Next
    
    SysCmd acSysCmdSetStatus, "Deleting DAPs, please wait..."
    
    For index = 0 To UBound(objs)
      DoCmd.SelectObject acDataAccessPage, objs(index), True
      DoCmd.DeleteObject acDataAccessPage, objs(index)
      DoEvents
    Next
  End If
  
ExitHere:
  SysCmd acSysCmdClearStatus
  Screen.MousePointer = 0
  MsgBox "Operation completed successfully." & vbCrLf & vbCrLf & _
    "Module """ & conThisModuleName & """ must be deleted manually." & _
    IIf(Len(strOut) > 0, vbCrLf & vbCrLf & "Exceptions:" & vbCrLf & _
    strOut, ""), vbInformation, "Results:"
  MsgBox "You should run compact and repair before using this database.", _
    vbInformation, "Attention:"
  On Error Resume Next
  [green]'clear the startup form property if it exists since
  'no forms should be left after the deletes.[/green]
  CurrentDb().Properties("StartupForm").Value = "(none)"
  Exit Function
ErrHandler:
  If Err = 2008 Then   [green]'Forms closed by code won't get deleted - Access bug.[/green]
    strOut = strOut & Err.Description & vbCrLf
    Resume Next
  End If
  SysCmd acSysCmdClearStatus
  Screen.MousePointer = 0
  MsgBox "An Error occurred: " & Err & "-" & Err.Description
End Function

If you're using Access 97 this code has to be changed to use the old format, such as:
[tt]
Dim db As DAO.Database
Set db = CurrentDb()

db.QueryDefs.Count
db.Containers("Forms").Documents.Count
db.Containers("Scripts").Documents.Count [green]'macros[/green]
db.Containers("Modules").Documents.Count
db.Containers("Reports").Documents.Count
db.Containers("DataAccessPages").Documents.Count
[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[Inky2201]

Hello vbslammer,
Saw your very detailed entry on the forum for deleting objects from a database and wondered if you can help me. I am trying to delete all of the tables, on a command button, from a self contained database.
I have managed it with one table but for an unknown reason it does not want to delete all of the tables. I am using the code:
DoCmd.DeleteObject acTable, "TableName1"
This works but if I try to put this line in several times for other tables it will not work.
If I try to delete "TableName6" on its own it will not work.
I do not know the reason - I can only guess that it will delete table1 because that is the table that is being used and is therefore "live" at the time.
Any suggestions please?
Regards
I

 

[VBslammer]

Try something like this:

Sub DeleteAllTables()
  Dim tbl As AccessObject
  Dim lngTotal As Long
  
  For Each tbl In CodeData.AllTables
    [green]'delete all but the system tables[/green]
    If UCase(Left(tbl.Name, 4)) <> "MSYS" Then
      DoCmd.DeleteObject acTable, tbl.Name
      lngTotal = lngTotal + 1
    End If
  Next tbl
  
  MsgBox "Deleted " & lngTotal & " table(s).", vbInformation, "Results:"
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob