Phucon
That code should do the trick. I found mine. Here it is for you perusal.
Function DeleteErrorTables()
'Purpose : Delete Error Tables generated by imports.
'DateTime : 10/29/2008 09:38
'Author : Bill Mosca
'Requires : DAO library
Dim strTableName As String
Dim strSQL As String
Dim intCount As Integer
Dim intTableCt As Integer
Dim db As DAO.Database
On Error GoTo err_PROC
Set db = CurrentDb
For intCount = (db.TableDefs.Count - 1) To 0 Step -1
strTableName = db.TableDefs(intCount).Name
If Right(strTableName, 13) = "_ImportErrors" Then
strSQL = "DROP TABLE [" & strTableName & "]"
db.Execute strSQL, dbFailOnError
intTableCt = intTableCt + 1
End If
Next intCount
If intTableCt = 0 Then
MsgBox "No Import Error tables found.", vbOKOnly
Else
Application.RefreshDatabaseWindow
MsgBox intTableCt & " error tables were deleted." _
& vbNewLine & "Compacting is suggested to " _
& "remove the tables from memory."
End If
exit_PROC:
Set db = Nothing
Exit Function
err_PROC:
MsgBox Err.Description
Resume exit_PROC
End Function
Regards,
Bill
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of saigonf7q5
Sent: Sunday, January 27, 2013 12:27 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Deleting tables
Thank you Bill.
If it's ok to do that I will use that approach, keep the Delete Procedure,
delete the Error Tables right after each import.
Below is the code that I use (If I remember correctly, I think I copied it from
MS Access Help and modified it. It's good to see or learn other techniques, like
yours...
Thanks
Phucon
Public Function DeleteErrTables()
Dim tdf As TableDef
Dim Delt As Integer
Dim tblName As String
Delt = MsgBox("This will delete all Import Error tables. Proceed?", vbYesNo)
If Delt = vbYes Then
For Each tdf In CurrentDb.TableDefs
If tdf.Name Like "*ImportErrors*" Then
CurrentDb.TableDefs.Delete tdf.Name
End If
Next tdf
End If
Application.RefreshDatabaseWindow
End Function
--- In MS_Access_Professionals@yahoogroups.com
, "Bill Mosca" wrote:
>
> Phucon
>
> I had an application that had the same thing, bad excel data. I just ran a
procedure to delete the ImportError table. If you want my code I'll have to look
for it. I can't remember which application it was in.
>
> Since the import error tables are very small there shouldn't be much bloat.
Compacting on close problems usually stem from trying to compact a db on a
network. Setting a local front end to compact on close is perfectly fine. If
your database is split the import error tables will be created locally.
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> My nothing-to-do-with-Access blog
> http://wrmosca.wordpress.com
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com
, "saigonf7q5" wrote:
> >
> > I have a database that users need to import an Excel sheet every week. After
the imported process, Access created 2 "Data Feed$_ImportErrors" tables,
reporting that there's data conversion errors.
> >
> > Will the error tables be created and accumulated after every import?
> > Since the user's not willing to investigate or fix the Excel data, I added a
function to delete these error tables immediately after they were created each
time.
> >
> > My concern is if this is a good practice to do? Will the size of the
database grow? Is "Compact On Close" necessary?
> >
> > While ago, I read an article saying that Compact On Close can cause trouble.
I am wondering if that only happened on the older version of Access or it's
still the case.
> >
> > Phucon
> >
>
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar