topic Re: 64-bit and 32-bit ODBC issue in Microsoft Integration with SAS

64-bit and 32-bit ODBC issue

model_coder — Fri, 18 Jun 2010 14:05:17 GMT

I am working on a 64-bit machine with 32-bit Microsoft office and 32-bit odbc drivers. I used 32-bit version of ODBC Data Source Administrator to create by DSN to connect to a local copy of Microsoft Access database. When I run following code, I get an error:

libname msaccess odbc dsn=myDSN;

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source
name not found and no default driver specified
ERROR: Error in the LIBNAME statement.

Is there a way I can specify in libname that i want to use 32-bit odbc? We also don't have Microsoft Access PC/File access to use proc import. I will really appreciate any feedback.

Re: 64-bit and 32-bit ODBC issue

UMAnalyst — Mon, 26 Jul 2010 14:46:34 GMT

I'm not sure if "MS Access PC/File access" which you referred to is the same as the SAS PC Files Server. If it isn't and you do have SAS PC Files Server installed, perhaps this will help.

back-info: I was recently upgraded to Win 64-bit and had success connecting to 32-bit MS Access DBs (.mdb) by:

1) Starting the SAS PC Files Server, and

2) using the LIB statement:
libname pcfiles server= port= path=full file path\dbname.mdb

To disassociate the reference use: libname CLEAR;

If this helped and you want to return the favor, please tell me how to make this (or some other method) work for 32-bit MS Access 2007 DBs with .accdb extension.

To this point, I have found comfort in declaring it impossible.

Good Luck

Message was edited by: UM Analyst

Message was edited by: UM Analyst

Message was edited by: UM Analyst

Re: 64-bit and 32-bit ODBC issue

deleted_user — Tue, 24 Aug 2010 16:29:06 GMT

We are having the same issue. We have SAS 64-Bit installed on a Win2k8R2 x64 machine. When trying to import data from any MS file (Excel, Access, etc) there is the "ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" issue.

Here's what I found out.

It looks like SAS polls the default ODBC driver locations, which is fine for an x86 environment. However, in an x64 environment, the default location is for the x64 drivers in which MS hasn't released 64 bit ODBC drivers for their office programs.

The x86 ODBC drivers do exist and are located on the server. I need to run the x86 ODBC manager to find and see them, but they are there. How can I change SAS to poll an alternative or multiple locations when opening an ODBC stream?

EDIT: We do NOT have SAS PC Files Server installed. Message was edited by: JimmyITCS

Re: 64-bit and 32-bit ODBC issue

CaseySmith — Tue, 24 Aug 2010 19:17:03 GMT

See this usage note:
http://support.sas.com/kb/38/779.html

Re: 64-bit and 32-bit ODBC issue

deleted_user — Tue, 24 Aug 2010 19:24:44 GMT

PERFECT! Now, why is this information so hard to find?!

Re: 64-bit and 32-bit ODBC issue

UMAnalyst — Thu, 26 Aug 2010 14:43:51 GMT

issue resolved after installing 32-bit and updating the 64-bit version. Yes, I have both versions installed

64-Bit: AUTOMATIC SYSVLONG4 9.02.02M0P01152009

32-Bit: AUTOMATIC SYSVLONG4 9.02.02M2P09012009

My code from the previous post now functions with no errors, so I now can move on with my life.

good luck

Re: 64-bit and 32-bit ODBC issue

model_coder — Tue, 30 Nov 2010 01:06:34 GMT

We have SAS/Access interface to PC files installed on our machines. But I don't see the PC File server component. Do we need a separate license for this component?