Desktop productivity for business analysts and programmers

Connecting to live access tables

Reply
Contributor
Posts: 66

Connecting to live access tables

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

Trusted Advisor
Posts: 2,114

Re: Connecting to live access tables

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

Contributor
Posts: 66

Re: Connecting to live access tables

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.

Trusted Advisor
Posts: 2,114

Re: Connecting to live access tables

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?

Contributor
Posts: 66

Re: Connecting to live access tables

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

Trusted Advisor
Posts: 2,114

Re: Connecting to live access tables

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

Contributor
Posts: 66

Re: Connecting to live access tables

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.

Contributor
Posts: 66

Re: Connecting to live access tables

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.

Ask a Question
Discussion stats
  • 7 replies
  • 242 views
  • 0 likes
  • 3 in conversation