6 Replies Latest reply on Oct 9, 2013 11:18 PM by LMouxDominguez

    Exposing PI AF Data in MS Access

    jsilvestre

      I had a question around making AF Data available in MS Access and wanted to see if anyone in the vCampus community has had experience with this in the past.  Specifically, I want to make AF data accessible to a variety of end users, therefore I want to make all the data available.  I also want to make this as easy as possible, since many of the end users will be discouraged if there is a large learning curve to get the data into Access.  My question is the following:

       

      Which Data Access Layer would work best here?  MS Access natively supports ODBC connections and Web Services through Data Services.  Unfortunately, AF currently does not support ODBC (at least not until Q2 2014), therefore I’m left with Web Services.  Are there any other alternatives?     

        • Re: Exposing PI AF Data in MS Access

          Couldn't a linked server via SQL and PI OLEDB be an alternative? Then you could bring PI Data to Access via SQL Server Native client (ODBC) or one such similar ODBC driver.

            • Re: Exposing PI AF Data in MS Access
              jsilvestre

              I did consider this approach, but I'm not sure you can create an ODBC connection to a Linked Server.  At least I haven't found anything thus far that has made this possible.  

                • Re: Exposing PI AF Data in MS Access

                  You wouldn't create it to the linked server per se. Rather you would create a set of views and link (i.e., "Access' link table") to them.

                   

                  I just did a quick test on a SQL server which already has all the nice Kerberos delegation stuff setup.

                   

                   

                   

                  Below is the view I created. It relies on an linked server which is a totally foreign system (this one is Informix, in your case it would be OLEDB Enterprise)

                   
                  create view luistTesting.vCampusLinkedServer as
                  select top 10
                  *
                  from openquery(cisco_am,
                  '
                       select first 50
                            r.resourceName,
                            c.*
                       from Resource r
                       inner join AgentConnectionDetail c
                       on r.resourceID = c.resourceID
                       where r.resourceName like ''%Moux%''
                       order by startDateTime desc
                  ')
                  

                   I created a DSN pointing to the correct database, selected the appropriate view and then it's normal Access from there.

                   

                  Here is a picture of me using this view which relies on a linked server (just like you would) from Access 2013

                   

                  8203.meUsingLinkedServerViaAccess2013.png

                   

                   

                    • Re: Exposing PI AF Data in MS Access

                      Here is the example with OLEDB Enterprise. I couldn't get my regular cross apply TransposeInterpolateRange query to be parsed correctly (SQL didn't like the <,> brackets and I couldn't figure out how to escape them) but few joins would do as example -and then you could probably just create the objects directly rather than using the cross apply

                       

                      In any case, hopefully this gives you a way to get it there.

                       

                      6874.meUsingLinkedServerViaAccess2013_5F00_OLEDB.png

                • Re: Exposing PI AF Data in MS Access

                  When you say AF data, I assume you mean PI tag values via AF references.  You should be using AF OLEDB Enterprise - you can start with the OLEDB Commander examples.