Unanswered: Cautionary Tale - moving from Access 2000 to 2010
Thought I'd share a recent tale with you, in the hopes that it prevents someone else getting a Friday afternoon emergency like the one I had last week!
The company is in the process of updating our user PC estate from Windows XP, Office 2000 and Lotus Notes 6 to Windows 7, Office 2010 and Lotus Notes 8.5. This is not a fun thing to do. This is being done site-by-site, and so the network is enforcing the Office applications to work in compatibility mode.
One of our legacy processes revolves around four Access 2000 databases. Three of them are used by end users - they download CSV files from the GL and import them into one of the databases. Once there, they manipulate the data and prepare it for exporting, via the fourth database, into the reporting system.
We were asked to create a new Access file for another business group to use with this system. This has been done before, and so we didn't think much of it. I duly copied one of the existing models, stripped out the data specific to that business unit and updated other constant values - in general, housekeeping-type tasks. I did this in Access 2010.
When the user came to try importing data for the first time, they received an error message to the effect that the import specification named on the TransferText method could not be found. I tried manually creating the tables MSysIMEXSpecs and MSysIMEXColumns within the new model from the old one, and thereafter the TransferText method gave the error "'PrimaryKey' is not an index in this table". This surprised me even more, as the destination table doesn't have a primary key!
I created a new model for the new business unit, based on a working one, this time working solely in Access 2000. Once I had created the file and performed the housekeeping tasks, I verified that the import specifications still existed. They did, without manual intervention! Then I tried performing the import process, once in Access 2000 and once in Access 2010. Both times, it worked.
I suspect that this problem was caused by a change behind the scenes in how Access handles import specifications - I haven't tried creating any databases in Access 2010 that use them yet, but I leave this here in case anyone else has this problem!
10% of magic is knowing something that no-one else does. The rest is misdirection.