April 26, 2015 4 Comments
I thought it was about time to add some indexes I had to the Geelong & District Database as it had been a few months since I had done this. More than 1.63 million entries live in an MS Access Database. Indexes of all sorts to be added are compiled into an MS Excel spread sheet then copied and pasted into the MS Access form before uploading to the SQL database online.
Everything was honky-dory until I tried to paste – as I have done hundreds and hundreds of times before.
ERRORS – and nasty ones. It wouldn’t even let me manually add a single entry!
What had changed since I last added entries? I had upgraded MS Office to version 365 [actually version 2013]. I wasn’t about to uninstall the new version as that included months of University assignments but surely it was just something really simple that would fix the problem.
I had older versions backed up – didn’t come down in the last shower and I was able to update the database on the old Notebook running Vista.
Hours and hours of Google work showed I wasn’t the only one with problems but none of them were exactly the same – and certainly didn’t solve the problem. By then I had about 8 versions I was experimenting with.
I then sat quietly and spoke quietly to myself saying there HAD TO BE a logical answer. Then I tried attacking it from a different angle. The Geelong & District Database is probably one of my oldest LIVE MS Access databases. So I tried a new more recent one – NOT A PROBLEM!
I won’t bore you with the next four hours of experiments – I’ll just give you the outcome. The MS Office upgrade should have converted the database to work in the new version. True BUT NOT IF YOU HAVEN’T UPGRADED every time there was a new release. I couldn’t afford that and so somewhere along the way where I didn’t pay for an upgrade until there were a couple of versions, it skipped a step that no longer works in the latest version. Very convoluted but after another couple of hours I had all the data, queries, forms and reports working in a brand new database. Basically I had to completely recreate the relationships as well as the Forms and Sub-Forms but I’m no longer heading for the Guinness Book of Records!
I’ve lost nothing [unless you count a bit of sanity], added the new indexes and uploaded the entries to the online database.
Haven’t had any sleep since last night but hey – IT’S WORKING! What a relief.