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
> >
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar