How do I disable the import errors table? in Access Database General Questions  
 |  Edit my Profile  |  Help
 
     
   
 
 
 
Nicholas Scarpinato 5/26/2005 1:06 PM PST
  Question
  I'm importing text files into an Access table. The only problem is, when I
import the file, Access creates a import errors table. That isn't the
problem, I don't care about that table. In fact I'm glad it's being created
because it means my import is working the way I designed it, lol. The problem
is, I want to get rid of the table, but since I have different names on the
import files, the error tables name is never the same. How can I get rid of
the table? Or can I force Access not to create it?
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Douglas J. Steele 5/26/2005 2:47 PM PST
   
  I don't believe there's any way to prevent Access from creating it.

You know the name of the import file, though, so you should be able to
figure out the name of the error table. Worst case, make an array of all of
the tables before and compare it to the list of tables after.

On the other hand, the following query should return a list of your tables
in reverse order of creation (i.e.: the most recently created table will be
first)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Nicholas Scarpinato" <NicholasScarpinato@discussions.microsoft.com> wrote
in message news:648B38E5-8BF1-411B-B70A-4DFBA300B0A4@microsoft.com...
> I'm importing text files into an Access table. The only problem is, when I
> import the file, Access creates a import errors table. That isn't the
> problem, I don't care about that table. In fact I'm glad it's being
> created
> because it means my import is working the way I designed it, lol. The
> problem
> is, I want to get rid of the table, but since I have different names on
> the
> import files, the error tables name is never the same. How can I get rid
> of
> the table? Or can I force Access not to create it?


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
aaron.kempf@gmail.com 5/26/2005 3:20 PM PST
   
  Dim tbl As AccessObject

For Each tbl In CurrentData.AllTables
If tbl.Name Like "*_ImportError*" Then
DoCmd.DeleteObject acTable, tbl.Name
End If
Next tbl

 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Douglas J Steele 5/27/2005 5:14 AM PST
  Answer
  Not sure that'll necessarily work if there are two _ImportError tables in a
row.

Instead, try:

Dim intLoop As Integer

For intLoop = (CurrentData.AllTables.Count - 1) To 0 Step -1
If CurrentData.AllTables(intLoop).Name Like "*_ImportError*" Then
DoCmd.DeleteObject acTable, CurrentData.AllTables(intLoop).Name
End If
Next intLoop

At least, I think that's how you'd do it with the AllTables collection: I
always use the TableDefs collection (and I only have Access 97 available to
me at present, so I can't test)

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim intLoop As Integer

Set dbCurr = CurrentDb()
For intLoop = (dbCurr.TableDefs.Count - 1) To 0 Step -1
Set tdfCurr = dbCurr.TableDefs(intLoop)
If tdfCurr.Name Like "*_ImportError*" Then
DoCmd.DeleteObject acTable, tdfCurr.Name
End If
Next tbl


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


<aaron.kempf@gmail.com> wrote in message
news:1117145905.049259.276700@f14g2000cwb.googlegroups.com...
> Dim tbl As AccessObject
>
> For Each tbl In CurrentData.AllTables
> If tbl.Name Like "*_ImportError*" Then
> DoCmd.DeleteObject acTable, tbl.Name
> End If
> Next tbl
>


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Nicholas Scarpinato 5/27/2005 6:24 AM PST
   
  Ok, now how do I call this from a macro? Do I have to give it a function name
or something?

I'm pretty good with Access queries but I never really learned how to write
functions, so forgive me if I ask dumb questions, lol.
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Nicholas Scarpinato 5/27/2005 6:32 AM PST
   
  That first one worked perfectly, thanks! I'm not concerned if there are two
ImportError tables. If I did the macros right they should delete the tables
every time they're ran, and the import macro is the only way to import the
data automatically, so there should only ever be one.

"Douglas J Steele" wrote:

> Not sure that'll necessarily work if there are two _ImportError tables in a
> row.
>
> Instead, try:
>
> Dim intLoop As Integer
>
> For intLoop = (CurrentData.AllTables.Count - 1) To 0 Step -1
> If CurrentData.AllTables(intLoop).Name Like "*_ImportError*" Then
> DoCmd.DeleteObject acTable, CurrentData.AllTables(intLoop).Name
> End If
> Next intLoop
>
> At least, I think that's how you'd do it with the AllTables collection: I
> always use the TableDefs collection (and I only have Access 97 available to
> me at present, so I can't test)
>
> Dim dbCurr As DAO.Database
> Dim tdfCurr As DAO.TableDef
> Dim intLoop As Integer
>
> Set dbCurr = CurrentDb()
> For intLoop = (dbCurr.TableDefs.Count - 1) To 0 Step -1
> Set tdfCurr = dbCurr.TableDefs(intLoop)
> If tdfCurr.Name Like "*_ImportError*" Then
> DoCmd.DeleteObject acTable, tdfCurr.Name
> End If
> Next tbl
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> <aaron.kempf@gmail.com> wrote in message
> news:1117145905.049259.276700@f14g2000cwb.googlegroups.com...
> > Dim tbl As AccessObject
> >
> > For Each tbl In CurrentData.AllTables
> > If tbl.Name Like "*_ImportError*" Then
> > DoCmd.DeleteObject acTable, tbl.Name
> > End If
> > Next tbl
> >
>
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
  Return to Microsoft Communities  Notify me of replies