Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    lmnnt is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2011
    Location
    Darwin NT Australia
    Posts
    16

    Macro to delete ImportErrors tables

    I have 2 problems and would be forever grateful if they could be solved:
    1. I need to build a macro to delete the ImportErrors tables that are created in my database during the import of about 50 files.
    2. Also I need for an existing macro to continue to run if any of the saved import tables are not yet filed (the files that the saved import tables refer to come to me over a number of days but I need to start working on the imported data immediately I start receiving)

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Well, for #1 - just paste this function into a STANDARD module (not a form, report or class module) and then you can call it easily from your macro:
    Code:
    Function DeleteImportErrTables()
        Dim z As Integer
        Dim db As DAO.Database
        
        Set db = CurrentDb
        For z = db.TableDefs.Count-1 To 0 Step -1
            If InStr(1, db.tblDefs(z).Name, "ImportError") > 0 Then
                DoCmd.DeleteObject acTable, db.TableDefs(z).Name
            End If
        Next z
    End Function
    In the macro just use the

    Action: RunCode

    and in the function name just put

    DeleteImportErrTables

    As for the second part - I'm not sure I am understanding what you mean there.

  3. #3
    lmnnt is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2011
    Location
    Darwin NT Australia
    Posts
    16
    Hi there
    Thanks for that .. just to clarify ... will this delete all the tables that have “ImportError” in the name of the table or do I have to have the word Like in the function ? – the import error tables are titled something like “Ref_001_ImportErrors1”.

    The second part is that I have a macro that imports a number of files from one source and places them in a single table. Not all the files come in at the same time but I need to start processing the ones that I do receive as soon as I get them. Currently, if a file has not yet been filed into the source the macro stops. What I need the macro to do is ... if x file isn't there - then continue. As I say the files come in over a period of days - although it would be great if they all came in together

    Hope this makes it a bit clearer.

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Quote Originally Posted by lmnnt View Post
    Hi there
    Thanks for that .. just to clarify ... will this delete all the tables that have “ImportError” in the name of the table or do I have to have the word Like in the function ? – the import error tables are titled something like “Ref_001_ImportErrors1”.
    That code will delete any table that has the word ImportError in it. You don't need to use the LIKE. This will work. (it has been tested)
    The second part is that I have a macro that imports a number of files from one source and places them in a single table. Not all the files come in at the same time but I need to start processing the ones that I do receive as soon as I get them. Currently, if a file has not yet been filed into the source the macro stops. What I need the macro to do is ... if x file isn't there - then continue. As I say the files come in over a period of days - although it would be great if they all came in together

    Hope this makes it a bit clearer.
    I would have your macro either move the file (preferable) to another folder when done processing or have it delete it. That way you can simply run it over and over and it will deal with the files in the defined location.

  5. #5
    lmnnt is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2011
    Location
    Darwin NT Australia
    Posts
    16
    Hi Bob
    Maybe I’ve got the macro incorrect – I have a line entry for each file to import that says (Action) RunSavedImportExport – (Argument) Import_Ref_001 - and so on for each of the files I need. Is that the way to do it?
    Unfortunately there are other files in the source folder that need to be there for a different kind of processing and named similar (ie Ref_XYZ_001) but I only need the ones called Ref_001.
    I can’t move or split them out because of the other process (that’s done via a different method)

    I guess I’m asking advice on the best way to import only the files I want out of a source folder to populate a single table in Access – it may be that I’m asking the impossible but worth a try!

  6. #6
    clean32 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    51
    do you mean you need some think like

    If table XXX is there then import table if not then go to next action

    i doint know who to do it. i assume you get a box saying item or control or record not found?

  7. #7
    lmnnt is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2011
    Location
    Darwin NT Australia
    Posts
    16
    Hi clean32 yes that’s exactly what I need.
    Here are the error messages:
    Pop-up ……… can’t find the database file (filepath\XXX
    Make sure you entered the correct path and file name.
    (OK button)
    pop-up Action Failed
    only option is “Stop All Macros” button.

    This stops all further actions

  8. #8
    clean32 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    51
    SetWarnings no

    at the beginning of your macro

    i doint know if this will fix it or not

  9. #9
    lmnnt is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2011
    Location
    Darwin NT Australia
    Posts
    16
    thanks heaps - I'll give it all a try

  10. #10
    lmnnt is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2011
    Location
    Darwin NT Australia
    Posts
    16
    Hi Bob
    I tried the process (selected Module and pasted the code you provided and did as you said in the macro and came up with the following error message – where have I gone wrong? :
    "The object doesn’t contain the automation object ‘DeleteImportErrTables”
    You tried to run a visual basic procedure to set a property or method for an object. However the component doesn’t make the property of method available to Automation operators
    Check the component’s documentation for information on the properties and nethods it makes available for automation operations"

  11. #11
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    You probably put the call into the EVENT PROPERTY instead of the VBA Window for that event. If you do that, you have to include the parens:

    DeleteImportErrTables()

  12. #12
    lmnnt is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2011
    Location
    Darwin NT Australia
    Posts
    16
    Hi Bob
    I added the () but another error message "The expression you entered has a function name that XXX can't find"

  13. #13
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Quote Originally Posted by lmnnt View Post
    Hi Bob
    I added the () but another error message "The expression you entered has a function name that XXX can't find"
    Are you ABSOLUTELY sure that you put the function into a STANDARD MODULE (not a form module, not a report module, not a class module, but a standard module)? A standard module is one which you get when you click the CREATE tab on the Ribbon and then select MODULE which is down next to the Macro one. Then you would name the module something like modUtils or something other than the name of the function.

  14. #14
    lmnnt is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2011
    Location
    Darwin NT Australia
    Posts
    16
    Hi Bob
    Sorry to seem so dense – I created the module as you have described and then in the macro I have (Action) RunQuery and for (Arguments) I went down to the Function name and clicked on the “… button” then in the expression builder I selected Functions and inserted the function I made and it gave me “DeleteImportErrTables ()” – then clicked on OK

    When I run the process now I get this error message with the line “Method or data member …: highlighted in yellow and :… “.tblDefs” highlighted in blue.

    Compile error:
    Method or data member not found
    Function DeleteImportErrTables()
    Dim z As Integer
    Dim db As DAO.Database

    Set db = CurrentDb
    For z = db.TableDefs.Count - 1 To 0 Step -1
    If InStr(1, db.tblDefs(z).Name, "ImportError") > 0 Then
    DoCmd.DeleteObject acTable, db.TableDefs(z).Name
    End If
    Next z
    End Function

  15. #15
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Sorry, I had a typo in my saved code. It should read:

    If InStr(1, db.TableDefs(z).Name, "ImportError") > 0 Then

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error: Could not delete from specified tables
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-03-2011, 02:07 PM
  2. Replies: 2
    Last Post: 08-24-2010, 03:32 PM
  3. Update / delete and recreate access tables
    By metro17 in forum Access
    Replies: 4
    Last Post: 09-23-2009, 04:45 PM
  4. Replies: 0
    Last Post: 08-25-2008, 12:17 PM
  5. Could not delete from specified tables.
    By bullwinkle55423 in forum Queries
    Replies: 0
    Last Post: 08-31-2006, 08:14 AM

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