To add a bit more information to this, here is what I'm running into and perhaps someone can help me find the best solution. I've tried to live-link to each of the separate databases, but I can't seem to figure out how to join them all together within Tableau for a seamless data set. Just for giggles, unsure how Tableau would handle such a task, I've also tried live-linking to the database that simply contains the connections to each separate database and consolidates them in a single join query, but it appears Tableau can't live-link to multiple tables in a single Access database that exceed Access' 2gb file limit (even though they are simply links).
I've gone back to looking at importing the first access DB and adding the rest to the extract using "add data from file", but I'm unsure how I will handle the single access file that gets updated with new data each week. I saw an option to "refresh", but got an ominous warning that doing so will replace all of the data. Is this option going to actually re-create the extract from each of the files all over again, each time I use it? How will it handle this updating file being renamed once the quarter is finished, at which point I begin a new updating Access file?
I apologize if these are terribly ignorant questions, but I am having a difficult time finding information that addresses this sort of situation. If there are already resources that cover this problem, please point me in the right direction. Thanks!
I think the MS Jet data engine is your limiting factor, not Tableau. You probably don't want to hear this (or you probably already know this ) but you'd be far better off using a db application that can actually handle large amounts of data. Access is good for small databases but becomes painfully weak when you start working with large data sets like where you're heading.
Have you tried data blending? It's in the instruction manual but basically you plop EACH table into the Data pane and they are connected (you can think of it as "joined") by one or more common fields. Since it sounds like you have each required table in its own Access db this should work for you, definitely since each Access db is a separate connection. I did a little bit of data blending a long time ago so I don't remember the actual steps but read about the procedure and you'll get it.
If you solve your issue please let us know! Then we all learn something and it can help someone else