Can DTS Test for Presence of MS-Access Table

Question

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

Answer 1

Microsoft Access has a system table called MSysObjects that contains a list of all database objects, including tables. Table objects have Type 1, 4 and 6.

It is important to reference the type:

... Where Name='TableName' And Type In (1,4,6)

Otherwise, what is returned could be a some object other than a table.

Answer 2

Try the same T-SQL, but in MS ACCESS the sys objects table is called: MSysObjects.

Try this:

SELECT * FROM MSysObjects WHERE Name = 'your_table';

and see if it works from there.

You can take a look at these tables if you go to Tools -> Options -> View (a tab) -> and check Hidden Objects, System Objects. So you can see both. If you open the table, you should see your table names, queries, etc. Do not change this manually or the DB could panic :)

Martin.

P.D.: Your If Exists should also check of object type:

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[Your_Table_Name]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

Answer 3

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?