Drop All Tables that have the defined names!

[Mohsin Malik]

Hi All,

I have an access database which import the .csv file but after importing it is creating the tables

"google_ImportErrors"
If i won't delete this, for next time i would open the database it will create the tables and follow the same sequence like stated below and it increase the size of the database so deletion must be required which i do manually.

"google_ImportErrors1" (table names)
"google_ImportErrors2"
"google_ImportErrors3"
"google_ImportErrors4"

I want to delete/drop all tables with a query that have the names like "google_ImportError...", please guide me with this how to do that in automation?

Thank you
Mohsin

 

[jdraw]

You do realize this is Access trying to help you. Access is telling you that not all fields were imported successfully -- there was an error on these records during import.

Getting rid of the table will not correct the errors.

 

[Mohsin Malik]

Yes i know that Access is trying to help me with the issue is with the data type like i import all in "Short Text" and access ask me for a "Memo" field, i know my data is imported successfully and the need is just to drop tables with error, that would be great if you know the trick of dropping the tables automatically when the import is done!

Thanks

 

[jdraw]

Here is a small procedure, you could set it to run when a Button on a Form was clicked.

Sub DropTablesImportErrors()
Dim db As DAO.Database
Dim tbl As DAO.TableDef

   On Error GoTo DropTableByName_Error

For Each tbl In db.TableDefs
     If tbl.name Like "google_ImportErrors*" Then
     'drop table
        DoCmd.RunSQL ("DROP TABLE " & tbl.name)
     End If
Next tbl

   On Error GoTo 0
   Exit Sub

DropTableByName_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure DropTableImportErrors"
End Sub