11-06-2015 11:45 AM
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 */
11-06-2015 12:27 PM
In an EG instance running on the server, File | Import Data then selected the accdb file. Then I got the error message.
11-06-2015 05:56 PM
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?
11-10-2015 11:02 PM
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.
11-07-2015 07:50 AM
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....
11-10-2015 11:29 PM
11-11-2015 02:13 PM - edited 11-11-2015 02:16 PM
We are running the 64-bit MS Access Database Engine 2010 (AccessDatabaseEngine_x64.exe) on Windows Server 2012 R2 and that works for us.
11-12-2015 03:39 PM
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.