ACCESS FEED: [MS_AccessPros] Re: Deleting tables

Senin, 28 Januari 2013

[MS_AccessPros] Re: Deleting tables

Thank you very much Bill. So now I learned another technique and I will experiment the code.

--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" wrote:
>
> 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]
>