Results 1 to 7 of 7
  1. #1
    Robyn_P's Avatar
    Robyn_P is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2017
    Posts
    150

    Deleting all Import Errors tables


    Hi,

    I hope someone can help. I have a very simple database that imports some data, processes it and then spits out some output. The import sometimes generates ImportError tables, which is fine, the data still imports so I'm not that worried about that. I would however like to keep the database clean and so I would like to delete all these tables.

    I have the following code, which errors with a 3295 "Syntax error in Drop Table or Drop Index" and I'm not sure what the error is. This is my code

    Code:
         
    ' Delete any ImportErrors tables  
    
     Dim t As TableDef
    
     For Each t In CurrentDb.TableDefs
            If t.name Like "*ImportErrors*" Then DoCmd.RunSQL ("DROP TABLE " & t.name)
        Next

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Make the sql into a string variable and
    a) Debug.print it
    b) Execute it from the immediate window

    Always break the problem down to small parts and test each in turn.

    https://www.google.com/search?q=drop...hrome&ie=UTF-8

    This example also uses Execute?
    https://docs.microsoft.com/en-us/off...oft-access-sql
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't suppose there are any spaces in any of those table names? AFAIK, Access uses underscore in import error table names, but I thought it might be worthwhile to ask the question.
    EDIT - or perhaps there are spaces in the original table name, so you have "My Table_ImportErrors"?
    There are at least 3 other methods for dropping tables if you can't get this way to work.

    DoCmd.DeleteObject acTable, "tblname"

    CurrentDb.Execute "DROP TABLE tablename", dbFailOnError

    CurrentDb.TableDefs.Delete "tablename"

    EDIT2 - drop the ()? So
    DoCmd.RunSQL "DROP TABLE " & t.name
    Better to use Execute method so that you don't have to deal with warnings?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    pretty sure an import errors table will have spaces (tablname importerrors) so you need to surround with square brackets

    "DROP TABLE [" & t.name & "]"

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have one such table in a db I use to play around with for this forum. It has an underscore, no space, so I suspect that's the default. Maybe that's a recent change because M$ is just wising up re spaces in names. The name is Sheet1$_ImportErrors. I suspect spaces in table names would remain, though.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Robyn_P's Avatar
    Robyn_P is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2017
    Posts
    150
    Using CurrentDb.TableDefs.Delete (t.name) worked perfectly. Thank-you all

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    FWIW I managed to get the DROP option to work on a few tables I could afford to lose?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Replies: 1
    Last Post: 07-27-2018, 10:34 PM
  2. Help with Import Data from Excel Errors
    By mgw2786 in forum Import/Export Data
    Replies: 3
    Last Post: 04-24-2017, 11:43 PM
  3. Import errors when importing txt files
    By lenk73 in forum Access
    Replies: 4
    Last Post: 04-17-2015, 02:39 PM
  4. Conversion Errors in import
    By sdc1234 in forum Access
    Replies: 4
    Last Post: 04-22-2014, 01:43 PM
  5. Errors during import of spreadsheet
    By crowegreg in forum Import/Export Data
    Replies: 9
    Last Post: 07-18-2012, 07:10 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