SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

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.


Accepted Solutions
Solution
‎07-22-2012 06:05 AM
Respected Advisor
Posts: 4,131

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

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.

View solution in original post


All Replies
Respected Advisor
Posts: 4,804

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

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

PG
New Contributor
Posts: 3

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

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 :-(

Solution
‎07-22-2012 06:05 AM
Respected Advisor
Posts: 4,131

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

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.

New Contributor
Posts: 3

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

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 561 views
  • 3 likes
  • 3 in conversation