Import/Update Access Database linked to a SharePoint List using SAS

bhuiyaas — Wed, 30 Dec 2020 06:31:02 GMT

Hi,

I am trying to import, update a table in Access database (64 bit) using SAS (SAS Enterprise Guide, Version: 7.12 (64 bit)). I have been trying to identify the problem and resolution for a few days now and based on all the searches I have done in the last few days, I think I have narrowed down the problem and looking for some help to resolve this last bit. Although there are similar problems posted and resolutions discussed, none of them worked for me or the problem I have (or the solution I am looking for) is slightly different from those.

I am also new to SAS as well as SharePoint so it is possible that I am making a mistake out of my limited knowledge on SAS coding or SAS/ SharePoint architecture in general.

Below is an overview of the systems that are connected to this:

1. I have a SharePoint site. I have been given full permission to a particular page where I created a List that I am trying to work with.

2. I have created an access database and established link between the Access and SharePoint List. So, if I enter a value in the (Access) table, the List gets updated instantly, so no issue with regards to the connection (I think).

3. I have SAS EG that can connect (create, update, import etc. ) with other Access DB files (not linked with SharePoint) located in the same folder.

The Access DB file is located in a Network Drive location mapped as my 'Documents' folder.(Meaning, in window explorer, if I click on the 'Documents', I am actually referring to the location in the shared drive so everything I have in my pc have a back up in that drive automatically)

The things that I have tried so far:

PROC HTTP:

Initially, I tried with the PROC HTTP procedure (and was not considering communicating via ACCESS) only to learn afterwards that my company wont allow SAS communication through HTTP protocol because of security. So this is not an option at the moment.

PROC IMPORT:

Next I tried with the PROC IMPORT procedure:

PROC IMPORT OUT= outdata
DATATABLE='tablename'
DBMS=ACCESS REPLACE;
DATABASE="full path to the access file starting with \\...";
USER= admin; 
PASSWORD=''; 
USEDATE=YES;
SCANTIME=NO;
DBSASLABEL=NONE;

proc print;
RUN;

Error received (with warnings for all of the columns in the table).

WARNING: Failed to scan text length or time type for column A.
WARNING: Failed to scan text length or time type for column B.
WARNING: Failed to scan text length or time type for column C.
WARNING: Failed to scan text length or time type for column D.
ERROR: Open cursor: Cannot connect to the SharePoint site 'https://site/subsite'. Try again later.

My understanding from this is that SAS was able to establish a connection to the Access DB (since it correctly identified those column names but failed to connect to the site). So I tried to use my Windows credentials as user/pass info. The same username and password is used throughout my PC, SAS EG application, SharePoint site.

PROC IMPORT OUT= outdata
DATATABLE='tablename'
DBMS=ACCESS REPLACE;
DATABASE="path to the access db starting with \\...";
USER= 'my username'; 
PASSWORD='my password'; 
USEDATE=YES;
SCANTIME=NO;
DBSASLABEL=NONE;

proc print;
RUN;

Error received.

ERROR: Connect: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.
ERROR: Error in the LIBNAME statement.

I do not have access to the folder in which my workgroup information file would be located but I am quite sure that file is not causing the issue (Guessing based on some of the other procedures that I followed and changes I made to the code or simply the user/pass information its expecting may be different). I have also changed the settings in my Access to address the other part of the error message and I am the only user of this Access file.

Also tried:

PROC IMPORT DBMS=ACCESSCS DATATABLE='tablename'
     OUT=WORK.outtable REPLACE;
     DATABASE='full path starting with \\....';
	 USER= "my user"; 
 	 PASSWORD="my pass";
RUN;

Error:

ERROR: CLI open cursor error: [Microsoft][ODBC Microsoft Access Driver] Cannot connect to the SharePoint site 
       'https://site/subsite'. Try again later.

ODBC:

