Delete an import Errors table

S

Shane Nation

When I import a text file it sometimes cause the database to create an
Import error table.
Is there a way of deleting this error table if it exists. I have tried to
delete the "Object" (The error table) in the macro I use to import the text
file, but if there has been no error table produced the macro fails.

Please can anyone tell me how to do this. The error table name is always the
same.

Thanks

Shane
 
D

David Lloyd

Shane:

You can try a couple alternative approaches.

One is to reference the error table and then trap the error if it does not
exist. For example:

Function DeleteTable()

Dim db As Database
Dim tdf As TableDef

On Error GoTo Errorhandler

Set db = CurrentDb

Set tdf = db.TableDefs("MyImportErrorsTable")

db.TableDefs.Delete tdf.Name

Function_Exit:

Exit Function

Errorhandler:
'Error 3265 is thrown if the table does not exist
If Err.Number = 3265 Then GoTo Function_Exit
'Additional error handling here.

End Function

A second approach searches the TableDefs collection for the Import Errors
table and deletes it if it exists. For example:

Function DeleteTable()

Dim db As Database
Dim tdf As TableDef

On Error GoTo Errorhandler

Set db = CurrentDb

For Each tdf In db.TableDefs
If tdf.Name = "MyImportErrorsTable" Then
db.TableDefs.Delete tdf.Name
End If
Next tdf

End Function

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


When I import a text file it sometimes cause the database to create an
Import error table.
Is there a way of deleting this error table if it exists. I have tried to
delete the "Object" (The error table) in the macro I use to import the text
file, but if there has been no error table produced the macro fails.

Please can anyone tell me how to do this. The error table name is always the
same.

Thanks

Shane
 
S

Shane Nation

Thank you for that. Where would I enter this text and how would I run it?

Thanks again, so to be so thick
 
D

David Lloyd

Shane:

For the code, you need to go to the Modules section of Access and create a
new module. You can copy and paste the code, although you will need to
change the name of the Import table you wish to delete.

You can run the code from your macro by adding a RunCode action. In the
Function Name property for this action, click the ellipsis (...) and select
the function from the list.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Thank you for that. Where would I enter this text and how would I run it?

Thanks again, so to be so thick
 
S

Shane Nation

Thanks for that. I get an error of "The expression you entered has a
function name that Microsoft Access can't find"

Arugment "DeleteTable () "

If I change the name of the fuction to say DelT() I get the error Visual
Basic compile error "User defined type not defined".


Shane
 
D

Douglas J Steele

What version of Access are you using? David's code uses DAO (Data Access
Objects), which, by default, isn't enabled in Access 2000 or 2002.

With any code module open, select Tools | Referneces from the menu, scroll
through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, select it and close the dialog.
 
D

David Lloyd

Shane:

I was able to recreate that error message by misspelling the name of the
function. Please check that the Function Name property of the RunCode
action is spelled the same as the name of the function in the module.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Thanks for that. I get an error of "The expression you entered has a
function name that Microsoft Access can't find"

Arugment "DeleteTable () "

If I change the name of the fuction to say DelT() I get the error Visual
Basic compile error "User defined type not defined".


Shane
 
S

Shane Nation

OK got that sorted, I have called the fuction DT() and that seems to work.
But I still get the - Basic compile error "User defined type not defined".?

Shane
 
D

Douglas J Steele

It's there in Access 2003.

Just to confirm, you must be in the VB Editor when you look for References
on the Tools menu.
 
Top