topic Re: Proc Export to ACCESS (Office 365) fails to connect to server in SAS Programming

Proc Export to ACCESS (Office 365) fails to connect to server

gdaymte — Fri, 14 Feb 2020 15:40:26 GMT

I am using SAS Studio (x64) and Microsoft Access (Offcie 365, x64). I would like to export a SAS dataset to Access. Beacuase I am using SAS Studio, I am unable to point to a specific drive letter for the DATABASE. So I need to point to \\server-name\file.accdb.

PROC EXPORT DATA = DCR.A_EXTRACT
OUTTABLE='A_EXTRACT'
DBMS=ACCESSCS REPLACE;
DATABASE= "\\server-name\file.ACCDB";
RUN;

I keep getting the error of "ERROR: Failed to connect to the Server: .". This works for exporting to CSV or XLSX, but it doesn't seem to work for ACCESS. Is there an option that I need to add to my code?

Re: Proc Export to ACCESS (Office 365) fails to connect to server

Reeza — Fri, 14 Feb 2020 16:04:19 GMT

If both are 64 bit you shouldn't need ACCESSCS, CS is for when one is 32bit and one is 64bit.

Have you tried DBMS=ACCESS or an ODBC connection?

@gdaymte wrote:

I am using SAS Studio (x64) and Microsoft Access (Offcie 365, x64). I would like to export a SAS dataset to Access. Beacuase I am using SAS Studio, I am unable to point to a specific drive letter for the DATABASE. So I need to point to \\server-name\file.accdb.
PROC EXPORT DATA = DCR.A_EXTRACT
OUTTABLE='A_EXTRACT'
DBMS=ACCESSCS REPLACE;
DATABASE= "\\server-name\file.ACCDB";
RUN;
I keep getting the error of "ERROR: Failed to connect to the Server: .". This works for exporting to CSV or XLSX, but it doesn't seem to work for ACCESS. Is there an option that I need to add to my code?

Re: Proc Export to ACCESS (Office 365) fails to connect to server

SASKiwi — Fri, 14 Feb 2020 22:06:46 GMT

ACCESSCS requires that you have the SAS PC File Server installed and running as a Windows service, preferably on the Windows server you are writing to. From the error you are getting it looks like SAS PCFS isn't running.

Re: Proc Export to ACCESS (Office 365) fails to connect to server

gdaymte — Fri, 14 Feb 2020 22:18:00 GMT

That simple change worked. Thanks.

Re: Proc Export to ACCESS (Office 365) fails to connect to server

ketu497 — Mon, 17 Jan 2022 19:21:16 GMT

We recently upgraded to Access Office365 and I tried the solution

proc export data=sashelp.class outtable="classkp"
DBMS=ACCESS REPLACE;
DATABASE = "location\classkp.mdb";

run;

however still getting the following error. Is this any other solution to this issue.

29 run;

Connection Failed. See log for details.
NOTE: The SAS System stopped processing this step because of errors.

Re: Proc Export to ACCESS (Office 365) fails to connect to server

SASKiwi — Tue, 18 Jan 2022 00:21:03 GMT

Was it working prior to the upgrade? What bitness of Office 365 are you using 32-bit or 64-bit?

Are you running this on a remote SAS server session or on your PC?

Re: Proc Export to ACCESS (Office 365) fails to connect to server

ketu497 — Tue, 18 Jan 2022 01:01:52 GMT

Yes it was working prior to the upgrade with the

DBMS=ACCESSCS and then based on the given solution, I changed it to DBMS=ACCESS.

I am running this on the SAS Enterprise guide my PC.

Re: Proc Export to ACCESS (Office 365) fails to connect to server

SASKiwi — Tue, 18 Jan 2022 01:45:21 GMT

Is EG connecting to a remote SAS server or to SAS on your PC? If you use a connection profile you are using remote SAS.

If your SAS is remote and runs on Unix you have to keep using ACCESSCS as MS Access doesn't run on Unix. You should also talk to your SAS administrator about your issue as they will be more familiar with your SAS installation.

Re: Proc Export to ACCESS (Office 365) fails to connect to server

ketu497 — Tue, 18 Jan 2022 13:39:53 GMT

@SASKiwi I verified and our EG connecting to a remote SAS server and SAS remote is not running on Unix so then should this not work. Is there anything the SAS administrator have to do after the Office 365 upgrade on their side.