I’ve been reacquainting myself with MS Access recently. Since I seem to be mainly in a Windows mode at the moment and since I have an Office365 subscription, I’ve got access to Access and it provides tools for building an interface to keep on top of my collection of data about what goes on in my garden – plants, harvests and the like. I had been using SQLite but making updates from a command line interface was clunky and I don’t need the power of more advanced database software or to put it behind a web-based interface.

Stage one, creating the Access database from my SQLite one, was accomplished earlier this month. Since then, I’ve also been feeding in a whole set of updates that I was behind on inputting, starting with (ahem!) events from 2022! All of those had been typed up in Excel but I needed to get them imported. One of the things that slowed me down was the discovery that I hadn’t got the Access tables set up with autonumbering and, contrary to my expectation, I couldn’t easily change that afterwards and have Access start autonumbering from the next available number while preserving the existing links between tables.

It turns out there is a solution although one which really ought to be much simpler on a system that is meant to be quite beginner friendly. What you have to do (after ensuring a back up has been made!) is create a copy of the table in question (structure only) and delete the non-autonumbering key field. There is no data in this new table and it isn’t linked to anything so nothing is locked or lost. Now you create a new autonumbering field with the same name and set that as the primary key. There’s still no data so it doesn’t mind. The critical step is then to create an ‘Append’ query to dump all of the data from the old table to the new one, which will accommodate the fact that every entry in the key field has a number in it. You can then weave the new table into the mesh of relationships and drop the old one and the new table will work with autonumbering as desired.

In other words, Access is perfectly capable of dealing with the problem – it just makes you go round the houses to get there. These are the kind of joys I remember from working with Access in the past. I’ll see how I get on setting up some data entry forms and feeding in the information for 2023 and 2024 to date. Either that will make up time or I’ll soon be updating my skills in feeding from Access to another database system. Whichever way, I hope to be able to accomplish better things by the end of the year, like finding out which types of tomatoes seem to have done best in my polytunnel in recent years!