Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DELETE * FROM MULTIPLE TABLES in ACCESS97

Status
Not open for further replies.

jane30

Programmer
Nov 14, 2000
92
US
Hi, I'm Access97 user. There are over 10 tables in my db. I need a magic code to delete * from all tables before I update them using Append Query. Besides, is it possible to use one query to append different records to all tables? Thanks a bunch in advance :-D)
 
There are over 10 tables in my db.

How many over 10? 1? or 100?...[smile]

I need a magic code to delete * from all tables before I update them using Append Query.

Try the undocumented[red] DoCmd.RunSQL DELETE * From * where * <=> *[/red] command..

Seriously, sorry there is no one &quot;magic&quot; command that will delete every record from every table. It sounds like you don't want to write 10+ delete queries, so you can use the other method. It involves looping through each table in your Tables collection, and applying a &quot;Delete * from {tablename}&quot; SQL statement to each one.
Code:
Sub DeleteALL()
 Set db = CurrentDb
  With db
   For Each tbl In .TableDefs
   If Not Mid(tbl.Name, 1, 4) = &quot;MSYS&quot; Then
      DoCmd.RunSQl &quot;Delete * from &quot; & tbl.Name & &quot;;'
   Next tbl
  End With
Set db = Nothing
End Sub
You may want to throw an &quot;Docmd.SetWarnings FALSE&quot; in there to keep from being prompted for each action. Up to you.

Besides, is it possible to use one query to append different records to all tables?

Ehhhh..Not really. Can you elaborate on this one?



Remember, you're unique - [smile]- just like everyone else
Another free Access forum:
 
That's exactly what I need. Thanks alot, JIMATFAA ::)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top