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 …
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.
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.
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..
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
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.
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.
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.
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.
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...
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.
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.
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)
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.
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.
"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...
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.
"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...
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.
(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".
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.
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.
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...
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
This post has been deleted by its author
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.
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
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
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.
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.
"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.
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.
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.
"... 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.
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.)
(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.
(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.
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.
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.
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 :)
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. 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)
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.
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.
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.
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)
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!".
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.
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.
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.
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.
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 -
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.
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.
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.
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.
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.
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...
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.
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.
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".
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.)
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.
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.
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.
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!!
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!
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.
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..
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.
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.
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!
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.
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.
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.
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).
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!
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.
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.
The Register 
Biting the hand that feeds IT
Copyright. All rights reserved © 1998–2024
