@Pat Hartman
@ The_Doc_Man
I'm a broadcast engineer for my state's local PBS station and the purpose of of the database is what cables are connection various pieces of equipment together and the function of each cable that is connected to a piece of equipment.
All of the cables are given a 7 digit alphanumerical code that represents a category that is assigned to the cable based on the function of the cable and 4 digit number to give the cable a unique identifier in that category. So if a cable is going to be used for sending HD video from point A to point B that cable falls into our category of HD video cables and gets the 3 character prefix of HDV. Typically, when we run a new cable we just add 1 to the four digit number of the last cable that was ran in a given category. So because the last HD cable that was ran has the cable number of HDV2204 the new cable that is ran will be entered into the database as HDV2205.
The record in the database will indicate that HDV2205 connects the video output of Satellite Receiver A that is in Rack 24 is connected to Port 221 of our video router.
The reason that we can't exceed a four digit number in any given cable category is due to the fact that also use Auto Cad to create drawings of our various systems. So we have a drawing that shows all the equipment that is connected to the inputs of our video router. My boss is very particular about how those auto cad drawings are done and because all of our current drawings use the 7 digit alphanumeric code he doesn't want to get away from the 7 digit alphanumeric code so that the drawings remain uniform. I know that is a silly reason to be unwilling to change but it is a hill that my boss will die on so I've given up on trying to get him to change.
We consider any cable that is still in the racks as a active cable, so if we take out a piece of equipment but leave the cable in the rack we'll note in the database that one end of a cable is not connected. And then we consider any cable that is completely removed from the racks as a decommissioned cable and delete the cable from the database.
Going back to my example of the Satellite Receiver. PBS decides that is going to stop sending us content via Satellite receiver A. Since, we no longer need Satellite Receiver A, we'll remove the the Satellite Receiver from rack 24 and remove the cable from both the video output of the Satellite Receiver and port 221 of the video router. Then we'll pull the cable HDV2205 out of the racks completely, coil up the cable, cut off the labels, throw it in out bin of cables to be used at a later date and delete HDV2205 from the database.
We do keep a history in a roundabout way. When the main navigation page of the database is closed. a function is called that creates a backup of the database for that day and saves the backup file to a shared network folder. My IT person has that shared network folder setup to automatically delete any file that is older than six months. So we effectively have a six month history of changes to the database. Which is more than plenty for us because we aren't constantly swapping out equipment and adding or removing cables. But beyond that, I don't know if keeping a history would benefit us since we removed cables when we remove equipment. If we swap out equipment or move equipment, we just update the current record in the database with the changes.
Pat, your suggestion that I either delete or archive old data and then create a procedure to find gaps in the cable numbers is ironic because I was doing something similar before and was cautioned against that by an other access user because if something didn't get deleted properly I could potentially create a duplicate record and corrupts the database.. But I am thinking I might not have been doing it in the way your suggesting. I am not using the cable numbers as a primary key, but rather letting Access assign the primary key to the records. So the way that I was doing that was first I'd delete the old data from the database, a query would run that identified gaps in the cable numbers and then a new record would be created when a cable number was reused.
To me, it sounds like you are suggesting that I write the new data to the old record, which I would think wouldn't be possible if the record is deleted.
I am confused about the_doc_man's comments about bloat and rollback. Does that simultaneous delete and insert only happen when I change information on a record in the database or does it also happen when I delete one record and then create a new record?
And if I set up something to run a compact and restore of the database, would I run that every time the database is closed or would it be a periodic thing?