I created ODBC (using user DSN as I don't have admin rights to create a System DSN). One of the reason I would have preferred to create the System DSN is mentioned in this article: https://docs.microsoft.com/en-us/troubleshoot/sql/connect/odbc-tool-displays-32-bit-64-bit

And I encountered same problem mentioned, but the workaround did not make sense to me. However, I have tried to rename my ODBC DSN with an extension '_64' and got the same error message.

Based on this link: https://support.sas.com/techsup/technote/accessing-microsoft-sql-server-from-sas.pdf

I decided to take advantage of not having to specify the connection information myself (using code). So I used the following code

Try 1:

libname odbcname odbc prompt;

Error:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver 
       specified

Since that did not work, I wanted to see of I can have those DSN info, User Info and Password info if I import the table using ODBC wizard which worked (the table was loaded in SAS). After the table was loaded, I went to properties and copied the information into my libname as below.

Try 2:

libname odbcname odbc required=
     "Driver={Microsoft Access Driver (*.mdb, *.accdb)};
    DBQ=full path starting with \\...
;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;
Uid=admin;pwd=''";

This code did not return an error. However, the Log shows that no information was stored in my libref.

NOTE: Libref odbcname was successfully assigned as follows: 
      Engine:        ODBC 
      Physical Name:

Try 3:

LIBNAME ACCESS ODBC DSN='odbcname' user=username pw="my password";

Error:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver 
       specified

Wizard Options:

When I am using the import wizard:

If I select WORK as the destination folder, it gives me an error:

WARNING: Failed to scan text length or time type for column A.
WARNING: Failed to scan text length or time type for column B.
WARNING: Failed to scan text length or time type for column C.
WARNING: Failed to scan text length or time type for column D.
ERROR: Open cursor: Cannot connect to the SharePoint site 'https://site/subsite'. Try again later.

If I select SASHELP as the destination folder, it gives me an error:

WARNING: Failed to scan text length or time type for column A.
WARNING: Failed to scan text length or time type for column B.
WARNING: Failed to scan text length or time type for column C.
WARNING: Failed to scan text length or time type for column D.
ERROR: User does not have appropriate authorization level for library SASHELP.

In both cases, I have checked "Import the data using ...."

However, when I left the boxes unchecked and simply clicked Finish, my data was imported in my 'Output Data' window. However, no code was generated.

[Part of the reason I was checking that box is to see where in my SAS/ACCESS to PC Files code I am making some mistake]

Pass Through:

Tried to follow: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=sqlproc&docsetTarget=p00m0fdlbdu781n1jxsn4umlqyok.htm&locale=en

proc sql outobs=15;
   connect to ACCESS as ora2 (path='full path starting with \\....' user="user name" password="my password");
   select * from connection to ora2 (select * from "tablename");
   disconnect from ora2;
quit;

Error:

ERROR: Connect: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

SAS PC FILES:

Finally, I tried with

LIBNAME db PCFILES PATH='path to the file starting with //....' SERVER=localhost SSPI=yes ;
   proc datasets library=db;

It gave me an output(correctly identifying the tables in the database):

However, whenever, I tried to run the below code for one of the table

proc print data=db.tablename;
   run;

Gave me the error:

ERROR: CLI open cursor error: [Microsoft][ODBC Microsoft Access Driver] Cannot connect to the SharePoint site 
       'https://site/subsite'. Try again later.

At this point, I am really not sure if there is any other way to import/ update this data. Is there any SharePoint related information that I should be providing in my code. If yes, what information is needed and how do I provide them?

Can someone kindly advise?

Kind Regards

Re: Import/Update Access Database linked to a SharePoint List using SAS

ChrisNZ — Thu, 07 Jan 2021 08:18:20 GMT

Congratulations on a good research and well documented post.

It seems that reading the Access database triggers an http access to SharePoint. When you access the database from your PC, this link to SharePoint is not an issue. When you access the database from SAS, the link cannot work due to the restrictions placed on the SAS server.

Is that a fair description?

topic Import/Update Access Database linked to a SharePoint List using SAS in SAS Programming

Import/Update Access Database linked to a SharePoint List using SAS

Re: Import/Update Access Database linked to a SharePoint List using SAS