Search code examples
sql-serverms-accessdts

Can DTS Test for Presence of MS-Access Table

I have an Access database in which I drop the table and then create the table afresh. However, I need to be able to test for the table in case the table gets dropped but not created (i.e. when someone stops the DTS package just after it starts -roll-eyes- ). If I were doing this in the SQL database I would just do:

IF (EXISTS (SELECT * FROM sysobjects WHERE name = 'Table-Name-to-look-for'))
BEGIN
drop table 'Table-Name-to-look-for'
END

But how do I do that for an Access database?

Optional answer: is there a way to have the DTS package ignore the error and just go to the next step rather than checking to see if it exists?

SQL Server 2000

Solution

  • I'm not sure whether you can query the system objects table in an Access database from a DTS package.

    If that doesn't work, why not just try doing a SELECT * from the Access table in question and then catch the error if it fails?