Macro to delete ImportErrors tables

[lmnnt]

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)

[boblarson]

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:

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.

[lmnnt]

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.

[boblarson]

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.

[lmnnt]

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!

[clean32]

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?

[lmnnt]

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

[clean32]

SetWarnings no

at the beginning of your macro

i doint know if this will fix it or not

[lmnnt]

thanks heaps - I'll give it all a try

[lmnnt]

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"

[boblarson]

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()

[lmnnt]

Hi Bob
I added the () but another error message "The expression you entered has a function name that XXX can't find"

[boblarson]

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.

[lmnnt]

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

[boblarson]

Sorry, I had a typo in my saved code. It should read:

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