DELETE * FROM MULTIPLE TABLES in ACCESS97

[jane30]

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)

 

[WildHare]

There are over 10 tables in my db.

How many over 10? 1? or 100?...

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.

Sub DeleteALL()
 Set db = CurrentDb
  With db
   For Each tbl In .TableDefs
   If Not Mid(tbl.Name, 1, 4) = "MSYS" Then
      DoCmd.RunSQl "Delete * from " & tbl.Name & ";'
   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 - - just like everyone else
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[jane30]

That's exactly what I need. Thanks alot, JIMATFAA :