[SOLVED] ODBC error when trying to connect MS Excel to SPSS file on fileserver

ODBC error when trying to connect MS Excel to SPSS file on fileserver

I want to load SPSS Data into Excel using an ODBC connection. I am successfully connecting Excel to a local SPSS file using the IBM SPSS Statistics Data Drivers. But when I try the same with a copy of that file that I have placed on the corporate file server the connection fails.

What is going wrong? do i need to specify the UNC path in a different way?

here is the connection string that I use in Excel for the local SPSS file which works great

= Odbc.DataSource( 
        "DRIVER=IBM SPSS Statistics 28 Data File Driver - Service Client(x64);
        SDSN=SAVDB;
        HST=localhost;
        PRT=18886;
        CP_CONNECT_STRING=c:\folder\spssfile.sav;
        CP_UserMissingIsNull=0",
        [HierarchicalNavigation=true]
)

Here is the same connection string - the only difference being, that I point to a file on the fileserver.

= Odbc.DataSource( 
        "DRIVER=IBM SPSS Statistics 28 Data File Driver - Service Client(x64);
        SDSN=SAVDB;
        HST=localhost;
        PRT=18886;
        CP_CONNECT_STRING=\\fileserver\folder\spssfile.sav;
        CP_UserMissingIsNull=0",
        [HierarchicalNavigation=true]
)

here is the error message I am receiving

DataSource.Error: ODBC: ERROR [HY000] [IBM][ODBC IBMSPSSOEM driver][OpenAccess SDK SQL Engine]File:<\\fileserver\folder\spssfile.sav> could not be opened

Solution

here is the solution: in order to open SAV files located on a file server from your local system the SPSS Statistics data file drivers not to be installed also on the file server. and the server IP or name needs to be defined for the HST parameter instead of localhost.