Import Errors!!

[robk5787]

I am currently importing an Excel table into my database, but unfortunately Access does not like a part my imported data. Mostly the date due to it not being in text format. Due to the import errors, Access creates an import table to store the data it had an errors with. Is there a way without writing VB code to delete this table in a Macro or something so that it does not fill up my table folder? I tried a delete query and a simple Macro that would delete anything with **Sheet Import** errors in the text, but no luck. Any help is greatly appreciated! Thanks.

 

[Mile-O]

Here's a quick bit of code that will look through your database for all tables containing the word Error in their title and ask you if you wish to delete them.

Public Function DeleteTable()

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    
    Set db = CurrentDb
    
    For Each tdf In db.TableDefs
        If InStr(1, tdf.Name, "Error") Then
            If MsgBox("Delete " & tdf.Name & "?", vbQuestion + vbYesNo, _
                "Error Table Found") = vbYes Then
                db.TableDefs.Delete tdf.Name
            End If
        End If
    Next

End Function

 

[robk5787]

I appreciate your response to my question, and have tried to use this with my DB. As a somehwat beginner I haven't had the chance to really try any of the module development yet, and was hoping you could tell me how to input this in to my DB! I tried to create a module and type this code in, but it keeps giving me an error message.

 

[namliam]

Make sure your DAO reference is set!

Tools => References

In the menu from the module screen (Hit ALT + F11 when you are in the DB window)

Regards

 

[Mile-O]

It should work no problem if you are using Access '97.
If you are using a later version then you have two options: fix a reference or rewrite the code in ADO. (I don't know ADO so can't help there.)

So, to change a reference:

1) Open a module
2) Goto Tools -> References
3) Look through the list for Microsoft Data Access Objects 3.6
4) Check it
5) Move its priority above Microsoft ActiveX Data Objects


Now:

Copy the code (cut and paste into a module)
Create a macro and select RunCode
In the Function Name box enter: DeleteTable()
Save the macro; it should work.

 

[robk5787]

Everytime I try to run this module I get a "Type Mismatch" error message with the "Current DB" part. I tried to play with it, but I do not to delet any part of my other DB components. I hate ask for more help, but I am new to VB code. I appreciate the help.

 

[Mile-O]

Everytime I try to run this module I get a "Type Mismatch" error message with the "Current DB" part.

There should be no space in CurrentDB.