Results 1 to 3 of 3
  1. #1
    jdubp is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2008
    Posts
    15

    Delete a table only if it exists or just replace the table

    How can I delete a table only if it exists? Or can I import the Excel files to replace the existing tables? XLSX files are always the same and table names are always the same.



    Code works fine, but we are in area with lots of electrical outages and computer shut down while this was in process.. and table had already been deleted.


    Following is the code I use to import the Excel XLSX. (If a table is already deleted, this crashes with message that the table does not exist)

    Private Sub Command0_Click()
    DoCmd.SetWarnings False

    DoCmd.DeleteObject acTable, "QB ITEM LIST"
    DoCmd.DeleteObject acTable, "QB PENDING BUILDS"
    DoCmd.DeleteObject acTable, "QB OPEN POs"

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "QB ITEM LIST", "C:\Users\Jim\Desktop\QB Reports as XLSX\EXPORT TO MS ACCESS ITEM LISTING.xlsx", True, "Sheet1!"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "QB PENDING BUILDS", "C:\Users\Jim\Desktop\QB Reports as XLSX\Export to Access PENDING BUILDS.xlsx", True, "Sheet1!"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "QB OPEN POs", "C:\Users\Jim\Desktop\QB Reports as XLSX\Export to Access OPEN PURCHASES ORDERS.xlsx", True, "Sheet1!"

    DoCmd.RunMacro "001 PROCESS DAILY OPEN POS PENDING BUILDS, ITEM LIST & INV"
    DoCmd.SetWarnings True
    MsgBox "THIS PROCESS IS FINISHED, YOU CAN RETURN TO MAIN MENU!"
    DoCmd.OpenForm ("Switchboard")

    End Sub


    Thanks,

    Jim

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    try

    Code:
    on error resume next
    DoCmd.DeleteObject acTable, "QB ITEM LIST"
     DoCmd.DeleteObject acTable, "QB PENDING BUILDS"
     DoCmd.DeleteObject acTable, "QB OPEN POs"
    on error goto 1
    or a more informative solution would be

    Code:
    on error goto errCtrl
    DoCmd.DeleteObject acTable, "QB ITEM LIST"
    DoCmd.DeleteObject acTable, "QB PENDING BUILDS"
    DoCmd.DeleteObject acTable, "QB OPEN POs"
    on error goto 1
    
    and at the bottom of your sub put
    
        exit sub
    
    ErrCtrl:
    select case err
        case ??? 'put the error number generated here
            'ignore - table already deleted
            resume next
        else
            msgbox "Error " & err.number & " " & err.description
    
    end select

  3. #3
    jdubp is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2008
    Posts
    15
    AJAX- thank you. It worked perfectly.

    Jim

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Add Entry to a Table Unless Already Exists, VBA
    By nick404 in forum Programming
    Replies: 12
    Last Post: 07-09-2015, 12:06 PM
  2. Replies: 2
    Last Post: 10-09-2012, 03:20 PM
  3. Replies: 2
    Last Post: 01-24-2012, 02:16 PM
  4. If table exists then
    By SorenIX in forum Programming
    Replies: 2
    Last Post: 06-26-2011, 08:42 AM
  5. How to tell if a file exists and then delete it
    By orcinus in forum Programming
    Replies: 3
    Last Post: 05-17-2010, 05:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums