Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: How to Procedureally Delete an Import Errors table

    I am working on an application where Access is opened from Excel, a text file of 600,000+ lines from a third party is imported into an emptied table. As a precaution, records are deleted if the first field is Null, then records are counted using code in a module. If the count is less than a certain number, then I know that the import failed and an error message text file is written to tell the Excel procedure there was an import failure. After the import, Access exits and control is returned back to the Excel VBA procedure. If the import fails, I would like to delete the import error file before quitting Access. I have found that the Access macro displays and error message and halts if I try to delete a non-existant import error file. In the macro, Warnings are set to "No" just above the step to delete the error file, but this did not suppress the error message and halting.

    Question: is there a way to delete a table and ignore the error if the table does not exist. Table name is newdata_ImportErrors.

    Jerry

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    SQLString="DROP TABLE newdata_ImportErrors;"

    YourDB.Execute SQLString,,,adcmdtext
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Add this code to a module:

    Code:
    Public Function DropTable() As Boolean
    
        On Error Resume Next
        DoCmd.DeleteObject acTable, "tblTemp"
        
    End Function
    Then call it from the macro using RunCode. The parameter will be DropTable().

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    DCKunkle, I chose your solution. After feeding the Access database an incorrectly delimited text file for import, with your solution, I was able to delete the import error table from a module, and for testing, I attempted to delete a non-existant table and there was no error message or interruption.

    Your solution:
    HTML Code:
    Public Function DropTable() As Boolean
    
        On Error Resume Next
        DoCmd.DeleteObject acTable, "tblTemp"
        
    End Function
    Thanks to M Owen too for the reply.

    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •