DeletingalldatafromanAccessdatabase
Sometimesitmaybenecessarytodeleteallthedatainadatabase
whileretainingthetablestructure.Ifdonemanually,thisjobcan
quicklybecometedious.Ifyourdatabasehasmanytables,the
followingcodewillclearallthedatainahurry.
DimctrAsContainer,docAsDocument,dbAsDatabase
Setdb=CurrentDB()
Setctr=db.Containers!Tables
ForEachdocinctr.Documents
IfLeft$(doc.Name,4)<>"MSys"Then注釋：Tableisnotasystemtable
db.Execute"DELETE["&doc.Name&"].*"&_
"From["&doc.Name&"];"
EndIf
Nextdoc
Youmightnotwanttodeletedatafromlinkedtables.Thiscanbeeasily
accommodatedbycheckingtheConnectpropertyforeachTableDefdocument.
ThemodifIEdcodereads:
DimctrAsContainer,docAsDocument,dbAsDatabase
Setdb=CurrentDb()
Setctr=db.Containers!tables
ForEachdocInctr.Documents
IfLeft$(doc.Name,4)<>"MSys"And_
db.TableDefs(doc.Name).Connect=""Then
注釋：Tableisnotasystemtableoralinkedtable
db.Execute"DELETE["&doc.Name&"].*"&_
"From["&doc.Name&"];"
EndIf
Nextdoc
Youmustalsohavecascadingupdates/deletesenabledforthisprocedure
toclearthedatafromalltables.Asanalternative,runthecode
multipletimes.Onthefirstpassthroughthedatabase,thetableson
onesideoftherelationshiparecleared,allowingtheremainingtables
tobeclearedonthenextpass.
ThistipwascontributedbyDr.MichaelS.Stoner,HenriKover,andStephenBond.
