BookmarkSubscribeRSS Feed
kconnors
Obsidian | Level 7

Tried to import an accdb Access database in EG, but message indicates that MS Access 2007 or later needs to be installed or Microsoft's 2007 or later Office System (ACE) Driver needs to be installed. The message said to search Microsoft's website for "Microsoft Access Database Engine" to find the download, but all I could find was this page https://www.microsoft.com/en-us/download/details.aspx?id=13255, which shows an ACE driver, but does not include Windows Server 2012 as a supported OS.  Does anyone know of one?  Below is a product status list for SAS on the server. Thanks.

 

proc product_status ; run ;
/*
For Base SAS Software ...
   Custom version information: 9.4_M3
   Image version information: 9.04.01M3P062415
For SAS/STAT ...
   Custom version information: 14.1
For SAS/GRAPH ...
   Custom version information: 9.4_M3
For SAS Integration Technologies ...
   Custom version information: 9.4_M3
For High Performance Suite ...
   Custom version information: 2.2_M4
For SAS/ACCESS Interface to Oracle ...
   Custom version information: 9.4_M3
For SAS/ACCESS Interface to PC Files ...
   Custom version information: 9.4_M3
For SAS/ACCESS Interface to ODBC ...
   Custom version information: 9.4_M3
*/
8 REPLIES 8
Reeza
Super User
How did you try and import it?
kconnors
Obsidian | Level 7

In an EG instance running on the server, File | Import Data then selected the accdb file.  Then I got the error message.

SASKiwi
PROC Star

Have you tried PROC IMPORT and LIBNAME with ACCESS option? Same error? Please post the error.

 

Is the 64-bit ACE driver installed even if its not officially supported?

kconnors
Obsidian | Level 7

Thanks.  Using http://www.codeproject.com/Questions/553016/Microsoft-ACE-OLEDB-plus-pluswhereplusisplusit as a reference, I searched C:\Program Files for ACEOLEDB.DLL and didn't find it and searched C:\Program Files (x86) for ACEOLEDB.DLL and found it.  So it looks like only the 32-bit ACE driver is installed, not the 64-bit one.

 

I tried the following libname statement

libname testacdb "D:\Data\test.accdb" ;

and got the following error:

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.

I tried the following PROC IMPORT code

 

PROC IMPORT OUT=WORK.AB_CAT_adj
           DATATABLE='AB_CAT_adj'
           DBMS=ACCESSCS REPLACE;
    DATABASE="D:\Data\test.accdb";
    USEDATE=YES;
    SCANTIME=NO;
    DBSASLABEL=NONE;
RUN;

and got the following error

 

ERROR: Failed to connect to the Server: .

I have more SAS products installed on my PC than the target user; one of those products is the PC Files Server.  So I tried the following code (but with the real domain in realdomainhere) with the same Access database on my PC's C: drive:

 

%macro import_table () ;
%local client_machine ;
%let client_machine =  %sysfunc(dequote(&_CLIENTMACHINE.)).realdomainhere.COM ;
PROC IMPORT OUT=KEVIN.AB_CAT_adj_5pct
           DATATABLE='AB_CAT_adj_5pct'
           DBMS=ACCESSCS REPLACE;
    DATABASE="C:\test.accdb";
    SERVER="&client_machine";
    PORT=9621;
    DBDSOPTS='READBUFF=300';
RUN;
%mend import_table ;
%import_table ;

and that worked.  However, if I tried it a second time, it didn't work and I had to restart my computer to get it to work again.  I have asked a user who only installed EG and the SAS Add-In for Excel to install the appropriate PC Files Server version based on the version of SAS on the server and then try that code a few times in case it only works once without rebooting.

 

jakarman
Barite | Level 11

The ACE driver is part of MS-office. MS-office doesn't seems to be installed on the server. It could be done ask you MS support staff.
The next thing is downloading that driver and installing it on the server. Ask your MS support staff...
Be carefull on installing the same bitness of the driver as you EGuide bitness. 
Why has it been missed by the guys doing the SAS installation? Requiremetn interaction wiht MS dedicated file formats.... 

---->-- ja karman --<-----
kconnors
Obsidian | Level 7
I am following up with the folks who support the MS products. I was hoping someone might know if that driver is supported on Windows Server 2012 as that OS is not listed on that MS page. Using SAS PC Files Server on each user's machine isn't nearly as easy, but it may have to do. Thanks.
SASKiwi
PROC Star

We are running the 64-bit MS Access Database Engine 2010 (AccessDatabaseEngine_x64.exe) on Windows Server 2012 R2 and that works for us. 

kconnors
Obsidian | Level 7

That's good to know.  If the MS support people don't get back to me soon enough, I will likely try that given that it works for you.  Thanks.

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 3994 views
  • 1 like
  • 4 in conversation