VBA/Excel/Access/Word/Access/AllTables — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 15:46, 26 мая 2010
Содержание
- 1 Delete backup tables
- 2 iterates the AllTables collection looking for a table whose name includes the substring "Backup".
- 3 iterates the AllTables collection, opens each nonsystem table, prints its structure, and then closes the open table.
- 4 iterates the AllTables collection to determine if a table is open. If it is, it prompts the user to close it.
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>