2 Replies Latest reply on Apr 9, 2016 9:53 AM by Jonathan Drummey

    Using Access data source - 255 column limit - any updates?

    Caitlyn Conley

      I've got a Tableau project which sits on an Access database.  The Access database has lots of "wide, shallow" tables - many columns, not too many rows.  When I  import all the tables into Tableau and try to do simple joins to combine them all, I run into a "too many variables" error, which I've discovered is due to the outdated limitations of the MS Jet driver, which - if I understand correctly - is still a problem because Tableau is using Access to run the table joins and Access has a 255 column limit.

       

      My question is this:  Is there any work being done with Tableau to remove this dependence on the Jet driver?  Could the joins be executed in Tableau instead?  While I can't believe Microsoft still has 255 column limits in 2016, I know there's no point in asking Microsoft to change.

       

      I built my original database in a 32-bit version of Access 2010.  I rebuilt it using a 64-bit version of Access 2013 in the hopes that maybe Microsoft had overcome this limitation - didn't work.

       

      For various reasons, I don't want to use extracts in Tableau, so now I'm looking at creating a sql server express database and sitting Tableau on that, but that's a lot of extra work (and my IT department hates this solution).  Would love to keep the database in Access if Tableau has a work-around (not extracts) or anyone can suggest another way to approach.

        • 1. Re: Using Access data source - 255 column limit - any updates?
          Dan Cory

          Sorry, this is entirely a limitation of Microsoft Access.

           

          You can use any other database, or text or Excel files, and you won't have this problem.

           

          Dan

          • 2. Re: Using Access data source - 255 column limit - any updates?
            Jonathan Drummey

            Hi Caitlyn,

             

            I've used Access *a lot* with Tableau and Dan's right, the limitations are all about MS Access. It's fundamentally built as a database for workgroups or small departments and doesn't have the capabilities that we might want. We can't have more than 255 columns in a table nor more than 255 columns in a query in Access. Also Access has some other size limitations (# of queries & tables, number of records, total size of data, complexity of query) and is much more sensitive to the quality of the network connection than other RDBMS's I've used. In my case for Access data sources I almost entirely use Tableau data extracts because I find they are up to 100x faster than live connections.

             

            I've been (slowly) moving our Access DBs onto SQL Server (both Express and a full install) and even with the added work I am much happier. There are no limitations on # of columns, much improved performance so live connections are more feasible, a lot more SQL features, views, stored procedures, improved security (which makes my IT department very happy), profiling, the list goes on and on.

             

            Jonathan