- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Surely they're Backup tapes.
Admin
This WTF does not pass the smell test.
"Access" could not be used as the datastore for an application this large. Leaving aside that "Access" is not a database engine (Jet is the database engine that Access uses by default), but a RAD front-end development tool, the fact is that Jet MDBs have a hard size limit of 2GBs. There is no way an application of this scope could store the amount of data described in 2GBs or less.
Thus, it's quite clear that if Access was used at all, it was as a front end to a server database (MS SQL or Oracle or whatever).
Access is a very good RAD front end to MS SQL, for instance (and works very well for other server dbs, too). It can scale as large as you need, as long as the developers know WTF they are doing. True, if you're a moron, you can create a hideous app that won't work well and corrupts data. But that's the case with whatever development language you use to build the front-end application.
Access has the advantage of being specifically designed for the purpose of building database applications, and has excellent documentation in how to develop front ends for large-scale enterprise databases. If you don't use that documentation, then it's not the fault of Access if your app doesn't turn out well.
As to the question of stability/corruption/multiuser:
A Jet MDB has a hardwired limit of 255 users (yet another reason, BTW, that the story can't be true if it was intended as saying that the data was stored in a Jet MDB). That user tells you it is designed from the ground up for small-scale workgroup applications. And for that purpose, it is an excellent choice, especially for small multiuser apps (if you can't design an Access app with a Jet back end that works fine with 5 users, then you really are completely incompetent).
I know of Access applications that use Jet back ends that have 100+ simultaneous users. Yes, these apps are very carefully designed, and tend to do many operations unbound (instead of the default bound nature of Access) in order to enhance stability and concurrency. But they work, nonetheless, and their users are very happy with them.
My largest Access/Jet app (I've been developing Access apps professionally since 1995) had 10-20 simultaneous users and two central data tables of 350K and 250K records, and a table of secondary importance with 400K records. Performance and stability were fine. Retrieving records was near instantaneous once the application was loaded.
In the 7 years it was in use in that configuration (it was upsized to a SQL Server back end last year in preparation for a coming increase in number of users), there were maybe 2 or 3 cases where the back end MDB was reported as corrupt. In all cases, a compact restored the MDB to proper operation and not one byte of data was lost (the files weren't corrupt, just flagged as "suspect" because some process using the back end data file had not exited properly).
This is the standard history for Access apps that are programmed by experienced developers who know WTF they are doing. That is, excellent cost/benefit ratio, reasonable to excellent performance and virtually no downtime with absolutely no data loss, ever.
Would I propose a Jet back end for all apps?
Of course not. Only a complete moron has a one-size-fits-all answer to every problem.
But the vast majority of those commenting in this thread demonstrate by their comments that they are clearly completely ignorant of how to use Access/Jet productively. Most seem not to know enough about Access to even understand the basic distinction between Access (a front-end development tool) and what you choose as a data store for your Access application. If you don't understand that basic fact, then you really are pig-ignorant about the product and have no business spouting off about it, in private or in public.
-- David W. Fenton David Fenton Associates http://dfenton.com/DFA/
Admin
I always told my customers that Access is like a drug. In small quantities, it can help alleviate your pain. However, taken over time, it will kill you.
Admin
I remember with horror a production control system where the database was a MS Access 2.0 database. It was a real-time system involving thousands of database actions per minute. The number of clients were around 20 all accessing the db using ODBC.
The funny part was that they also installed an Oracle db but that was for the more "serious" part, the order forms.
German engineering at its finest.
Admin
You can access (pun intended) using ODBC, slow as hell but it works most of the time.
Nope, wrong conclusion
Access is a good RAD tool to develop a db and then use some more serious DB for the "real" database. Morons are everywhere though, especially in charge of design. I agree though that a carefully designed system could probably use access but then why not use a real db instead? Saves a lot of trouble.
Admin
http://en.wikipedia.org/wiki/Adwords http://xooglers.blogspot.com/2005/12/lets-get-real-database.html
Admin
If a company expects about $285,000 to be enough for building an ERP solution from the ground up, the new product is going to be a lot worse than the one described in this article
$50,000,000 and 5 years work is more realistic for an application where downtime costs $5,000 per minute
Admin
That attitude is probably why they haven't done anything.
What they really need is someone to say 'we get, say, 3 incidents a year that cost $285,000 each. If we spend $285,000 on a few developers for a year, we can migrate to a more stable backend DB, and it'll fail less than half as often. Then since we'll have a more stable backend, we can work on ironing out the other problems.'
The answer might not be perfect, but they'll save over $400,000 a year after the first year, more than paying for the work they've had done.
In fact, given that the current application is written in Access, it is probably not THAT complicated at all. It doesn't need to be a 'fit-all' ERP solution, just something for this particular company's particular warehousing & shipping needs.
Admin
Who cares? There were probably lots of old and unhandled exceptions in their current system.
Admin
[quote user="Soviut What are tapes? ;) (The real WTF?)[/quote]
Tapes can still be the best storage medium, depending on your requirements. For example, I have an audio book on tape of Rufus Sewell reading "Goldfinger" by Ian Fleming (brillant), which I listen to when I'm cooking. Now, when I turn the hifi off, the tape doesn't forget where I got to, or force me to remember the chapter etc. It just starts from where it left off, even if I take the tape to another room or to the car! Can't do that with CD. And I don't have to worry about how many Gb my player has - if I start running out of capacity, hey presto, I buy more at Tesco.
Admin
I call embellishment.
Admin
A rather rude and unintelligent reply considering you don't know the full context.
What if there was a tight budget? Maybe it's a short term solution for a system that has a limited lifespan anyway? Creating a full client-server RDBMS solution when one isn't needed is just as much WTF as the original story was. There have beem examples of these in this very site.
Admin
If your specifications for the MS workflow Foundation were done properly, I wouldn't be trying to force a square peg into a round hole and taking so much time developing this.
If the hole is big enough, a square peg will fit into a round hole.
Enough examples?
Captch "ratis". A rat is what?
Admin
Don't remind me!
When I started doing my first big DB project the people i had to build it for had that exact mind block, they knew that excel could not handle what they were doing, but they couldn't grasp that a DB was something completely different than a spreadsheet..
They was more concerned with the color of the buttons (especially which shade of green to use) than with the functionality or table design.... Argh
(Fortunately the people whom i work with now are much better as they know what a database is and what it can do and not do)
Yazeran
Plan: To go to mars one day with a hammer
Admin
Admin
And you STILL did not get the right shade of green! Atrocious! Would not buy for again.
Admin
Jesus fucking Christ. For the cost of one or two of these "hickups" and with the use of GPL software a single in house developer could build something that would most likely in the long run suck but at least would not crash the system. Ive seen Access with 2 milion records. It was not a pretty sight. It also was slow and broke in a breeze. And Ive also seen and built a MySQL based system for a very similar function that managed 47 million records without as much as a snivel. Access has a valid place in rapidly developing DB applications, its a quick and dirty GUI for linked tables from a real DB. But may God have mercy on the poor sod that uses it for anything else with more than one user and couple of thousand records. In this case Id investigate the possibility to move to a a linked table solution ASAP.
Admin
Admin
Admin
Unlike the author of the story - I am unwilling to risk my position to complain - but I wanted to touch up details, comprende?
Admin
The real WTF is that they're running something that critical on in-house software. A nice, modern, tested, supported warehouse management system for a shop that small (and yes, in the logistics world, that's maybe a medium shop at best) will run you between $80-200K, list. Much less hassle than trying to rewrite something.
Admin
Admin
They don't even need a new programming team. All they would have to do is copy the Access FILE to a seperate machine then import to a MSSQL instance then change the connection string.
Access is a good solution for a way to store data for a single-user-at-a-time usage for supporting basic data entry and providing data to excel and word mail merges. Before th days of SQL express it was a cheap way to test an application, but you have to know it's limitations.
The coder-in-question should have known about Access locking problems and copied the Access file instead of trying to connect and copy out a single table. Yes access was a bad choice but the TRWTF is the coders ignorance.
Admin
Pigs fly just fine, TYVM. RFC 1925 1.3
Admin
Won't work. When a company "gives" you a computer to do your work on and maintains it, pretty much all courts understand that they haven't given it to you as property. If you sold the computer your company "gave" you on eBay, you'd easily be convicted of stealing the computer.
Usually, there's a lot of corporate paperwork that makes this all clear, but few people read it. It's generally mentioned in the paperwork you sign when you take the job, but only included by reference.
When you "give" a computer to your girlfriend, even if you offer free tech support, there is no such understanding. In fact, the general understanding is that gifts given to girlfriends are proper exchanges of property. The only clear exception I'm aware of is engagement rings. If she sold her computer on eBay, you could not convict her of stealing the computer.
Your girlfriend generally doesn't sign anything to formalize your relationship. Any such documents generally don't include clarifications regarding who owns computer assets which you provide to your girlfriend. Most people who try to get their girlfriends to sign such documents quickly find they don't have a girlfriend.
Admin
Well, you'll usually find they did put the money in, yes $285k is enough for a good seniour level programmer and a few noobs, but usually they paid it (yes, the whole $285k and possibly more) to one reasonably well spoken ex used-car salesman who once read a "Learn Visual Basic 6 in 24 hours book".
I subcontract to a fairly large alarm transmission systems (the kind of thing that tells a bank if someone is trying t steal their ATM) vendor in Australia, and our competitors core networks are usually based on equally flaky applications. Our first iteration product was exactly the same. Finally we made it out of the woods, and have something workable, but it never ceases to amaze me how many companies are based on some moron's naive and unworkable implementation and the bullshit of an uncountably large number of clever marketers selling to slightly less clever procurement officers.
Admin
Admin
Admin
I've found a good cost-saving way to use MSSQL is to design your system to use webservices or WCF for it's data access layer then host the service on one of the $35 per month asp.net + MSSQL hosting companies.
Of course this only makes sense in an always-on internet connection, but if your buliding a web/smart client based app you have that restriction anyway.
This way you get not only cheap access to a decent DB they usually have a soild infrastructure for fail over and what not.
When usage of your app grows to the point where the web based service is too slow, it is easy to then transition to a local DB server by changing a URL or swaping out your proxy code to skip the WS part all togather.
Admin
And THAT right there is what makes me pull my hair out with regards to portable media devices. For the benefit of going digital, we've completely lost the most basic and sacrosanct of features like remembering where the user left off in an audio file.
I had a Sansa View for a while. It's really a very nice MP3 player. Most of my listening consists of lengthy podcasts. The Sansa lets you fast forward or rewind only 8 seconds at a time. It also doesn't remember where you left off. So I get half way through a 1 hour podcast during my morning commute. Then at my evening commute I want to resume the podcast, but first I have to put something heavy on the FF button and let it stay there for 10 minutes while it fast forwards.
Holding the FF button down w/ your thumb for more than 30 seconds is tiring and after 30 seconds of FF'ing you get like 12 minutes in.
You're right on the money. What I wouldn't give for some basic fundamentals to be understood by the current manufacturers of portable media devices. At least my iPhone always remembers where I left off...
Admin
That's why when I come across a WTF Access database I give its original developers the benefit of the doubt. We've all been in situations where someone gave us a ludicrous deadline to create something that everyone knew would have a very limited life span.
Then 3 years later and it's being used for something it was never intended.
So next time you come across an Access DB that makes you go "WTF?", think to yourself that maybe even the original dev thought to himself "This $hit is f*cked. Good luck to whoever takes this over"
Admin
What's with all the noobs thinking all it takes is some 7337 haxor to come up with a solution of this scale?
A total revamp done in house would require Business Analysts, senior developers/architects and programmers. That's not to mention the endless requirements gathering phase; a critical phase in any project this size. Then there's the choice of architecture: what servers to use, etc. Should this all sit on Sun boxes for the reliability or Dell rackmount servers running Windows Server 2003?
And chances are the UI will have to be updated as well so now you have to re-train the workforce. That takes time and has to be scheduled with their leaders.
Lest we create another WTF, a new backup system has to be put into place. Support contracts will have to be drawn up if the hardware is large enough to need them.
I could go on...
There's a lot more to these projects than just "OMFG letz me haxor teh ur fix"
Admin
He posted an equally lengthy rant a while back:
http://thedailywtf.com/Comments/Access-Abomination-.aspx?pg=2#169885
I have it bookmarked to this day. I have to agree with him. And his arguments have some merit.
Admin
I once rebuilt a mutual fund management system that had more than $300 million in assets. It was written in Access 2.0. I watched it die just as we where finallizing the new SQL Server implementation of the system.
Admin
Or maybe you exaggerated a bit and you know damn well that the architect could also be the business analyst and that any solution 2 or 3 programmers with decent experience throw together will be better than this WTF.
Or yet again, maybe you do all your design by committee and wonder why the rest of the business world is running circles around you. Good luck there at Initrode!
Admin
Except that you "gave" her the computer - it's not yours anymore. Now, if you install a logger on your computer and let her use it, you're just a soon to be ex-boyfriend.
Admin
The costs of building and transitioning to a new software solution keeps "short term solutions" in use long past their sell by date.
Admin
There's plenty of ways Access will fall on its face even if you use a MSSQL backend:
My favorite is to use big ints as primary keys. In another example of how Access has not kept up, it doesn't know how to use big ints on linked tables.
Background:
We use it mainly as a reporting tool and gave a customer select permission on their tables so they could run the kind of custom queries that managers are always asking for.
We have long since moved to winforms applications for real error handling, better plug in components, actual object oriented design, etc.
This app had one table with an int PK that was getting a bit large [mainly thanks to testing]. The next key was something like 1,027,337,369, and last thing we wanted was to overflow the primary key, which would be guaranteed to happen at 1am when I'm out of town. So we changed the key from int to big int. No problem, right?
Immediately, Access had no clue how to page through the data. It couldn't figure out which records had been pulled down and which were on the server. So it grabbed the entire 200 million record table every single time a user asked for any row in it. Big problem.
Yet another way that Access is way behind, well, everything else, even as just a crummy reporting front end.
Admin
You're wrong. I'm not saying it has to be one way or another. We don't know enough of the details to make that determination. Everyone's assuming it just takes a small team of programmers and that's it. I'm merely pointing out that there may be other factors which complicate this.
Design by committee is a slow painful death. Business Analysts can be a boost or a drain, just depends on the analyst and his experience. And as for the other requirements, projects can be complicated fast. Most of the time it's unnecessary scope creep. Other times it's because of a HUGE underestimation of the project done by incompetent management. It's those kinds of managers that hire 1 or 2 programmers and think that's all it takes when in fact that's sometimes a recipe for disaster. This company specializes in logistics. Don't tell me there's no room for a BA in a logistics company and that he shouldn't be involved in a revamping of their core system.
Admin
You're right that Access hasn't kept up. But your example doesn't refute the quote "Access is a very good RAD front end to MS SQL". In your case you're using very large datasets, even if they are in linked tables. As has been mentioned here before, Access doesn't play well with large sets of data, even if it resides in another database. Just think about executing a local Access query using several JOINS instead of creating a view server side and linking to that.
Admin
Admin
The problem isn't large datasets, the problem is Access doesn't know how to use a big int as a primary key, which is pretty amazing.
If there were 10000 records, not being able to seek over them would be a problem, but at 2 million, it is a show stopper.
The bigint was introduced in SQL 2K, eight years and three versions of Access ago, yet still Access doesn't know how to use one. That's being left behind.
Admin
If you're using ODBC, you can't connect to a Jet back end (Access prohibits it), so if you're using ODBC, you're not using a Jet back end.
Which is what I said in the first place.
Exactly how is it the wrong conclusion? If you know so much about Access, explain to me the steps, in any version of Access you like, that you use to create an ODBC connection to a Jet MDB from an Access front end.
You can't because it can't be done.
Thus, assuming more than 2GBs of data, my point is indisputable.
That's exactly what I just said, that Access is a great RAD front-end development tool, and that it can connect to any number of databases for the back-end data store.
What's your point, caller?
You still don't get it, do you?
Access = front-end development platform
Jet = the database engine that Access uses by default.
The whole point of my post was that Access is an excellent RAD front-end development platform that can be used with any back-end database platform, Jet (if you know what you're doing and have a sufficiently small user population), MS SQL Server, Oracle, MySQL, DB2, PostgreSQL, etc.
The fact that you posted a reply that just repeats what I said but with the clear impression that you are disputing me deliciously proves my point that most of the posters in this thread are pig-ignorant about Access and don't understand the most basic concepts of how it is designed and how to use it.
-- David W. Fenton David Fenton Associates http://dfenton.com/DFA/
Admin
Well, technically they're correct. Any information or data stored in an organized format of some kind is a database.
That's the difference between a Database and a Database Management System...
Admin
While I agree that the WTF as posted (which claims an "Access" back end, i.e., implying that the data store is a Jet MDB) isn't believable, you are wrong to think that Access/Jet can't work just fine with 20 users.
If you're having corruptions problems on a regular basis you have environmental instability of one of two major kinds:
software (e.g., certain AV products interfere with Jet's ability to communicate with the file system in a timely manner; software running on a file server can lead to corruption, too)
hardware (e.g., flaky NICs, bad network drivers)
A professional Access developer knows how to test for and resolve these issues.
Of course, the most common cause of corruption is letting multiple people open an unsplit application (i.e., a monolithic MDB file with all the data tables in the same file as the forms/reports/etc.). The solution is to split the app, with one back-end MDB with the data tables (stored on a server) and a separate copy of the front-end MDB (forms/reports/etc.) on each user's workstation. This configuration is (and always has been) the recommended configuration for running any multiuser Access application.
But somewhere along the line, an awful lot of people miss that basic fact.
-- David W. Fenton David Fenton Associates http://dfenton.com/DFA/
Admin
Yes, you're right that an Access MDB connecting to SQL Server via ODBC will not work with bigint primary keys (if it's not a PK, it's fine).
The reason for that is really historical, and due to MS's shifting internal agendas for its technologies and the place of Access. This is one of those artifacts of the "ADO Wars," as I like to call it.
Starting with A2K, MS tried to deprecate Jet and its native interface, DAO, in favor of ADO, its designated replacement for ODBC. ADO had a lot of really great features, but MS was suggesting it for replacing DAO in interacting with Jet data (which was bloody stupid).
In A2K, MS also introduced the Access Data Project (ADP), which was a different kind of Access front end that spoke ADO natively and could connect to a SQL Server and give you direct access to SQL Server objects without having to go through the primitive ODBC interface. So, MS put all its improvements for Access to work with SQL Server into the ADP.
Turned out, though, that ADPs had tons of drawbacks (many of them due to ADO and its tendency to make too many guesses behind the scenes, beyond the user's control), and MS couldn't seem to decide from version to version how ADPs should work. Things that worked in A2K ADPs broke in A2K2 ADPs. Things that were broken in A2K2 ADPs worked again in A2K3 ADPs. And things that had never worked in early ADPs sometimes worked in the later ADPs.
The result was that you couldn't upgrade an ADP to the new version and expect it to work reliably. That would have been OK if any of the three major versions of ADP worked reliably for everything, but they didn't. They required way too many workarounds.
Because of all of this, MS itself now deprecates ADPs (http://technet.microsoft.com/en-us/library/cc178973.aspx in the section titled "Access Data Projects (ADPs)") in favor of MDBs/ODBC for working with SQL Server.
Unfortunately, in the wake of the abandoned ADO/ADP plans, MDB/ODBC doesn't fully support everything in SQL Server, becauses it's old.
The limitation here is that Access uses Jet for processing ODBC data (Jet makes the decision about whether to hand off SQL to the server for processing or attempt to parse it itself, or in many cases, hand off part of it to the server and process the results locally), and Jet doesn't have a data type corresponding to the BIGINT.
MS could rectify this in Access, as Access now has its own version of the Jet database engine (Jet 4 is maintained by the Windows development group because all versions of Windows use Jet as the data store for Active Directory), called the ACE (and a new file format, ACCDB), but the ACE amounts to Jet 5, an updated version of Jet 4. I'm sure MS is actually concerned about this kind of problem (they want Access to work as seamlessly as possible with SQL Server), but starting with A2K7 and the ACE, they now have a way of finally fixing it (introducing a compatible data type, or through somehow working around the problem in some other way), something they couldn't do when they didn't control their own version of the Jet db engine.
(don't feel like SQL Server developers are the only ones who've had problems caused by this internal MS confusion -- there are features in Jet 4 that can only be accessed/manipulated via ADO because MS never updated DAO to reflect the new Jet 4 changes; and the people who trusted MS's plans for the future of data access and drank the ADO Kool Aid are pissed off because classic ADO is now completely dead, replaced by ADO.NET, which is similar but not close enough to make it easy to port classic ADO code easily)
In the meantime, I experimented with A2K and SQL Server 2K and found that:
for display of existing records, you get #DELETED# displayed for all rows. The workaround for this is to display data via a VIEW that casts the BIGINT field as VARCHAR. All the fields are editable in the view, so you could use this for editing existing records -- you just can't add new records using this view. You also use this view for reporting. The only problems would be if you were linking with subforms/subreports -- you wouldn't have to have a view for those, but you'd have to do something for the linking. That could be as simple as linking your subforms/subreports on an expression instead of on a direct value (you'd simply convert the subform foreign key to a string and link on that, i.e., Link Master = [BIGINT PK cast as VARCHAR from view] and Link Child = CStr([foreign key field])).
for adding records, Access has no trouble displaying the new data (including when the BIGINT PK is an identity field) and inserting data into the new record, so you could switch recordsources for your form from the view to the table itself when adding new records.
So, yes, there's a workaround, but it does involve going beyond the basic default methods that Access provides. Another alternative would be to go with entirely unbound forms, but I think that's overkill given the fact that the workaround is so simple. One of the great things about Access is that you have many options for working around most problems you encounter. In this case, the workaround seems pretty trivial.
-- David W. Fenton David Fenton Associates http://dfenton.com/DFA/
Admin
It can be used, it has been used, I have seen it used myself in a real WTF project so what is so difficult to understand? There are such crappy architectures running, and the problem with Access (as well as VB6) is that such tools support/encourage creating such crappy solutions.
Admin
Unfortunately, I know way more about Access that I ever wanted and I can tell you that there are many reasons why Access can corrupt itself even with a proper frontend/backup setup. A common reason is that one or more users is using a different version of Jet. Another reason is that two people with a different versions of Jet sync with the same replica. Another cause is users using different versions of Access. The most notorious reason of course is if the network is anything less than 100% reliable. If a glitch happens in a users network connection to the server, that can easily corrupt the database.
The fact that an Access db can be corrupted is enough reason not to use it for anything critical or for more than five to ten users.
Admin
Adding to what I said, my biggest beef with Access beyond the unbelievable fact that it can corrupt itself, is that Access' SQL syntax is not standard. As if that is not enough, I have run into far more situations that I care to admit where the query engine simply cannot handle a query that it should. The other day a colleague of mine ran into an issue where IIF was incorrectly processing the data (in essence returning false when it clearly should not have). That instigated hours of hack workarounds in order to get the query to do what it was supposed. As I said, I have had to run into numerous hack workarounds with Access evil query engine to get it to do what a normal database like Oracle or SQL Server can do with no problem.
As I said, Access is a drug. It feels good at first until one day you look up and wonder how you ended up in bed with a crack whore.
Admin
Right. If it's good enough for those bulldozers, it's good enough for whatever precision clock you're working on.