Problem with DoCMD.DeleteObject or IsObject

[P5C768]

I'm trying to determine my error in this coding. I am looking to check if a table exists, if it does, delete it and import a new table that has the same name as the one I checked for (in this case, check for CPPS, delete it if it exists and add a new table named CPPS from a form value using import specs).



It appears that the isobject is not working, because it tries to execute the code even if there is no CPPS table. It also appears to duplicate the imported table (i.e. if the CPPS table has 100 records, it creates a table with 200 records, each one duplicated once).

Here is the code:

On Error GoTo CPPSnxt
If IsObject(CurrentDb.TableDefs("CPPS")) = True Then
DoCmd.DeleteObject acTable, "CPPS"
End If
CPPSnxt:
DoCmd.TransferText acImportDelim, "CPPS SEPS", "CPPS", CPPStxt.Value
Resume Next
End If

[ajetrumpet]

p5,

an easy way to check this is to check the error number. example:

strname = currentdb.tabledefs("tablename").name

if err.number=3265 then
   'TABLE DOESN'T EXIST
else
   'table is here.  delete it!
end if

if the table is present, the name prop will be passed to the variable, and the err.number will remain at 0, where it always starts in a procedure.

[P5C768]

I tried this code:

        If Err.Number = 3265 Then
            GoTo CPPSnxt
        Else
            DoCmd.DeleteObject acTable, "CPPS"
        End If
CPPSnxt:    DoCmd.TransferText acImportDelim, "CPPS SEPS", "CPPS", CPPStxt.Value

But now I'm getting error 7874 (can't find CPPS object) when I execute the code.

[ajetrumpet]

I tried this code:

        If Err.Number = 3265 Then
            GoTo CPPSnxt
        Else
            DoCmd.DeleteObject acTable, "CPPS"
        End If
CPPSnxt:    DoCmd.TransferText acImportDelim, "CPPS SEPS", "CPPS", CPPStxt.Value

But now I'm getting error 7874 (can't find CPPS object) when I execute the code.

probably because you're not using my code. different syntax yields different errors, right? YES.

concept is the same. the table is probably not there.

[P5C768]

Using your code, I still get error 3265 when strname is set, it never goes to the if statement to get the err.number.

This seems to work:

 
On Error GoTo CPPSnxt
    IsObject (CurrentDb.tabledefs("CPPS"))
        
CPPSnxt:    If Err.Number = 3265 Then
                DoCmd.TransferText acImportDelim, "CPPS SEPS", "CPPS", CPPStxt.Value
            Else
                DoCmd.DeleteObject acTable, "CPPS"
                DoCmd.TransferText acImportDelim, "CPPS SEPS", "CPPS", CPPStxt.Value
            End If

[ajetrumpet]

you have to set your options P5. BREAK ON UNHANDLED ERRORS is what you have to have set! good luck!

[P5C768]

It looks like the actual problem here was that I was trying to use On Error Goto <label> four times in the same procedure. Researching around a little more, I found that VBA doesn't let you do this. I solved my problem both by using the code above and by separating my code in to four separate procedures and than calling each of them in an on click event. Thanks for the help!