1 Reply Latest reply on Mar 9, 2018 7:41 PM by gmichaud-verreault

    Pi ODBC connection string for Microsoft Access

    jlamantia1

      Hello,

       

      I'm trying to get a connection to my PI Data Server using the PI ODBC Driver through MS Access. I'm using a pass-through style query and have been trying many variations of this string in the ODBC Connect Str box: "ODBC;Driver={PI ODBC Driver};Server=<myservername>;Trusted_connection=Yes;Provider_Type=PIOLEDB;Provider_String={Data Source=<myservername>;Integrated Security=SSPI;};". Unfortunately the most progress I've made was getting the driver to ask me to confirm logging in before returning a pretty unhelpful error:

       

      MSAccessPIODBCuninformative.PNG

      I've been refering to the 2016 User Manual's instruction for connection strings but I'm not sure what I'm doing wrong here. Any ideas where I should start would be greatly appreciated.

       

      Thanks,

      Joe

       

      Edit: Additionally, here is the test query I'm trying to run: "select * from [piarchive]..[pisnapshot];".

       

      Edit 2: I should point out that I have been (mostly) successful with bringing in data to Access through a DSN connection using the PI ODBC driver, but this is not the approach we are looking to take.

        • Re: Pi ODBC connection string for Microsoft Access
          gmichaud-verreault

          Could you share why you don't want to use a DSN?

          Could you enable some logging in your connection string?

          Driver={PI ODBC Driver}; Server=myDataAccessServer; Trusted_Connection=Yes; UID=User1; PWD=password1; Provider Type=PIOLEDB; Provider String={Data Source=myPIServer; Integrated Security=SSPI;};Log Level=3;Log File=<fulllogfilepath\file.txt>;

           

          Also, are you experiencing the same behavior if you use the following? That could clarify at what level the connection fails.

          Driver={PI ODBC Driver}; Server=myDataAccessServer; Trusted_Connection=No; UID=User1; PWD=password1; Provider Type=PIOLEDB; Provider String={Data Source=myPIServer; Integrated Security=SSPI;};

           

          Gabriel