Error trapping for presence of table within an Access DB accessed through ODBC

[pharmKitty]

Does anyone know how I can check for the presence of particular table within an Access DB from within my SAS code?  If you know of any other error checking I should do ivolving working with an Accdess Database in SAS, I would appreciate knowing that too.

THanks in advance for your time and help.

[PGStats]

Assuming that your ODBC library has libname="T", you can check for the presence of table "Test" like this :

proc sql noprint;
select sum(upcase(memname)="TEST") into :testPresent
from dictionary.tables
where libname="T"; /* use uppercase */

%put Present: &testPresent;

dictionary.tables is dynamic, i.e. it reflects the current state of the database in real time.

PG

[pharmKitty]

PG, Thank you very much for your fast reply.  I will try your solution Monday when I get to work. Then I will mark correct/helpful.  My home SAS has expired 😞

[Patrick · Accepted solution]

Below code is what SAS DI Studio generates for such a test:

%let etls_tableExist = %eval(%sysfunc(exist(<libref>.<table>, DATA)) or   %sysfunc(exist(<libref>.<table>, VIEW)));

&etls_tableExist is '0' if the table/view does not exist and '1' if the table/view exists.

The pre-condition for this code to work is of course that you've issue a libname statement using the ODBC engine.

If you're just using SQL pass-through and there is no libname defined at all then you would also have to write some SQL pass-through code querying the dictionary tables on the DB side to determine if a table exists or not.

[pharmKitty]

Patrick, thanks very much for your reply.  I do want to test whether the table exists to determine what will happen next so I really like this answer a lot.  I can't wait to get to work tomorrow to try this out.  Thanks also for such a detailed explanation!  I will mark as helpful/correct tomorrow after I try both these solutions out.