Darryl Collins
darryl at whittleconsulting.com.au
Thu Aug 4 19:11:38 CDT 2011
Mark, The best solution would probably be to store their source data on a SQL server (or Access DB) as a back end and import into Excel the data they want to analyse. That way the integrity of the source is maintained but they can do all the numerical wizardry on the numbers in Excel. It sounds chaotic, but there is opportunity in chaos, howver you are going to need to sell the idea to them. I would argue that they need you (otherwise they wouldn't be in this mess now if their original lads did a decent job) but you are going to have to take some time to fully understand the system. How can you fix something that you don't understand fully? Doing a quick bandaid fix would be a waste of your time and their money as it will all fall over again in a couple of months. If they do only want a quick and dirty - I would recommend walking away and letting them know that is now how you work as a professional. You either do the job correctly or not at all. FWIIW some of the worst spreadsheet I have seen have come out of those 'smart' places like Accenture and PWC etc. The calculations are clever, but the integrity of the workbook is rubbish. They are nearly all unusable with a few weeks of the users getting hold of them. Cheers Darryl. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms Sent: Friday, 5 August 2011 1:28 AM To: 'Access Developers discussion and problem solving' Subject: [AccessD] AC 2007/2010 - Help...this is turning into a research project Long story short: Got a call from an agency....for a "short term" contract to "clean-up" an AC 2007/2010 application. I've finally looked into the plumbing.... All of the usual....some tables have no keys, some tables have client descriptions as keys, etc, etc. It was all designed by non-professionals. It gets better: Most of the work is done in Excel....it acts as a giant "dashboard"....investment/portfolio management application. They are using all of the new Table features with OLEDB connections from Excel to Access. A million calculations are being performed against this linkages in Excel.....25 worksheets total. My first task of creating some VBA to update the OLEDB connections took over 8 hours.....and quite a bit of research. I kept on getting 1004 errors for one of the connections. Here, it turns out that connection was to a Pivot Table that went directly to an Access table. Any attempts to update the DataSourceFile property failed via VBA. However, get this: it could be done thru the GUI. So once again, I tried researching this....to no avail. It appears this whole project might become a research project.....and the client wants a defined time-line. What do I do ? -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com