How do I drop a table if it exists? 2

[crystalreporting]

I've tried the macro 'deleteobject' function, but it returns an error message if the table is not present in the database. I've tried SQL e.g.

IF EXISTS( SELECT *
FROM MSYSOBJECTS
WHERE (((MSYSOBJECTS.Name)='CREXPORT')))
DROP TABLE CREXPORT

But this gives me an error if I use it in a SQL query, or as a macro using RunSQL.

The table is an export from Crystal Reports and I'd like to have it deleted if it exists when the main form is opened.

Thanks - Peter.

Peter Shirley

http://www.srhconsulting.com

 

[rjoubert]

Try this code in the OnOpen event of your main form...

If IsObject(CurrentDb.TableDefs("CREXPORT")) Then
    DoCmd.RunSQL "DROP TABLE CREXPORT"
End If

 

[crystalreporting]

Thanks - that works when the CREXPORT table is present, however it errors with 'Run-time error 3265, item not found in this collection' when the table is not present.

Peter Shirley

http://www.srhconsulting.com

 

[PHV]

VBA code closest to your SQL:

If Not IsNull(DLookUp("Name", "MSYSOBJECTS", "Name='CREXPORT'")) Then
  DoCmd.RunSQL "DROP TABLE CREXPORT"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[crystalreporting]

That works perfectly PHV - thanks a bunch!

Peter Shirley

http://www.srhconsulting.com