VBA/Excel/Access/Word/Access/AllTables

Материал из VB Эксперт
Перейти к: навигация, поиск

Delete backup tables

   <source lang="vb">

Public Sub DeleteBackupTables()

  Dim tbl As Variant
  For Each tbl In CurrentData.AllTables
     If InStr(1, tbl.Name, "Backup", vbTextCompare) > 0 Then
        If vbYes = MsgBox("Delete table " & tbl.Name & "?", _
                          vbYesNoCancel Or vbQuestion, _
                          "Confirm Table Deletion") Then
           DoCmd.DeleteObject acTable, tbl.Name
        End If
     End If
  Next
  

End Sub

</source>
   
  


iterates the AllTables collection looking for a table whose name includes the substring "Backup".

   <source lang="vb">

Public Sub RestoreFromBackup()

  Dim tbl As Variant
  Dim intPos As Integer
  Dim strNewName As String
  
  For Each tbl In CurrentData.AllTables
     intPos = InStr(1, tbl.Name, " Backup")
     If intPos > 0 Then
        strNewName = Left(tbl.Name, intPos - 1)
        If MsgBox("Replace " & strNewName & " with " & tbl.Name _
                  & "?", vbYesNoCancel Or vbQuestion, _
                  "Rename Table") = vbYes Then
           DoCmd.SetWarnings False
           DoCmd.Rename strNewName, acTable, tbl.Name
           DoCmd.SetWarnings True
        End If
     End If
  Next

End Sub

</source>
   
  


iterates the AllTables collection, opens each nonsystem table, prints its structure, and then closes the open table.

   <source lang="vb">

Public Sub PrintTableStructures()

   Dim tbl As Variant
   
   For Each tbl In CurrentData.AllTables
         
      If InStr(1, tbl.Name, "MSys", vbBinaryCompare) = 0 Then
         DoCmd.OpenTable tbl.Name, acViewDesign
         If MsgBox("Print table structure?", _
                   vbQuestion Or vbYesNoCancel, _
                   "Print Table Structure") = vbYes Then
            DoCmd.PrintOut acPrintAll
         End If
         DoCmd.Close acTable, tbl.Name, acSaveNo
      End If
   Next
  

End Sub

</source>
   
  


iterates the AllTables collection to determine if a table is open. If it is, it prompts the user to close it.

   <source lang="vb">

Public Sub CloseTables()

   Dim tbls As AllTables
   Dim tbl As Variant
   
   Set tbls = Access.Application.CurrentData.AllTables
   For Each tbl In tbls
      If tbl.IsLoaded Then
         If vbYes = MsgBox("Close " & tbl.Name & "?") Then
            DoCmd.Close acTable, tbl.Name, acSavePrompt
         End If
      End If
   Next

End Sub

</source>