1 Reply Latest reply on Jan 1, 2013 6:35 AM by Russell Christopher

    Access queries with UDFs not appearing as possible data connections

    Neil Berkowitz

      When I try to connect to any Access 2007 data source, it seems that any query that uses a UDF does not show up among the list of tables. When I change the query definition to exclude any UDFs the query does appear among the choices in the connection dialog box. When I enter try to set up the connection as custom sql instead of as a single connection, I get a message about a syntax error in the From clause. But I can find no such errors.


      Any thoughts on what is happening?


      The query contains process step times and the UDF is essentially a CASE statement  (Access sql doesn't permit CASE with the sql statement itself) that determines which step duration to bring in to that row's data. While I can do the case statement in Tableau I would rather not do so.


      Thanks for your help.


      Neil Berkowitz

      Seattle Children's Research Intitute

        • 1. Re: Access queries with UDFs not appearing as possible data connections
          Russell Christopher

          Hey Neil -


          I'm no longer familiar with the internals of JET (the database engine for MS Access), but what you're seeing doesn't really surprise me.


          I suspect that that unless you're IN a tool that directly supports VBA/VB/C#/whatever and knows HOW to reference code stashed in a module, then the function that you've written (let's say "myFunction()" ) and the reference TO that function is simply seen as a syntax error since there is no SQL command called myFunction()


          In your shoes, I'd consider not using the UDF and simply re-writing the same logic inside Tableau, since we support CASE in our expression language.


          This probably isn't exactly the answer you're looking for, but I want to keep you sane


          Edit: Just for kicks I reproduced this behavior not only in Tableau, but in Excel and other tools. For example, the "Get Data from Access" feature of Excel hides queries which utilize UDFs but shows "normal" ones. This tells me the Microsoft JET database driver is returning a list of tables/views that it feels is appropriate for consumption by outside clients.


          Message was edited by: Russell Christopher