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

[gdaymte]

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?

[Reeza · Accepted solution]

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?

---

[gdaymte]

That simple change worked. Thanks.

[ketu497]

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.

[SASKiwi]

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? 

[ketu497]

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.

[SASKiwi]

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.

[ketu497]

@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. 

[SASKiwi]

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.