We do a lot of TFS analysis and reporting in Excel, using PowerPivot to map to 'executive friendly' terms (like dates vs iterations) and they are quite please with all that. However, we also now have several TFS 'projects' (instances, versions, whathaveyou), and an Excel workbook cannot link to more than one project at a time. Also as the projects grow larger and larger, my machine struggles to process. I'd like to put the mapping tables AND the TFS data from several instances into an Access database.
Question 1
Is there any way to link Access to TFS? If not, I'm fine having TFS data in Excel and linking to Excel, but with the 'header' TFS insists on putting in your export, the linked table has issues (like not having headers in first row, and then always having two erroneous records upon every refresh).
Question 2
Any thoughts on how to get around the funky header?
There is no native way to link Ms Access to TFS or to easily get rid of the header row that's added in Excel when linking a table. I'm afraid that the direction you're seeking will not help you in the end. It will only put all the computational pressure on Access instead of Excel.
The way to go here would be to setup the TFS reporting features, which offloads a lot of the processing to SQL Server Analysis Services (which can be used as a source for Excel) and reporting is provided by SQL Server Report Server in that case.
Unfortunately, the Reporting part of TFS hasn't had a decent update since 2010 and can be a bit archaic to use. For VSTS (the cloud-based version of TFS) it's now possible to link the account to PowerBI, which does everything you're after. This feature is not available on-premise though, it may be a great reason to move to the cloud. PowerBI can handle large amounts of data, can be connected to one or more VSTS accounts and can slice and dice data from multiple accounts and projects with ease.