Solution: automated table clearing

• • Reply

  19 years ago

  by tlosekoot

  Thamar

  Poland, Poland

  Joined 19 years ago

  A very simple solution to clear MS-Access '97 and 2000 tables:
  

  
  Create a form with a combobox called "boxTables" and a button called "btnClearTable", then add the next code to the form:
  

  
  Option Compare Database
  

  
  Private Sub Form_Activate()
     [boxTables].RowSourceType = "Value List"
     For Each Item In Application.CurrentDb.tabledefs
         [boxTables].RowSource = [boxTables].RowSource & ";" & Item.Name
     Next
  End Sub
  

  
  Private Sub btnClearTable_Click()
     Dim strSQL As String
     For Each Item In Application.CurrentDb.tabledefs
         DoCmd.SetWarnings warningsoff
         If Item.Name = [boxTables].Value Then
             strSQL = "DELETE " & [boxTables].Value & ".* FROM " & _
               [boxTables].Value & ";"
             DoCmd.RunSQL strSQL
         End If
         DoCmd.SetWarnings warningson
     Next
  End Sub
  

  
  BE CAREFUL! This thing will delete everything in th table so be sure to pick the right table name from the combobox...
  

  
  I would recommend including an "are-u-sure" messagebox...
  

  
  If anyone has a better solution, please let me know.
  

  
  Regards,
  

  
  Thamar.

  Report abuse
• • Reply

  19 years ago

  by James Crowley

  James Crowley

  London, United Kingdom

  Joined 20 years ago

  Would you like to have this code properly published on DeveloperFusion.com? If so, visit the members area

  Report abuse
• • Reply

  19 years ago

  by CodeWarrior76

  CodeWarrior

  Calgary, CA, United States

  Joined 19 years ago

  *Side note:   The above code is VBA and should be put in a VBS module directly in Access as DoCmd is unavailable in VB

  Report abuse
• • Reply

  19 years ago

  by tlosekoot

  Thamar

  Poland, Poland

  Joined 19 years ago

  ...you're right, Daryll, I forgot about that. Since I'm still all the time working with VBA 6 I got used to those DoCmd functions.
  

  
  How to translate that to VB? I only got VB 5.0 and it's not working as it should (crashing all the time under WinNT 4.0 WS)...  :-(
  

  
  And yes, please James, but how to get it there, and isn't that a bit simple routine to publish? I think anyone could have thought that up...
  

  
  Regards,
  

  
  Thamar

  Report abuse
• • Reply

  19 years ago

  by James Crowley

  James Crowley

  London, United Kingdom

  Joined 20 years ago

  Even simple things like that are worth publishing... it will save someone a few precious minutes, or show a beginner how to solve their problem :-) Any code submitted via the system will be evaluated by me first anyway... only code I think is worth publishing will be published :-)

  Report abuse
• • Reply

  19 years ago

  by James Crowley

  James Crowley

  London, United Kingdom

  Joined 20 years ago

  To 'get it there', log in, and then click Add Resource on the left hand navigation bar (or at the top of every page)

  Report abuse
• • Reply

  19 years ago

  by tlosekoot

  Thamar

  Poland, Poland

  Joined 19 years ago

  I'll do that a little later today, gotta run now 'cause I gotta call my girlfriend, and I've got only one phone line :-( Waiting for my permanent connection for 3 months now...
  

  
  BTW: You got mail... :-)
  

  
  Later,
  

  
  Thamar

  Report abuse