Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1061
  • Last Modified:

How do I clear records from an Access table using VBA?

I want to clear the records in the 'LSI_US_BookOrders' table before I run the next line of code.

DoCmd.TransferText acImportDelim, "LSI_US ImportSpec", "LSI_US_BookOrders", "f:\Shared Documents\LSI_US.txt"

I can't remember.  Searched long enough. Help.
0
c9k9h
Asked:
c9k9h
  • 2
  • 2
  • 2
2 Solutions
 
jmoss111Commented:
Hi c9k9h,

dim db as dao.database
set db = currentdb
db.execute("DELETE * FROM LSI_US_BookOrders"), dbfailonerrors

Regards,

Jim
0
 
c9k9hAuthor Commented:
Thank you Jim.  I am getting a compile error.  User-defined type not defined on the dao line.  I'm working on a remote server and 'References' is greyed out so I cannot change anything.  Is there a work around that you know of?
Thanks!
Karen
0
 
jmoss111Commented:
Dim dbcur As Database
   Set dbcur = DBEngine.Workspaces(0).OpenDatabase("X:\MyPath\myRemoteDB.mdb")    
    dbcur.execute("DELETE * FROM LSI_US_BookOrders"), dbfailonerrors

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Jeffrey CoachmanMIS LiasonCommented:
c9k9h

Try using SQL:
(BTW, you always want some type of confirmation before deleting *all* records! Because deleted records in Access can never be recovered)

Dim bytConfirm As Byte
bytConfirm = MsgBox("Delete all records? (deleted records cannot be recovered)", vbYesNo + vbQuestion)
    If bytConfirm = vbNo Then
        Exit Sub
    End If
   
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete * FROM LSI_US_BookOrders "
    DoCmd.SetWarnings True
   
    MsgBox "Done deleting all from the table", vbInformation

JeffCoachman
0
 
c9k9hAuthor Commented:
Jim - still the compile error.  I've sent a note to the admin to see if I can get the access to the 'References.'  I'm sure both of you're solutions would work if it were available.

Jeff - You're absolutely right on the caution of deleting records.  I actually did have a message box.  I like your code and it works like a charm.

Thank you both - I think you both gave good posts for other readers as well.  I'm upping the points and splitting them.
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now