topic Re: Connecting to live access tables in SAS Enterprise Guide

Connecting to live access tables

avatar — Thu, 19 Jul 2012 16:04:48 GMT

I have established ODBC connection to Access db thru EG. However not able to query the tables (live)? I tried making copy of the tables (day old ) but still not able to query that table

any suggestions?

Thanks

Re: Connecting to live access tables

Doc_Duke — Thu, 19 Jul 2012 16:42:04 GMT

If someone else has the table locked for update (e.g. exclusive access), SAS won't be able to read it.

BTW, if you have SAS/Access for PC Files licenses, you will get better performance by just specifying the Access database in a libname statement.

Doc Muhlbaier

Duke

Re: Connecting to live access tables

avatar — Thu, 19 Jul 2012 17:29:38 GMT

thank you Duke,. we do have SAS/Access for PC files license. The libname statement does not work since the server is running on AIX box. right now I export the table as Excel file and work with it.

Re: Connecting to live access tables

Doc_Duke — Thu, 19 Jul 2012 19:02:44 GMT

It is also possible that the ODBC connectivity on AIX is not configured properly. Are you able to read the data with some other tool on AIX?

Re: Connecting to live access tables

avatar — Thu, 19 Jul 2012 19:28:56 GMT

I am working with EG4.1 on Windows XP. I have to check with Server Administrator regarding possibilities.

Re: Connecting to live access tables

Doc_Duke — Thu, 19 Jul 2012 19:43:01 GMT

Avatar,

One other possibility just came to mind. If you click Open --> data and then select ODBC, you are connecting to the data directly from EGuide and NOT using AIX. If you are using a LIBNAME statement with the ODBC engine, then you are connecting through AIX. The two different approaches use different ODBC drivers and have different performance characteristics. If you are using the first approach, you could open the data directly (using the PC Files engine) rather than ODBC.

Doc

Re: Connecting to live access tables

avatar — Thu, 19 Jul 2012 19:55:51 GMT

Yes, I am able to open the data in EG using the first approach. I can not query the data, while users are entering the data into the table. I get error msg " cannot access SAS Code". I have to ask users to log off the access db to run reports.is there any other work around to run reports using that table?

Appreciate your help.

Re: Connecting to live access tables

Dorota_Jarosz — Thu, 19 Jul 2012 19:59:36 GMT

Is your MS access or Excel ODBC database defined in Data Sources (ODBC)? Define user DSN in Start --> all Programs --> Administrative Tools --. Data Sources (ODBC).

The path and file name has to match exactly. It is a pain to update paths for production jobs placed in monthly directories.