Deleting Tables...necessary?

[George Too]

Hello all,

We import heavily into our database, basically it's just the same type of data over and over. The database has a protection where it won't let duplicate records into the tables. This process creates several "error" tables which contain the duplicated records. I would like to delete them with code (by pressing a button maybe?), but i'm not sure wheter this is really necessary. Can you advise me on this? And, should I delete these files, what would the code look like?

Thank you,
George

 

[IMO]

The code to delete a table is

DoCmd.DeleteObject acTable, "Import_err"

"Import_err" being the table with the errors. When you say it doesn't allow duplicates, is that duplicate ID numbers? If so, what happens if the data is different (newer) but the ID number is the same? I think it's a good idea to delete them to stop bloating, but make sure they really are duplicates in every aspect.

IMO

 

[George Too]

Thanks IMO, the field used to check for dups allows me to be 100% sure that these are dup records. Now, since we import heavily, we get several tables like "Import_err1", "Import_err2", etc. How do I make the code delete all these tables? Or will it just look at the "Import_err" part of the name and delete them all?

Again, thanks for your time,
George

 

[IMO]

You could place this code in the click event of your import button after the code that imports the tables.

Private Sub YourButton_Click()

Dim tdTable As TableDef
For Each tdTable In CurrentDb.TableDefs
If Left(tdTable.Name, 6) = "import" Then
DoCmd.DeleteObject acTable, tdTable.Name
End If
Next tdTable

End Sub

This code will delete any tables with names starting 'import'. Don't forget to set a reference to 'Microsoft DAO 3.6 Object Library'.

IMO

 

[George Too]

Now, IMO, that's more like it...
Thanks a lot, that works wonderfully.

Regards,
George

 

[IMO]

Glad you got it working

IMO