back to article Excel is for amateurs. To properly screw things up, those same amateurs need a copy of Access

If there is one Microsoft product guaranteed to send a shiver down the spine of an IT pro more than Excel shoehorned into the wrong place, it's Access inserted into any place. Welcome to Who, Me? Our story comes from an anonymous financial services company where a reader Regomised as "Chris" used to work back in the day. He …

  1. GlenP Silver badge

    Access

    Access is not part of our default Office install and never will be! When I took over here we had a number of private databases running, I put a stop to them and insisted data is held in the main ERP system where possible.

    We have one application, a data driven label printing program, that uses it but that's only active on the runtime version of Access on an RD server.

    1. Korev Silver badge
      Coat

      Re: Access

      >Access is not part of our default Office install and never will be!

      The users will find other ways to Excel with their "databases"...

    2. chivo243 Silver badge
      Trollface

      Re: Access

      Access is removed, so is Publisher and Outlook from the default installation of office. If anybody asks, it's not part of our package!

      1. Terry 6 Silver badge

        Re: Access

        Publisher is surely harmless, as long as it's not used out of its field ( for example to try to produce professional DTP). It is what you need to put a notice up on the office board, say, reminding people of the tea rota or that its Emily in accounts' birthday.

        What is strange about it is that it isn't included in the Home version of Office. i.e. where it's most useful, but is included in the Pro versions where it isn't so much needed. Another example of how MS are so out of touch with the Real World.

        1. Charlie van Becelaere
          Trollface

          Re: Access

          Upvoted for the "out of touch" comment, but I have to say it's dead simple to make a notice of Emily's birthday in Excel.

          1. Terry 6 Silver badge

            Re: Access

            Or WORD or even on a Powerpoint page. Or even (gasps) with a nice colour felt tip pen.. Publisher has all the amateur's tools for a nice poster, or a home made greetings card and all that SOHO stationery. Which,as noted, is why they don't put it in the Home version where it's useful..

            1. midgepad

              Notepad

              Is sufficient for that task.

            2. Anonymous Coward
              Anonymous Coward

              Re: Access

              No! Word is for maintaining tables of stock inventory and balance sheets!

          2. karlkarl Silver badge

            Re: Access

            Really? All the accounts are already in a nice MS Jet Access database!

            Emily's birthday notice is basically a print screen of a cool Visual Basic program knocked up by an intern ~12 years ago.

        2. Dave314159ggggdffsdds Silver badge

          Re: Access

          Does publisher even still exist? I thought it was rolled into Word a decade ago.

          1. Glenn Amspaugh

            Re: Access

            Power Point is the true DTP hero of MS Office.

          2. Terry 6 Silver badge

            Re: Access

            Yes. £420

            You had me doubting myself. (Don't know if there's a 2020 version though)

            Quote from

            https://www.microsoft.com/en-gb/microsoft-365/p/office-professional-2019/cfq7ttc0k7c5?activetab=pivot%3aoverviewtab

            Office Professional 2019

            ‪Microsoft Corporation‬

            32

            • One-time purchase for 1 PC

            • Classic 2019 versions of Word, Excel, PowerPoint, and Outlook, plus Publisher and Access

            • Microsoft support included for 60 days at no extra cost

            • Licensed for home and commercial use

    3. JimboSmith Silver badge

      Re: Access

      One place I worked had a regulatory requirement to produce a report once a month (I'm being deliberately vague because I might identify myself). To produce a report you took the output of two crappy programs (both made by the same company) and mapped the UID field between data sets. One program was supposed to contain the master data with the required details. The other was a list of each time the ID and other details were used. So like a door entry log it might record the ID number of the pass and when it was used.

      One day I was told that I was about to become surplus to requirements and at risk of redundancy. And low it came to pass that I was made redundant. Suddenly they realised that I did a specialised job as part of my work and was asked to help. Could I train a senior manager and a bored office junior who had just graduated from university (in Classics) ? Not wanting to make waves I agreed to do two sessions of training for them. This was probably far less than they needed but they were making me redundant after all. I explained how to map and merge two sets of data from two different pieces of software/databases in Access and generate the report. All you had to do then was chase the missing data which the report highlighted as not being there.

      You might have thought the software firm would have automatically produced something to do this, but as I say they were fairly crappy. Well the senior manager spent most of the training on their phone talking to various people. The office junior paid about as much attention to the training as I do to Jane Austin period costume dramas. There was clearly something else she wanted to do instead of this and said in a cocky voice she'd "Got it". I didn't leave any notes as they were supposed to be taking notes and asking questions. it wasn't difficult to do at all but you had to have a vague understanding of what you were trying to do in the first place. Access was eaiser for me than doing it in Excel and as we found out this month has a definitive limit.

      A month after I left I had a call from the university grad now panicking and much less cocky. She couldn't understand anything she'd written in notes taken in the training. Hardly surprising, so I said she should talk to the senior manager but he couldn't remember anything apparently. Imagine that! I said I would charge to do more training or to come in and do it for them at which point they were less interested. I believe they were fined far more than the cost of hiring me back for a day. They then paid the software firm to produce an automated software method of doing this. That took a long time to iron out all the bugs and I understand more fines were issued. Turns out (thanks to an ex-colleague of mine spilling the beans) that the senior manager had only been there to check that I was actually doing the training.

      1. Anonymous Coward
        Anonymous Coward

        Re: Access

        oh dear oh dear oh dear what a shame! Result!

    4. Jakester

      Re: Access

      Fortunately, I don't have to deal with databases. Back in the early '90s I had a service call at a business using Access for their database. They could reproduce the problem of a whole range of records would disappear with a certain sequence of adding, editing and deleting single records. Microsoft vehemently denied any problem with their Jet database engine for years. I don't know if they ever did fix it as I quit following that issue with computers after about a decade.

      1. dc_m

        Re: Access

        Nope, I have used Jet reports recently alongside Microsoft dynamics. It's still horrendous.

  2. Anonymous South African Coward Silver badge
    FAIL

    I've been there with a stock-taking access program... was a bit of a nightmare TBH.

    But at least it looked slick and neat... but the gubbins.... *gibbers*

    1. tfewster Silver badge
      Facepalm

      I confess, I wrote one myself for the systems teams to use for stock control.

      We weren't allowed to use the corporate system, as we were just a side branch. But we had PCs, wireless hand scanners and label printers in the stock we had to count every month, so why not build our own? How difficult could it be?

      As it turned out - not very difficult. Scan deliveries in, print labels for the boxes, easily track stock, print shipping lists. Stocktake into a new "table" to compare with the DB.

      And finally, print the reconciled stocktake report to send to HQ to be re-input into their system. Could I have sent an electronic copy ready for import? Yeah, but stuff 'em.

      1. John Jennings Silver badge

        Me too

        I wrote one many years ago in a small software house., for a single site charity

        It worked OK, provided you didnt have too many users hit it at the same time.

        For really simple stuff, and back in the day, it was adequate (ie windows 3.1/3.11 machines) for adding, subtracting stock, and simple reports and printing...

        I also used it for a 'homer' in 1999 to fix old Y2K bugs in old databases in some farming legacy software. I made at a grand a customer for a couple of evenings work each with that product. There was some puckkering with the first time I tried! Small farmers tend to be big guys, and it was no fix, no fee conditions.

  3. Red Ted Silver badge
    FAIL

    Using a computer where pen and paper would have sufficed!

    When the only tool you have is a hammer, all problems look like nails.

    Step away from the keyboard, step away....

    1. Korev Silver badge

      Re: Using a computer where pen and paper would have sufficed!

      On the other hand, if you (think) you know how to make Access databases, then knowing that you'd be waiting for 18 months for something you can knock up in a day or so would be very frustrating...

      I've had to clear up the mess of scientists' crashed Access DBs before, I'm not seriously advocating the above, just empathising...

      1. Peter Gathercole Silver badge

        Re: Using a computer where pen and paper would have sufficed!

        There is another side to this. Most companies do not have a general purpose database system lying around for users to use.

        I've often wanted to create a small database at many of the organizations that I've worked at, mainly use on one of my tasks, but sometimes requiring it to be shared. If we had had an Oracle, DB2 or other mainstream database instance that allowed users to create their own relatively small DBs then I would use them.

        Being on the UNIX side of things (and never getting on with MS applications anyway), I've often ended up writing my own series of scripts on a GP UNIX system, using things like cut, join sort, uniq, comm, and most importantly awk, when I would have been much better off with half a dozen tables in a proper DB, with SQL as the query language.

        But the PTB (or should that be PHB) would never see the value in having a small real database installed in their standard image for the systems (even the Linux ones where it could be done for free), nor would they commit to a general purpose proper database on any shared system (in fact, since the advent of the PC, they've not even seen the benefit of a shared general purpose system!)

        So I can see why some people would turn to Access and even Excel to meet their requirements.

        1. Anonymous Coward
          Anonymous Coward

          Re: Using a computer where pen and paper would have sufficed!

          The front end on access is quite nice, and was my first experience of databases (not including being shown hypercards years before in school). It's a shame the backend is a bit ropey, but it probably does as well as you can for a shared user database that doesn't go through a server. Before we built a real database for storing results they went into an access database that a predecessor had set up, most stuff was machine readable, so little direct data entry, saved insert and query statements throughout, it actually worked quite well. When we built the new system a lot of the design could be carried over. The largest db did need to have the repair function regularly run on it prophylactically. It's not great, but I'd take it over excel any day.

          That was set up by someone who knew what they were doing (they'd also created proper databases for other tasks, so I suspect the choice was based on insufficient time to build user interfaces to them). By contrast, someone who did not know what they were doing was much later tasked with setting up a db for a different project. A long time passed, at intervals we got shown the carefully designed forms and workflows. Eventually they moved on, during the handover of the 'nearly complete' db, I realised that all these forms were backed by individual tables and there were absolutely no relations connecting them.

          1. MatthewSt

            Re: Using a computer where pen and paper would have sufficed!

            My first gig was an Access 2000 database on a co-ax token ring network...! A switch to 100mb Ethernet, separate the screens into an MDE and the backend to SQL and they were well on their way! For a small business, as a front-end to a "real" database it's a great product. Centralised backup and easy for admins to modify (but not end users)

          2. david 12

            Re: Using a computer where pen and paper would have sufficed!

            a shared user database that doesn't go through a server.

            It does go through a server -- that's the problem. There is a network database service provided by matching "workstation" and "server" services which auto-start on most Windows machines, and a database protocol called "SMB" which provides record locking and retrieval. Problems are that (1) SMB on IP is notoriously high latency, buggy, and made worse by authentication and encryption, (2) the native Windows database API dates back to DOS 3.x and was never upgraded to provide record-level security or authentication (WinFS was canceled) and (3) If it worked properly, why would people pay for SQL Server?

            The DOS 3.x database API is so limited that not even Access can use it without another layer on top, and even then is fragile due to the lack of record-level ACLs. While the 'server' service and SMB/IP protocol is so heavy weight that the number of recommended users has declined from 32.. to 16 ... to 8 ... to 4 ... to 1.

            Meanwhile, the free version of unix that was provided to universities did not include a native OS database system (just one of the reasons why mainframe programmers thought it was 'not a real operating system'), and the whole idea of a native OS database system ('computer says "no") was lost to a new generation, who are convinced that Access is a 'flat file' database because they have no idea what that means.

            1. Anonymous Coward
              Anonymous Coward

              Re: Using a computer where pen and paper would have sufficed!

              Well, you learn something new every day, so I sort of stand corrected there. But using file locking over SMB is pretty much what I was thinking of, after all this is how sqlite (another direct file access database) handles things without needing a specific database api.

              That said, I'm struggling to find any information on this DOS database api.

          3. swm Silver badge

            Re: Using a computer where pen and paper would have sufficed!

            "(not including being shown hypercards years before in school)"

            Ah yes - hypercards. Sort of a neat idea.

        2. anothercynic Silver badge

          Re: Using a computer where pen and paper would have sufficed!

          Boy does this sound familiar!

        3. Yes Me Silver badge

          Re: Using a computer where pen and paper would have sufficed!

          "So I can see why some people would turn to Access and even Excel to meet their requirements."

          Right. Let's say you urgently have to report infections and deaths for a serious new illness to a central authority, knowing full well that the results will be in the national media as well as sent off to the World Health Organisation. I wonder what you'd choose...

          1. Anonymous Coward
            Anonymous Coward

            Re: Using a computer where pen and paper would have sufficed!

            postgres

      2. Imhotep Silver badge

        Re: Using a computer where pen and paper would have sufficed!

        Yes, IT can solve the original problem in the beginning or solve the "solution" later.

        I've had to replace a number of cobbled together customer apps based on Access or Lotus Notes once the number of people using them had grown and they started to fall over.

        You couldn't really fault the intent, the design or the functionality - just the tools used for implementation. Which made things easier for me as a rule, since a lot of the work had been done and verified in those areas.

        1. Angry IT Monkey
          Pint

          Re: Using a computer where pen and paper would have sufficed!

          "I've had to replace a number of cobbled together customer apps based on Access or Lotus Notes once the number of people using them had grown and they started to fall over."

          My twisted mind is picturing staff falling over after too many of these ->

          Time for bed...

      3. Stevie

        Re: Using a computer where pen and paper would have sufficed!

        Access is a great tool for doing some jobs. Reading these tales of woe it is clear to me that they would have resulted in a mess no matter what technology was backing them.

        Point: MS Access is a powerful little tool, with a smashing front end. I'm looking at devising a small database to hold details of database user setups, to replace an absolutely horrible legacy spreadsheet.

        I could deploy it as Oracle or PostgreSQL, but then I'd need a second technology to front end the thing for display purposes, colleagues for the using of.

        I cannot deploy a web server for this. I do not know Java well enough to write what would take me a few minutes, even years since I last did it, of running off a Visual Basic app with Visual Studio.

        But Visual Studio cast a small fortune, and I have no license for Visual Basic.

        I do, however, have a copy of MS Access. The temptation to spend a couple of hours doing the entire thing in MS Access is almost overpowering.

    2. Mike 16 Silver badge

      Re: Using a computer where pen and paper would have sufficed!

      (Shivers) My first "real" job was with a telecom equipment company (since acquired at least three times and probably deceased) at which _exactly_ this happened. Slightly before I arrived, the process to turn a list of orders to the factory into checking parts inventory and if needed ordering parts was entirely done on IBM "Tab" equipment that Herman Hollerith would have understood. This was replaced by shiny new System 3 computer and (allegedly) appropriate software. Which of course never worked. Had we been a bakery, we could have the "pulled" 2 dozen eggs but no flour to "build" an order for one cake.

      It got so bad that the factory and engineering contrived a parallel system that allowed things to actually be built. Some of the documents were not literally "pen and paper", but "pencil on mylar".

      Same the basic idea.

      BTW: this was also the place where, when I asked the main operator why smoking was allowed in the machine room, they pulled a disk-pack off the shelf and blew smoke into it, saying "Don't hurt them none".

  4. Peter Galbavy

    Managers need to appear to have a purpose, especially when they don't.

    1. KittenHuffer Silver badge

      Yes, most of them are that way, and can generally be referred to as 'Slinky' managers! Bloody useless, apart from making you smile when you get to push them down the stairs!

      1. Scott 26

        I use the term 'slinkies' for the helldesk...

        Once , in front of their manager - who laughed and laughed until she clicked to it being HER staff she was laughing at.

  5. Little Mouse

    I had fun with access 2.0 in the 90's

    It was pretty powerful for an Office application, but you did have to shoehorn Visual Basic code behind every button.

    Recognising its limitations was key. I created a great company phone book, for example. Simple & functional.

    The PHB's then demanded that I write a system that could track shipping containers around the world, create historical invoices in assorted currencies, and that could know, by magic, when users input details incorrectly, and even, when they didn't input any details at all, know exactly what was missing. Sigh.

    1. KittenHuffer Silver badge

      Re: I had fun with access 2.0 in the 90's

      I remember having a similar request for 'sanitising and correcting' entries made by aeronautical engineers within an aeronautical engineering database. My reply was along the lines of - If I could develop a system that could correct qualified and experienced aeronautical engineers then I wouldn’t be handing it over to the overlords of my current IT contract, but instead I’d be making Billions in the AI sector.

      1. christooo

        Re: I had fun with access 2.0 in the 90's

        As one of those types, the only place we wrote the correct actions was the aircraft log book. Why? because our billion dollar computor was open to view to all employees so we screwed it up for them!.

    2. MiguelC Silver badge

      Re: I had fun with access 2.0 in the 90's

      I had absolutely no fun with Access in the (late) 90's. I was called to "rescue" a largish occupational health company that had all of their information (I really mean everything, clients, contracts, test results, payroll) in a single Access 700 MB application. They had an hardware failure that crashed the .mdb and their most recent backup copy was over a month old. Unfortunately (for them), we were unable to restore it, we only managed to salvage parts of tables, unlinked to anything else. They ended up losing several contracts over the issue.

      Think they learned the lesson? Think again, they rebuilt from the backup copy and manually reinputed all that could be saved from the crashed file...

    3. Not previously required
      Linux

      Re: I had fun with PARADOX in the 90's

      I can't understand it - all these Reg readers bewailing the awful Access. Is no one going to join me in shedding a tear for Borland's Paradox? It worked well enough with a database held on a file share with record locking and without falling over. Access didn't do that. It had proper database features like referential integrity. The programming language was basically Pascal, although it did get scattered a bit.

      Penguin because I still hate anything made by MS, but there should be an icon for fond reminiscence ?slippers by the fire

  6. Admiral Grace Hopper
    Facepalm

    All that counselling, wasted

    A repressed memory has just popped up to the surface.

    "We'll prototype it in Access then port it to SQL Server. Microsoft have a porting tool so we can just run it through that and Bob's your uncle".

    1. Antonius_Prime

      Re: All that counselling, wasted

      I wouldn't call it a repressed memory.

      Argument for the Defence, perhaps...

      #TheresBeenATerribleAccident

    2. Ebbe Kristensen

      Re: All that counselling, wasted

      "we can just run it through that..."

      Back in the day (some 35+ years ago) I was taught that:

      - Just

      - Only

      - Easily

      DON'T use those words.

      1. Admiral Grace Hopper

        Re: All that counselling, wasted

        With the corollary that you should never trust anyone who does use them.

        1. This post has been deleted by its author

      2. N2

        Re: All that counselling, wasted

        And also:

        5 minute job..., 10 minute job... etc

        I had a manager whose emials never made sense, but he would always ring up (hence my deletion) and say, I need to come and talk with you this afternoon, about something that will be a 5 minute job.

        Fine, I can do tomorrow morning? (knowing he would not be in the office) anyway, an hour past the alloted time he would rock up expecting attention with some insane ideas to polish his data handling crown, to which I'd propose anything to prevent his blundering misadventure.

    3. Antron Argaiv Silver badge

      Re: All that counselling, wasted

      Conversion Tools...don't

      ...at least, not completely, nor without "issues".

      // at least, in the world of electronics CAD

      1. Prst. V.Jeltz Silver badge

        Re: All that counselling, wasted

        I once knew i guy who's idea of "save" and "load"

        was "Print" and "OCR"

    4. Mr Humbug

      Re: All that counselling, wasted

      Thanks a bunch! I'd managed to put that 'tool' completely out of my mind.

      I did use it once, to discover that its description was far in advance of its capability. As I recall, all it did was copy the table field structure and data. All queries were still executed in Access, so performance actually decreased, and you lost all the foreign key constraints

      1. Anonymous Coward
        Anonymous Coward

        Re: All that counselling, wasted

        I just recently tried the conversion tool on an access DB I inherited. I'm hoping to migrate to SQL server, but it's not high priority enough to put in a lot of effort. I assumed I messed up the options when all the foreign keys were gone.

        Turns out it was the tool... instead of the tool pecking the keyboard

    5. Stevie

      Re: prototype it in Access then port it to SQL Server

      Works fine without any tool other than the source SQL used to set up the MS Access DB. I used an out-of-date copy of ERWin (an excellent tool that has been priced beyond reach of most now, but then was available in a desktop version for $250) to design the D/B and generate the SQL to build it, so once I had the design done in MS Access and had done the proof of concept I used the same SQL to build version 1.0 of the SQL Server database.

      Of course, more MS SQLServer features were leveraged once the application was up and running, so a back-port wasn't possible, but it was ported to MySQL twice, once when the FS lobby said it would be ready for prime time (it wasn't, outside of hobby website purposes), and once when it was actually able to do the job, years later.

      So prototyping in MS Access is one thing I think is actually a great idea. You can solve all the big problems like undocumented manual procedures by showing the prospective users a working model and letting *them* use it and critique it.

    6. l8gravely

      Re: All that counselling, wasted

      Who else remembers all the joy and excitement of Ruby on Rails and how easy it was to make CRUD (Created, Read, Update, Delete) forms for your data. At least it could be kept in a real SQL DB... but they forgot the cardinal rule, which was authentication and authorization. At least the five or ten years ago I tried to make it work for me.

      It worked a treat for read-only search forms though.

      Looking at it more recently, they do seem to have cottened onto that need and it seems to be more documented and supported out of the box.

  7. fatalXception

    A nice little story but hardly a damning indictment of Access, since clearly it was the Ops Manager who was the "Tool" in this case, albeit the wrong one. I'm pretty sure the guys would have had the same problem no matter what he used to create his app.

    1. nematoad Silver badge
      Unhappy

      "Ever been faced with an over enthusiastic manager with some, but not quite enough, IT skill?"

      Yes.

      They were the bane of my life when I was doing desktop support at an oil refinery. Our team drew up a blacklist of managers known to fiddle around with the applications and settings on their PCs and laptops and if possible they were put at the back of the queue. In the end it was only given to a few more temperate members of the team to sort out as otherwise things might have been said and done that did not exactly accord with the SLAs we were working to.

      As the old saying goes "A little knowledge is a dangerous thing." And in a lot of cases expensive as well.

  8. My-Handle Silver badge

    While it might not be the best tool for the job, sometimes it's the only one you're allowed to get your hands on.

    Back in my own "amateur" days, I was tasked by a manager to create a piece of software to manage regularly occurring tasks (think regular reviews for customers). It was a little more complicated than that, but not much more. Something that was just out of Excel's reach. Now, I did put in a request for access to an instance of SQL (in whatever form), but I was denied. So I put an access data file on the network share, protected it every way I knew how, and then made a "client" file that I could distribute to my team-mate's machines (almost entirely consisting of VBA code and a form for the front-end). I freely admit it was a very janky setup, but it was made to work with the tools I had available. It increased the output of the team three-fold. It also was my gateway into actual IT work.

    Access has it's place.

    1. Coastal cutie
      Facepalm

      Access woes

      It does - the trouble is that isn't usually the place you find it. A few years ago I was involved in a project to change platforms - the new one had stricter security rules and Access was banned. Every so often, the hideous memories of migrating the Access databases that emerged from all the dark corners into SQL come back to haunt me and the Data Architect. All were locally constructed by enthusiasts, not one was screwed together properly - if there was a convoluted, long winded way of doing something, they found it. On the other hand, it does a very good job of cataloguing my huge collection of cookery books.

      1. Intractable Potsherd Silver badge

        Re: Access woes

        "... if there was a convoluted, long winded way of doing something..." then it means that the person who made it was going step-by-step and visualising how to get from A to B. Elegance comes with experience, which has often been refused because "it's too expensive" or delayed beyond a reasonable point. Remember that you folk are craftsmen*, and, unfortunately, the modern corporate world doesn't value your skills until it is too late.

        *Term of art not denoting any sexism.

    2. Anonymous Coward
      Anonymous Coward

      I help run our church library. For years it was a strictly paper-based system - an accession log notebook, actual card catalog of hand-written cards, etc. It took an hour or so to enter each book. When we started managing it, I began the slow process of going digital.

      Current state:

      Any book set up in the last several years is listed in an LOBase database (LibreOffice version of Access), to take advantage of the relational database and a front-end. When a batch of books is set up, I export the appropriate data to a .CSV file, upload it to a cloud-based library, and then hit update on the (donated used) iPad's library software that's used as the user-facing catalog. Per-book time: 5 minutes or less.

      Older books are still found via card catalog. Need to get all those typed in.

      Keep in mind the budget for the entire library is <$100/year, so a free database system with a free front-end is a must. (Cloud component is the only thing we pay for, and the LOBase database has data that doesn't belong in the user-facing index.)

      1. disgruntled yank Silver badge

        < $100/year

        Roughly $72/year will give you a Digital Ocean droplet. You can run Django with a Postgresql back end, and be in business fairly quickly.

        1. Anonymous Coward
          Anonymous Coward

          Re: < $100/year

          (Original poster, but not your downvote)

          That likely wouldn't include a nice front-end to run on the net-access-usually-disabled iPad. Most of the $100/year goes to the cloud subscription to link the iPad's local storage to the real database (no other way to get the data into the iPad software without manually typing it again), plus some $ for supplies.

          1. Anonymous Coward
            Anonymous Coward

            Re: < $100/year

            (The church librarian here)

            Oh yes, forgot to note, our "intermediate" step between 100% handwritten everything and iPad was to enter the book data into the database, then use something akin to mail merge to print the cards for the card catalog. Both then and now, when a batch of books was entered, I'd print a report of the just-entered books that shows what to write in each book (accession number, Dewey number, etc.).

            Using LOBase (LibreOffice's version of Access) as a single-user database with built-in front end works just fine.

        2. Andrew Alan McKenzie

          Re: < $100/year

          So spend more than 72% of your budget on software...good plan

    3. Arthur the cat Silver badge

      While it might not be the best tool for the job, sometimes it's the only one you're allowed to get your hands on.

      Back in the dim and distant past (the 1980s) I met a guy at a conference who had had to write both a parser and a text preprocessor in COBOL-74. He'd originally written them in C (with a bit of lex & yacc) but his managers announced "we're a COBOL shop, nobody else knows C so your code is unsupportable if you go under a bus, you'll have to rewrite it in COBOL". He ended up writing the parser using recursive descent with manual stack management using MOVE statements to/from a record array.

      The next time I met him, he'd changed jobs.

      1. A.P. Veening Silver badge

        He ended up writing the parser using recursive descent with manual stack management using MOVE statements to/from a record array.

        And the COBOL code was still unsupportable because it was way beyond his colleagues.

    4. phuzz Silver badge
      Meh

      I mean, assuming the stock taking system was only ever going to be used by a small handful of people, Access isn't a terrible choice.

      It's just in ten years time, when 100 people are trying to use it for things it was never supposed to do, and the person that originally wrote it has long since moved on. That's when it's terrible.

      1. My-Handle Silver badge

        Yep. It was originally built for a massively under-resourced team of no more than ten, all of whom sat within shouting distance of the developer's desk (i.e. mine).

        If it's any comfort, as soon as the higher-ups in the company heard about it, plans were made to extend it's use to other teams. Requests from the developer to be allowed time to document it, time to implement it in a more scalable architecture (SQl / MVC for example) were all denied. And yes, the developer did move on shortly thereafter.

        I may have inadvertently supported your point :)

  9. Sequin

    The problem is usually not the tool that is used, but the tool that uses it.

    1. Evil Auditor
      Thumb Up

      I fully agree. But with Access it certainly is both.

  10. Anonymous Coward
    Anonymous Coward

    Personally I like Access when it's just me using it with copies of data from elsewhere. I've used it as a data warehouse to create reports for years and never had any problems. Anything else is a no-no.

  11. ColinPa

    At least they used a database

    Going back 30 years, there was a tools team who were responsible for developing tools for developers to use.

    This team had been working for a year on a project to track defects using VM/CMS flat files as a database, and a hand built gui. I was asked to evaluate/test it prior to it "going live".

    I asked the users what they wanted. One manager said he wanted a list of defects of people working for him, and people who used to work for him. I went to the tools team with the list of requirements. They looked shifty and said, they could provide these features in a few months.

    I naively asked, "well cant do you a SQL query of this list of names". "SQL, what's SQL?" they said. After a long pause I said it was IBM's strategic database which runs on VM, and does databasey things, like queries and updates. End of conversation - and we all went home.

    I went in on Sunday, created a DB2 database, an ISPF front end (which has support for tables) and about 200 lines of glue code in rexx.

    Monday morning, I went in and said to the tools team - this is the sort of thing I was looking for. I did this in under a day!

    Tuesday I was up in front of my boss for upsetting the tools team - apparently after I left one of them burst into tears.

    Wednesday I was up in front of my bosses, boss who quietly said - well done - next time just be more tactful when you tell someone they've wasted a year reinventing the wheel.

    1. ibmalone Silver badge
      Joke

      Re: At least they used a database

      One manager said he wanted a list of defects of people working for him

      Don't we all?

  12. Anonymous Coward
    Anonymous Coward

    the tree of life....

    1. He uses pen and paper and is a luddite, but I use Excel

    2. He uses Excel which is no way suitable, I use Access.

    3. She uses Access which is a nightmare, I use Oracle

    4.He still thinks relational databases are a good idea whereas I know the future is unstructured databases

    5. I can't believe that you are all using a computer for this stuff - I just keep a tally on an abacus.

    [slot yourself into the hierarchy - but don't ever think it isn't circular)

  13. adfh

    Filemaker Pro anyone? :)

    1. Captain Scarlet

      Special Reserve used to use Filemaker pro, ah uploading stock takes on those crappy Mac's I do not miss.

    2. Anonymous Coward
      Anonymous Coward

      Superbase? Relational databases and a GUI - I built a GIS (of sorts!) in that one!

  14. Evil Auditor
    Mushroom

    To be fair, Microsoft Access does have its uses.

    Indeed, it has: killing my nerves, breaking my will to live. Didn't find any other use and I'm unwilling to investigate any further. Nuke it from orbit! and so on...

  15. wwwd

    Does you does or does you don't take access?

    Access is my flexible friend

    1. Dave559 Silver badge

      Re: Does you does or does you don't take access?

      Wow, that's going back quite some way!

      (I can still recall the advert, though, clearly someone did a good job there (and, as for the relevant part of my memory: re-record, not fade away; re-record, not fade away…))

  16. Chris G Silver badge

    Simon says

    A true BOFH would have invested the surpluses thrown up by Access into a pub, the shortfalls would have gone into the evidence file kept on the actions of the OM.

  17. ForthIsNotDead
    Thumb Up

    Don't dis Access :-)

    In 2006 I wrote a timesheet management system with a web front end (classic ASP) and an Access back end. Contractors enter their work hours into the system and it generates their invoices for them and they get paid. It's very simple. It doesn't tie into the office back-office systems or any of that nonsense. The office staff look at the contractor-generated hours/invoice, validate the hours, pay them, and mark the invoice as paid.

    They all love it because it's simple, doesn't require anyone to install any software, and works in the browser on desktop and mobile devices.

    They are still using it. It's survived numerous server upgrades over the years but its still running just fine. Yeah, I should have used SQL server or something, but they wanted something cheap. And since it's been running so long there is no way they'll pay for it to be upgraded to a different backend! Their argument: "It's been running for 14 years. There's nothing wrong with it!"

    If I ever do get tasked with upgrading it, since it's all really simple Access tables, it will be a piece of cake.

    I'm quite fond of Access to be fair. Though I never used it for the Forms/VBA stuff ever. Just used it as a data store and query engine.

    1. Captain Scarlet
      Thumb Up

      Re: Don't dis Access :-)

      Have an upvote, done right Access is fine and I saw it being used in plenty of commercial software.

    2. N2
      Thumb Up

      Re: Don't dis Access :-)

      Yes, agreed done right its a good choice, Ive written a few Access databases in my time and so long as you take a little care to get it right it works very well.

      I managed my old Ltd company invoicing, VAT and acountancy etc with it for many years, without any problems.

      But i can see where it could be a real 'mare - delayed writes for example - oh dear! But maybe thats fixed now.

    3. Anonymous Coward
      Anonymous Coward

      Re: Don't dis Access :-)

      Agreed. I recall from back in 1997/8, we needed a system to report supplier capability and performance - up to 3000 suppliers for around 40 customers sharing the system (and the majority of customers had turnovers in the billions of $). It would be shared, initially, by floppy-net - a 3.1/2" floppy with updated data sent monthly to each customer who would then install it on their own servers; input would be at a central office. Whilst the long-term idea was for the whole shebang to be run online via www, the concept had to be proven first. Back then, internet access speeds hadn't reached the stage where it could be viable - locally, we actually had a local network running within the city.

      Where this is leading is that the whole system was run on Access. I didn't program it (way beyond my capability) - I was just of the engineers tasked with leading the overall project on how it would be used. However, once commissioned, I "inherited" the task of tweaks and fixes as management didn't like the charges being levied for ongoing maintenance by the supplier. The database was a marvel of VBA - I particularly liked the modules that created on-screen graphs of supplier performance data overlaid on the corresponding capability data, especially where colours changed according to various other factors. It was run for a couple years and, when the fully online version was released, a lot of it mirrored the Access version - and still does, over 20 years and several rewrites later. Fortunately for my sanity, I left that contract a long time ago.

    4. Anonymous Coward
      Anonymous Coward

      Re: Don't dis Access :-)

      correct tools for the job, when its the correct tool there's nothing wrong with using it!

    5. PatH

      Re: Don't dis Access :-)

      Access is a RAD (Rapid Application Development) tool. It is NOT a database engine. Your website used either Jet or ACE. It did NOT use Access. In fact Access was probably not even loaded on the server.

    6. dajames Silver badge

      Re: Don't dis Access :-)

      I'm quite fond of Access to be fair. Though I never used it for the Forms/VBA stuff ever. Just used it as a data store and query engine.

      Very true ... but if you don't need the front end stuff you might as well use SQLite.

  18. Blofeld's Cat Silver badge
    Coat

    Hmm ...

    "... has frequently been found rammed into the most distressing of places ..."

    Sometimes still in its original packaging.

  19. goldcd

    As a student I had an occasional job for Virgin Megastore.

    Periodically they did a stock take of all their stuff - so they'd flood the place with students looking for a bit of cash for a nights work.

    You'd get a print out of what should be there in a section of alphabetised CDs, and note down anything that wasn't there and fish out anything incorrectly placed there.

    Notional idea was to spot 'shrinkage'/what had been stolen, but often as not you'd find excess stock - but as there was no way of adding stock back into the inventory, it just got given away as a bonus to us at the end of the evening.

    (and suspiciously entire stock of some albums, seemed to have managed to distribute themselves randomly across the store - meaning they both got recorded as stolen, and also distributed to the stock-takers as excess stock)

  20. Pascal Monett Silver badge
    Flame

    "Ever been faced with an over enthusiastic manager with [..] not quite enough, IT skill?"

    All the damn time.

    If you don't know how to write the code, don't tell me what I'm supposed to put in it. I don't care about your little pet, I just want to get the job done right.

  21. Ian Johnston Silver badge

    He ran a branch office, which employed 20 people and was responsible for supplying custom servers, built in-house, to connect customers to the outfit's POS network.

    That's admirably frank. Unless "POS" means "Point of Sale", in which case it's boringly factual.

    1. Stephen Wilkinson

      That's how I read it at first reading too, then realised...

  22. Spacedinvader
    WTF?

    When excel would have just been

    Columns

    Item Name Current Stock - Shit Out + Shit In = Total

  23. J.G.Harston Silver badge

    charcoal and cellulose based record keeping

    One of my summer jobs in the '80s was similar. Management wanted all technicians to use the computer to check for stock, and then book it out through the computer before going into the stock room. "So the computer knows what stock we've got and we can use it to order stuff". Spent a happy summer coding up the system. Of course it knocked a hole in productivity as everybody had to queueueue up at the computer to check out a resistor.

    When drafting out the spec I did point out that a more practical solution would be for the techs to just go to the stock room for their bits, and scribble down in a notebook tied to the door what they'd taken, and at the end of the week enter the notes into the computer. But no, "computers!".

    1. Anonymous Coward
      Anonymous Coward

      Re: charcoal and cellulose based record keeping

      Working at a big plant in a big industry, it's pretty common for small items (nuts and bolts, small pipe fittings, etc.) to be in bins and "free" (take what you need, no records needed), with a periodic count and reorder by the stockroom. The price of each item was typically less than the cost of paying a tech to record how many they took, against what work order, etc.

      Bigger items are logged, either with the "notebook tied to the door" approach or directly into the computer system.

      1. Evil_Goblin

        Re: charcoal and cellulose based record keeping

        Yup and if you're really snazzy those bins are translucent with a "scale" down the side, (usually a big line in permanent marker) and once a week/month either the person responsible for the area or the supplier rep comes in, does a quick eyeball round all the bins and orders/delivers more for any that are below their line.

  24. jimmyonthespot

    "Tech whizz" special

    Once I worked at an academies trust where one of the senior teaching staff was a tech whizz (according to all and sundry).

    He'd set up an access database that the organisation used for collecting staff appraisals, which were used by staff to receive to pay increments.

    During a pay awards season, rumours spread that the appraisal system had gone wrong and that data was missing. Next the HR director came to me, a coder, for help with it, the whizz had helpfully told her that it'd be easy for me to fix it.

    Investigating the issue I discovered there was an access database on a file share, and the folder that was supposed to contain the database actually contained around 120 copies of the database:

    appraisal.mdb

    Copy of appraisal.mdb

    Copy of Copy of appraisal.mdb

    Copy of Copy of Copy of appraisal.mdb

    Copy of Copy of Copy of Copy of appraisal.mdb

    Copy of Copy of Copy of Copy of Copy of appraisal.mdb

    Copy of Copy of Copy of Copy of Copy of Copy of appraisal.mdb

    you see where this is going..

    File or database locks caused by excessive concurrent users had caused the database to create copies itself, to make sure no data was "lost", I assume. I could never understand the logic of this, but there we are, read the warnings on the label.

    The whizz was clearly aware this had happened, but funnily enough wasn't keen on sorting out the mess. Suffice to say, neither was I and the HR director agreed, thank goodness.

  25. nxnwest

    Underenthusiastic

    I consider myself an under-enthusiastic manager with more than enough IT skills to call bullsh*t on a lot of overly optimistic and over enthusiastic Devops/DB and IT people who never deliver.

    1. ForthIsNotDead
      Pint

      Re: Underenthusiastic

      Underenthusiastic. I'm stealing that. In compensation I offer you this upvote and this beer! -->

  26. Mage Silver badge
    Flame

    To be fair, Microsoft Access does have its uses.

    No, it doesn't. It's rubbish compared to MS's own free MSDE based on SQL.

    SQLite is better.

    Access should have been buried about 15 years ago.

    1. A.P. Veening Silver badge

      Re: To be fair, Microsoft Access does have its uses.

      Access should have been buried about 15 years ago.

      That would have created a toxic waste hazard, it should have been cremated at that time.

  27. Annihilator Silver badge

    Costs

    You know the usual argument - small Access database grows to become a critical office system. So you look at hosting it properly and agree there is no budget available for doing that, so it carries on, running off a fileshare being used concurrently by almost 50 users as part of their day-to-day job.

    Team I was in had this exact situation. It was deemed so critical to the running of the team (which in itself was part of the service management world) that they hired a "database administrator" and an assistant to manage it. I suspect that was close to £100K a year in staff costs to manage it (cost to an organisation of a salaried employee is usually around double their salary when you consider NIC, pension, facilities etc), all because no-one could stump up the £50K needed to port it somewhere.

  28. ByterBit

    In between dBase, Clipper and Foxpro and my current toolkit of Python, Node, PHP and MYSQL, I lived in and made a living in Access. For scores of clients I wrote hundreds of large and smalls apps; it was good kit for Windows machines on a LAN.

    I can’t resist taking another shot at Microsoft and tell you that they trashed it starting around 2007 – they fiddled with the menu and file structures and made it harder to do serious work in; something analogous to what they are doing now with Win 10. No one at MS seems to care about users getting work accomplished -

    1. BHetrick

      That's because their customers are not the users. The customers (senior management) they wow with eye candy that visibly (although not effectively) supports the data generation, consumption, and communication needs of the organization. The users are left to beat the mass of data destroying, bug ridden, user hostile misfeatures into something approaching utility in getting the day to day work done. Other office suites failed primarily because they valued the user over the customer, and look where that got them. It's actually quite brilliant.

      1. Terry 6 Silver badge

        The customers (senior management) they wow with eye candy that visibly (although not effectively) supports the data generation, consumption, and communication needs of the organization. means they can get the job done with fewer, cheaper staff. FTFY.

    2. ICL1900-G3 Bronze badge

      Foxpro

      Underrated and very fast. In its day, it was outstanding.

  29. Paul Cooper

    SQL in Access

    I sometimes use Access, either as a front-end or for tiny jobs.

    My pet hate is the way it accepts SQL, but then mangles it into its own dialect of same. It DOESN'T retain what I typed, which is what I want it to do!

  30. Filippo Silver badge

    Inventory tracking

    In fairness, getting the tracking software and the real world to reliably agree on what's in the warehouse is, generally, substantially harder than you'd think. Even when the software isn't particularly buggy, if the users haven't used a tracking program before, chances are they have all kinds of special cases where stuff gets moved without being reported. At least half of these special cases won't have been mentioned to the developer during analysis, because everyone thought they'd be obvious (no, they aren't), or because the guy who handles those cases wasn't present at the right meeting, and nobody else in the entire company knows those cases even exists. Then there's the special cases that only happen once every couple of months, and so we agreed they'd be handled manually, except it now turns out they happen ten times per day. Or people will just forget to scan an item, and then forget to tell anyone. Then there's the 70-years-old worker who just won't use the program. Or the manager who won't tolerate anything that slows down ops, and so will veto any attempt to add any verification/confirmation steps. Etcetera, etcetera. The idea that it takes several months from deployment until the inventory actually matches reality is not out of the ordinary, really.

    ...which is why you shouldn't make it even harder on yourself by using a subpar DB.

    1. Terry 6 Silver badge

      Re: Inventory tracking

      You omitted my ( two) favourite(s). That the inventory wasn't right before they started. Has never been right and is never going to be right. Because for years and years no one knew what they were ordering or how much, or where it was put in the stockroom. So there could be 15 years' supply of left handed widgets under a pile of grommet wranglers at the back, but only three months' worth of right handed widgets because everyone thought there was another pile in the boxes under the spare springs that were stacked on top of them when they had an unexpected order cancellation four years previously.

      The other one being that there were twice as many knurdle splungers as everyone thought because some of the staff call them furdle splungers instead and keep ordering more because they keep getting told they haven't any in stock.

  31. dak
    Facepalm

    Not Just Acces

    Way back last century, the toolroom in the factory where I worked had a god-awful scheduling system and a very enthusiastic tooling engineer, who brought in a copy of Superbase and built his own system. It worked quite well for them and kept entirely under our radar.

    We (DP) found out about it 55 weeks after it went live, and about 30 weeks after the engineer went on long-term sick. He had, of course, omitted a full date specification and the previous year's jobs were popping back up again. Could we please, fix it for them, please, please?

    Of course, we couldn't, at least not quickly.

    Part of the reason that it took a couple of weeks to alert us is that for a while the toolroom was legitimately quoting delivery dates into the factory in week numbers up to 66.

  32. Vaughtex

    Happy days

    It's also great fun when you become "service owner" to an Access database created by someone else, because your IT team has been told to take ownership of all the end user applications. Then the using department comes along wanting changes made and you discover that this single database isn't quite so single, it has at least 17 other hanger's on pulling data from it, 30+ text files it gets every morning from the other systems dragged down by FTP, as well as the other end user databases it also takes a feed from. Then there comes a point where you start hitting the 2Gb file size buffer and end up having to split an already split back end, without affecting any of the hanger's on, closely followed by the "we can't access this from our off-shore site". It was all good fun whilst it lasted.

  33. Zippy´s Sausage Factory
    Windows

    Access is great... as a cheap front end to a proper database system. In fact I made a good living using it in exactly that way for a good few years.

    It's still a good tool provided you (a) know what you're doing, (b) don't misuse it and (c) don't rely on it being the sole point of reference for whatever it is you want to do.

    Anything more mission critical than that feels like a severely career-limiting move to me.

    And don't get me wrong - I love Access...

    1. Down not across Silver badge

      Access is great... as a cheap front end to a proper database system. In fact I made a good living using it in exactly that way for a good few years.

      Not it bloody isn't. Not sure if it was ODBC or something in Access in itself, but I saw it locking whole tables (instead of locking just the page) causing endless issues in multi-user databases when people used "access as a front end".

      Burn it on a stake.

      1. AndyD 8-)&#8377;

        Access is great... as a cheap front end to a proper database system.

        ... as long as the proper database is locked down solid with error-checking 'instead of' triggers and encrypted double audit trail logs that record user Id's

  34. TeeCee Gold badge

    If you really want to piss off an Access junkie: When asked for help, design your query by swapping into SQL entry and just typing it in. Then swap back into the default query view and hand it back to them.

    With a bit of luck, their head will explode when they see what it looks like.

  35. LaurieA

    It’s not the database, it’s the amateur at fault

    As an Access developer since 1993, I found your article to be amusing but misleading. The problem in your story is not Microsoft Access but rather the overly enthusiastic amateur who wanted to play with his new toy.

    A professional developer would have consulted with the stakeholders and management to see if this tool was even needed or wanted. In this case, the answer may have rightly been “no thanks” and the story would have ended there.

    But supposing there was a need for a system that could be used by more than one person at a time, said developer would have collaborated with the stakeholders to produce a robust, well-tested system that made their jobs easier instead of more difficult.

    After many years of professional development, we have a myriad of clients who are highly satisfied with their Access or Access/ SQL database systems. We have often rescued “home-grown”, buggy databases and transformed them into useful and reliable tools.

    In one case, I was brought in to an office to fix a specific problem with a home-grown Access database. It was so poorly written that it barely qualified as a database at all. When I saw the steps needed for a single process, I spontaneously asked them if they were planning to hire an additional employee in the next three months. Yes indeed they were.

    I recommended that instead they hire our company to implement a revised database and they agreed to do so. The new system worked so well that they were able to increase their business by 25% without needing to hire a new person.

    While your story has its comedic moments, it unfairly maligns an excellent database instead of identifying the risk involved in allowing folks with no background in database design to have free rein.

    There are countless stories of expensive systems that were purchased and abandoned because the system was buggy or did not meet the company’s needs. This is a problem not unique to MS Access. It is generally due to a lack of rigorous needs analysis, poor coding practices, a dearth of testing or insufficient user training.

    Developing an easy to use, stable and reliable database system is a high end skill. Hire a professional developer to get the best results.

    1. ibmalone Silver badge

      Re: It’s not the database, it’s the amateur at fault

      I was with you right up until "excellent database", let's not over-egg this. Most databases don't helpfully refactor stored procedures into broken ones with faulty syntax.

    2. N2
      Pint

      Re: It’s not the database, it’s the amateur at fault

      We have often rescued “home-grown”, buggy databases and transformed them into useful and reliable tools

      Me too, but sent a shudder down my spine at some of the horrors offered up.

      Pint for your efforts

      1. LaurieA

        Re: It’s not the database, it’s the amateur at fault

        Thank you for the pint! Yes, indeed - there have been some extremely gruesome databases that essentially had to be completely rewritten.

        My absolute "favorite" was one where the Primary Key was a long text string. So the code was full of hard-coded references to so called PK's like "Holland America Cruise to Anchorage".

  36. martinusher Silver badge

    Maybe a hasty judgment

    I'm not a great fan of Microsoft's software but I think this is being a bit unkind to Access. Access is really two piece of software, one being the actual database management system and the other a pretty lightweight database management system. My experience with Access is that it lacks the locks and journalling necessary to build a proper transaction processing system -- its the sort of thing that will handle a mailing list but would not be optimal for a warehouse inventory system. The management system and report writer part is 'meh', it works, and I suppose it would be tempting to embed Visual Basic in it to make an application-like piece of software. So the problem here isn't Access or even the individual's ability to write code, its the lack of understanding of system requirements and database properties. The fellow would make a pig's ear of the job regardless of the DBMS he used. Microsoft's original sin is providing tools to enable the misuse of Office products, an outstanding example of the Road to Hell being paved with good intentions. (The article's right in the sense that the most abused piece of software out there is probably Excel -- I've seen people use it for all sorts of non-spreadsheet tasks. Its one of those "If all you've got is a hammer then everything looks like a nail" jobs.)

  37. Binraider Silver badge

    Access' original purpose has, of course, very long been forgotten. Tool to "access" remote databases and cut up tables for downstream use. Other obvious tools to do the same job might be things like TOAD. When used that way, they can be pretty useful and accessible compared to say, hacking something together in PANDAS.

    But using Access as an actual DBMS, god, the nightmares. Or porting it to MS SQL server. Just don't do it.

    The loss of data integrity and spreadsheet / access morass has, on occasion, had me thinking of looking for new employers. Grass isn't always greener though; and indeed every business I've every touched has been tainted by "rapid application design", code for bodging stuff together with the wrong tools.

    1. Anonymous Coward
      Anonymous Coward

      "rapid application design"

      How about Rapid Application Tool Set?

  38. PatH

    Blame Access for everything

    Really? Blaming Access for the incompetency of the developer is like blaming the lawn mower for mowing down your prize roses. But the rant made you smile and feel superior because you don't use such a toy.

  39. John Smith 19 Gold badge
    Unhappy

    Oh $deity

    That really takes me back.

    And not in a good way.

    Manager -- MS Access training course --> Just a small app for 1 person --> App for X people. --> S**tstorm.

    We were mostly an iSeries house. You simply don't have to worry about stupid s**t in DB/400 that regularly came up in Access.

  40. Angry IT Monkey

    My first official IT creation was a Lotus 123 file that read in and parsed an EDIFACT file, then produced another for input back into the same system to complete certain orders. Things that weren't physically sent out like warranties.

    The second was another 123 file that took supplier and order numbers pasted in from a DB query in Approach and created a macro for the CICS system to automate completing orders. Before that most of the floor became highly paid data entry clerks on Friday afternoons.

    The company was quoted my annual salary and 2 weeks for the first and I quoted a week working from home. I got 2 days in a quiet office on another floor. I'd used Scottie's rule of doubling estimates so still got it done.

    I made the second because keying in numbers for hours on end every week isn't fun. Got no thanks from TPTB when they found out but a lot from my immediate manager and fellow data entry draftees.

    Sometimes you have to cobble things together from what you're given.

  41. Anonymous Coward
    Anonymous Coward

    ironically, just today I heard a senior tech lead announce they were building an internal database using Access. I thought it died so long ago that the installation files could only be found using the staff of Ra while standing in the map room at Tanis! I guess what's old is new again. But I got time off for my ribs to heal after breaking them laughing so luckily I won't have to work on it!!

  42. Zola
    Meh

    This brings back a bad memory!

    I worked in the London branch of a US investment bank, in the IT department, and the Ops department had hired one of the Y2K COBOL "consultants" we had been using prior to Y2K to "knock them up a system to help record certain compliance issues". So this all took place just before Y2K (Oct/Nov 1999) as the consultants were all looking for their next gig by then and had obviously convinced one of their old users (who had been system testing Y2K) that they could help them out with some of their more mundane reporting issues.

    Apparently, development of the system had gone swimmingly and the consultant delivered the system (with no input from IT, this was all done behind our backs) and the Ops department - which had a mixture of UK and US staff - diligently entered their data for a few months, and the consultant left the scene rolling in dough.

    Shortly after, early Jan 2000, the system began misbehaving - the reports weren't making any sense. Could the IT department send someone down to fix it?

    We had a brief internal discussion within IT, mostly involving the phrases "fuck off, I'm not touching it", "no fucking way am I taking a look" and "they developed it, it's their problem!" until finally I got given the short straw and talked into taking a look, but that if it couldn't be fixed relatively quickly then I should leave it alone as it really wasn't our problem (apparently the Ops department had been warned about this sort of shenanigans in the past - mostly using massive Excel spreadsheets that nobody could support).

    So I went down to Ops. Asked to see this system. All based on Access 97 and Forms. I quickly ascertained what it did, and what the problem was. In a nutshell: "A lot of the dates are wrong".

    I began looking at the data, and deduced that the Access 97 database being used to store the application data was using a TEXT column for all of the dates.

    You can all guess where this is going... right?

    Some of the users entering the records had their PCs set to use UK dates (dd/mm/yyyy) and other - US users based in our UK office - had their PCs configured to use US dates (mm/dd/yyyy). Sigh.

    "01/04/1999" as stored in the database could have been 01-April-1999 or 04-Jan-1999. Was "01/04/1999" entered by a UK user the same as "01/04/1999" entered by one of our US visitors? Yes? No? Maybe? Who could possibly tell anymore?

    And, due to the total absence of any audit trail, it wasn't even possible to know which user had entered which records (and thus dates) so matching US users to specific mm/dd/yyyy records - which might have been the only possible way to unfuck about 50% of the dates back to a sensible dd/mm/yyyy date format - was entirely impossible.

    I swiftly threw up my hands and said the existing data was now garbage, and couldn't be saved. In future, make sure everyone has the same date format on their PC when using this system. Bye!

    As for how it ended, the US investment bank was bought out by a European bank about a month later, and the Ops team (and their noddy system) were made redundant. That, as it turned out, was a lucky escape!

  43. Zarno Silver badge

    I meekly raise my hand to confess...

    That I use Excel as a calculator and equation solver more than anything else.

    =sum(a1:a23)*b3, yada yada yada. Great for when I want to plug in different values to something.

    I also use it for D&D character sheets, where I want to be sure the math for everything is proper.

    1. TWB

      Re: I meekly raise my hand to confess...

      Don't worry, I used LibreOffice spreadsheet to design a brick wall recently - I did not have enough pieces of Lego....

      Oddly not a single number on the sheet, I counted the bricks off the display then did the maths with pencil and paper to decide how many bricks to order.

      As others have said, you use the tools you've got and this was the best way for me. It also allowed me to show my client different designs much faster than if I had draw each out on paper..

      1. Anonymous Coward
        Anonymous Coward

        Re: I meekly raise my hand to confess...

        That's quite clever, actually. Good work!

  44. Anonymous Coward
    Anonymous Coward

    Other slassic migration fuckups

    Would be the migration of ********* users from SAP to Junifer that flushed all kinds of shit out of the woodwork and got a bunch of customers some very big bills.

    Annon because.

  45. herbgold

    May I copy a comment I made in Quora a little while ago.

    The question was, "What are some of the uses of Microsoft Access?"

    The only real-life development using Microsoft Access I’ve seen was a complex booking system for patient therapy appointments at a small charity I used to volunteer at. It was “cleverly” written by a non-professional developer, and it used common shared variables instead of explicit parameters, which made it a nightmare to debug.

    Because Access is designed for a single user and has no built-in locking, the development had to go through hoops to stop multiple concurrent users trying to update the same data (effectively inventing his own locking system). Because Access lumps everything (data tables and code) in a single module, it was very fragile and often fell over.

    Access should be OK for a single user system (this is what it’s designed for) but for the type of system I’ve described I would run a mile from it.

    1. Anonymous Coward
      Anonymous Coward

      I think you fall in the trap of basing your opinion of Access on examples of 'bad' database design in Access

      1. There's no need to lump data and code together in a single module. Front end/back end style development has been recommended since the day it launched. We often protype in access and then migrate the back end to Oracle or MYSQL or POSTGRES - which as long as no one at either end decides to be too clever is usually painless and fast.

      2. It's wrong to say that it has no built in locking. I've always found the locking perfectly adequate for 'sensible' numbers of users, where sensible is 10 to 50.

      3. Access's great strength is it's ability to put together data entry and especially browsing tools that are maintainable, don't require users to have a degree in SQL and don't look like they have escaped from an 80 character phosphor screen.

  46. Anonymous Coward
    Anonymous Coward

    My first job out of uni had a fair bit of MS Access. A lot of asbestos reports (required for legal compliance purposes) were powered by these DBs.

    To be fair, it worked. The code was clunky and it wasn't great, but it worked. And we did migrate off these Access DBs to a Java / DB2 based solution.

    Hi to anyone who might realise what company this was and who I am!

  47. Gene Jones
    IT Angle

    The people I worked for 15 years ago banned Access databases. The problems cropped up when creators of the dbs decided to leave for greener pastures.

    1. David Hicklin

      Another common case is the "production critical" database sitting on someone's desktop PC - until the hard disk fails.

      Backup? what's a backup?

      1. Terry 6 Silver badge

        Some of which start off as "I'll create a tool for myself that will help me do X".

        But then some manager decides that such tool or its improved efficiency is just what we need for the whole department. So the poor bugger who made it gets told to roll it out to the entire department, probably without any extra resources or time, and usually working from their own desktop.

        Possible the managers decide that it needs a pro to do the job and get someone in who is a) cheap and b) barely trained himself who will take over the project, fail to understand it and create something that even the original user can't err.... use.

  48. DBA-ONE

    I've seen companies trying to get off the ground basically run their business with Access. Usually this involved using a real database such as SQL Server to store the data rather that the corruption prone native DB. I wrote a report scheduling front-end using this formula only because I had doubts about creating a web-based app that functioned correctly within the time constraints. I've seen a lot of the "look what I made" thing with Access and it usually has the same problem - a real application isn't simple to create. There is no error trapping, no validation to keep bad data out. No all or nothing transaction-like deletes for records in multiple tables , etc. The list goes on. I liken it to complex reporting in that many software vendors promise great things with just drag and drop, but this is usually not true. In the end you have to know how to code.

    1. Anonymous Coward
      Anonymous Coward

      Real applications aren't simple to create, but none of you criticisms are really about Access , they are about poor application development. The reason you find the 'look what I made' Access apps is usually because when the user asked for a tool to enable them to do their job, they probably got told 'oh you don't want to do it like that' and then promised a world beating system that they know will be approximately as rubbish as the Access one, but cost 50 times more and be delivered in 3 years time, if you are lucky.

  49. sketharaman

    Access or SQL Server or SAP HANA - Bugs Never Go Away

    Why single out Access? Bugs happen in Inventory - and lot of other places - even in SQL Server, Oracle, HANA, etc.

    An ERP vendor that used SQL Server found that the software failed to update the stock status automatically after material was issued from the warehouse. Engineering said it would take a couple of quarters to fix the problem. Obviously Sales couldn't stop for six months, so it devised its own workaround. During demos to prospective customers, the presales consultant would raise a Delivery Challan on the system and shout "We have raised a Delivery Challan for 30 units" twice or thrice. More than a few prospects used to wonder why the presales consultant was so excited about such a mundane transaction. Little did they know that his or her voice had to carry over to the next room where a developer would run a SQL Query to manually update the stock by 30 units!

    Coming to SAP HANA, there's this well documented horror story where German retailer Lidl wrote off €500M because the database couldn't handle a change in Inventory Valuation from Selling Price (SAP's standard) to Purchase Price (Lidl's requirement).

  50. Gravesender

    What I did in the Army

    This reminds me of my time in the US Army back in the late 1960s. I was assigned to a second echelon electronics repair section of a training unit at Ft. Benning GA.

    At the time policy dictated that our supply room have in stock exactly the number of each item as specified in some document or other. The supply room was subject to regular "surprise" audits and there would be hell to play if any of the prescribed quantities were off in either direction. (I put surprise in scare quotes because for some reason or other our supply sergeant always knew about the audits a day or two in advance.)

    There were two consequences of this arrangement. The first was that we were never allowed to draw spares from the stock room. If we needed so much as a new fuse we had to fill out a complicated legal size multi-copy form and send it up to Brigade. The item needing repair would sit on the shelf until a week or so later when the single fuse would arrive packed in military grade multi-layered moisture and mold resistant packaging. If the fuse blew again when we tried it we went back to step one.

    The other fun bit was that the stock room had to carry excess inventory of some items to keep the unit running at all. When word of the audit came down two guys were s=dispatched to the motor pool to check out a large truck. All the excess inventory would be piled into the truck along with a case of C rations. The drivers were instructed to disappear into the boonies for a day or two until the audit was over. Great fun!

  51. DenTheMan

    Suckers !

    In my time I have come a aross Excel database that cause many a disaster and keep employees in full time work that 10 minutes of Access work could solve.

    And Access as a data query tool linked to SQL server has been known to save 100s of man hours too, it integrating across multi platforms in many an interestingly way.

  52. Dave559 Silver badge

    Altered reality

    I love that their work-around was to alter reality to match what the "database" thought they had in stock.

    That's a solution worthy of a few beers, at least!

  53. John Smith 19 Gold badge
    Unhappy

    *all* applications can be made much smaller if.....

    a) The user knows exactly what they want to do at all times.

    b) The data they are provided with is correct at all times

    c) They never make keying or pointing mistakes

    d) It is guaranteed that no two users will ever access the same record at the same time.

    I've never seen a code analysis but IMHO 75% of all code is about not what the program does, but what it has to stop the user doing, or fixing it when the user gets it wrong. BTW just because you can't see the ton of code managing record locking in a proper DB system does not mean it isn't there.

    In my universe a) is uncommon, b) is a fantasy c) is absurd d) is a distant dream.

POST COMMENT House rules

Not a member of The Register? Create a new account here.

  • Enter your comment

  • Add an icon

Anonymous cowards cannot choose their icon

Biting the hand that feeds IT © 1998–2021