topic Re: Able to import from Excel but not Access in SAS Procedures

Able to import from Excel but not Access

keh8 — Wed, 30 Oct 2019 20:03:39 GMT

I recently updated my computer to Windows 10, and have Microsoft Office 64-bit and SAS 64-bit (9.04.01). I have no problem running an existing proc import statement with Excel, but my existing import code with Access is no longer working. If I try to run this:

proc import out=codebook
datatable="Matrix Table"
dbms=ACCESS REPLACE;
database = "C:\Users\keh\Documents\Matrix.accdb";
run;

I receive the error:

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
ERROR: Connection Failed. See log for details.

This error typically occurs because of a mismatch between SAS and Office "bits", 64 vs. 32, and the solution is usually to obtain the PC Files Server. However, if the 'bits' are the same you aren't supposed to need the PC Files Server. (Since I am able to import Excel files, that would suggest the problem is not a general incompatibility with Office.)

Another possibility in this situation is that you should download Access Runtime in Microsoft. I tried this, but got an error when trying to install it that the version of Office I have installed doesn't work with Runtime, and that I should delete and reinstall Office in the Click-to-Run version if I want to download Runtime. If there is a simpler solution I would prefer not to uninstall and reinstall a different version of Office.

Does anyone have insights? Thank you!

Re: Able to import from Excel but not Access

SASKiwi — Thu, 31 Oct 2019 02:33:27 GMT

Did you update MS Office as part of your upgrade? If so to which version? This SAS note may be relevant:

http://support.sas.com/kb/60/321.html

BTW, I don't believe SAS 9.4M1 is officially supported on Windows 10. You need to be on 9.4M3 or later if my memory serves me correctly.

Re: Able to import from Excel but not Access

keh8 — Thu, 31 Oct 2019 15:37:25 GMT

Thank you. Yes, the new version is Microsoft Office 365 ProPlus. I tried the 'Runtime' suggestion in that link, but I got an error saying Runtime is incompatible with the version of Office I have.

Re: Able to import from Excel but not Access

SASKiwi — Thu, 31 Oct 2019 19:11:55 GMT

I suggest you track your problem to Tech Support. There may be some issues running on an old maintenance level.

Re: Able to import from Excel but not Access

keh8 — Fri, 01 Nov 2019 17:30:50 GMT

The solution to this is to download the driver that is recommended, Microsoft Office *2013* Runtime. I have Office 365 so I figured I'd need the newer version of the driver, but that was incorrect and didn't work. Thanks to SAS Tech Support for helping me with this!