APLawrence.com -  Resources for Unix and Linux Systems, Bloggers and the self-employed

Please HELP!! Help someone understand: SQL db's

© August 2005 bruceg

Author: bruceg
Date: Tue Aug 23 18:42:35 2005
Subject: Please HELP!! Help someone understand: SQL db's

Got something to add? Send me email.

(OLDER)    <- More Stuff -> (NEWER)    (NEWEST)   

Printer Friendly Version

-> Please HELP!! Help someone understand: SQL db's


Inexpensive and informative Apple related e-books:

Take control of Apple TV, Second Edition

Are Your Bits Flipped?

Take Control of Pages

Take Control of OS X Server

iOS 8: A Take Control Crash Course

More Articles by © bruceg

Tue Aug 23 18:53:55 2005: 1013   bruceg2004

I also meant to mention, that the sites I did google, did not explain things in terms meant for a non-db person. This guy knows how to use Access, but would not have to first clue in constructing a SELECT statement against a "regular" db. Most of the sites I found, were fairly technical, and I was hoping to have something less technical, and more "salesman friendly".

- Bruce

Tue Aug 23 20:02:55 2005: 1014   drag

I donno.. Access's normal database is simply incapable of doing multiple users as I understand it. It's a single user setup and you have to have the entire application running in order to access a access database.. I don't even think you can access it over a network as a normal database service.

However you can use Access as a front end for connecting to 'real' SQL databases, thru ODBC connector and whatnot.

Maybe is that what the guy was talking about? You'd be able to use Access as a front end for a MS-SQL system.

Not that I know much about it, mind you. Just not sure what is going on.

Wed Aug 24 02:08:32 2005: 1016   bruceg2004

I was recommending Access as a front end to PostgreSQL through ODBC. He just does not want to do that. He thinks because he can run an Access db that has 500+ MB of data on his laptop, that the company should be able to use Access for their entire ERP system! I tried to explain that there is a reason why MS-SQL, Oracle, Informix, DB2, etc exist, and why Medium to Large companies do not run their systems on Access, but he refuses to accept this. Instead of giving him technical explanations why, I thought maybe there is something that is more "salesman" like in explanation that he could read. I just keep hitting a wall with him, and he will not take any of my explanations, and wants me to "make it work".

I just don't know what else I can do to explain things to him. I can explain it technically, but he will not understand, so I was hoping there was something out there that may be more black and white for him. I certainly cannot make something work, that is not designed to work in the way he wants, which is multiple users accessing the data.

I dunno what else to do :-(

- Bruce

Wed Aug 24 03:33:08 2005: 1017   drag

Well crap.

Have him show you how he expects that you'll be able to have dozens of people connect to the database at the same time over the network.. How does he expect people to get work done when he takes his laptop home with him!

It's just not designed to do that sort of thing. Access by itself is just a stupid little databse program designed for small business book-keeping. It's not even able to use real SQL instructions, just psuedo-sql.

He can't be the only business man he knows, he has to have buddies that are involved in their own businesses of various sizes... Maybe ask him to ask them what they use.

Now that I think about it.. see if you can find some Microsoft documentation on what access vs MS SQL is to be used for. They are good at this sort of thing, after all they taught salesmen to be able to convince people to drop Novell for NT... so they ought to be able to convince anybody to do anything!

Funny stuff. Other then that, setup what he wants and wait a week or two. He'll probably understand by then.

Wed Aug 24 10:21:47 2005: 1018   drag

Well, I went to MS's site and found pretty much exactly what I mentioned above.

There is one doc file there that goes into depth about the differences between Access and MS SQL server. It paints a pretty rosy picture (from my limited understanding) of the Jet 'SQL' engine that Access uses.

The most impressive example of the differences is how a network error can spontaniously corrupt a Access database, completely locking out all users instantaniously.

Wed Aug 24 13:13:23 2005: 1019   bruceg2004

Thanks for the link! I will try to replace MS-SQL with something else :-) I am also considering recommending FileMaker Pro. I have a linux server version of FileMaker Server, and this works well in a multiuser environment.

- Bruce

Thu Aug 25 00:01:53 2005: 1023   BigDumbDinosaur

I need to help someone understand that MS Access is not the worlds best database, and should not be used in some situations.

Now, that's an understatement if I ever read one! <Grinning from Ear to Ear>

Problema numero uno with Access is that it is easily messed up by user contention in a multiuser situation. Microsoft's file and record locking semantics are not well implemented, and it is all too easy for concurrent writes to damage data in Access files, especially when said writes occur to contiguously adjacent records. Also, many Windows networks are running with opportunistic locks (oplocks) enabled because some MCSE (who probably doesn't know diddley.squat about the mechanics of database engines) told the client that it would enhance performance (an area in which Windows needs plenty of help). While oplocks can help performance on a large system under certain conditions, little is usually gained with database access, as such access is too random for oplocks to be beneficial. On the flip side, my (bitter) experience has taught me that oplocks are not trustworthy and can corrupt data. If such corruption affects the keyblock area of the file (this is the area where the record keys are stored), it can render the file totally useless.

Aside from the technical mishmash involving the low level aspects of Access, there's the fact that it stores data in a proprietary format, which can be a real impediment to data portability. In the UNIX world, ISAM (indexed sequential access method) files have long been the standard for shared databases (I use them extensively). In fact, at one time, the C development package sold by SCO included ISAM support. ISAM is so well standardized in most *NIX environments, I have been able to use my Thoroughbred Dictionary-IV development environment to read records in ISAM files generated by other languages and programs, extract data and in some cases generate new sorts that couldn't be generated in other packages. That's what I mean by portability. With Access, your ability to export data into other formats is significantly limited -- probably by intent.

If your client truly wants a reliable, high performance database system, he needs to get his cranium out of his rectum and quit buying into all this Windows crap. The best database packages (SQL or otherwise) are produced by independent thrid party developers, such as Faircom (long a standard in the *NIX world), Thoroughbred (a full timesharing language implementation that can support thousands of concurrent users with complete reliability), BBx (Basis International, another developer like Thoroughbred), etc. I've yet to see anything come out of Gates and company that even starts to approach the level of what I'd call a trustworthy and high performance database package.

Thu Aug 25 02:56:07 2005: 1024   drag


From some people it seems that Microsoft SQL server is god's gift to database technology. :-P

Thu Aug 25 14:32:19 2005: 1026   BigDumbDinosaur

From some people it seems that Microsoft SQL server is god's gift to database technology.

Well, I guess some folks are just easily impressed, eh?

Microsoft's SQL server is hardly worthy of the name -- a classic example of how MS tries to take a standard and muck it up to achieve vendor lock-in. If their SQL server were such a sterling piece of software major database users like airlines and master distributors would be running it on rows of Windows 2003 boxes. They don't because neither the SQL server or the operating environment in which it runs is trustworthy.

Thu Aug 25 19:56:56 2005: 1027   bruceg2004

Thank you so much for the great explanations. I need to grab some of the key points, as well as show him Microsoft's own page that explains that Access is not well suited for multi-user, high transaction db's.

I honestly think MS brainwashes people into thinking their products are superior. How else can I explain this persons love of Access. Personally, I cannot stand Access, and everytime I come across it, I usually will export everything, and import it into MySQL or PostgreSQL, and stick a PHP frontend on it. In all cases where I have done this, I have had ZERO support calls, other than someone needing to add a field or change something. No more crazy database errors, or having to purge data because it gets so slow. Also, no need to manually compact and repair the database on a regular basis.

With MySQL or PostgreSQL, I can run a cron script to optimize MySQL, or Vacuum the tables in PostgreSQL. I cannot count the number of calls I used to get with Access and the strange, cryptic errors it would produce. I simply get none of those calls when I have someone switched over to a "LAMP" solution. It just plain works.

- Bruce


Printer Friendly Version

Have you tried Searching this site?

This is a Unix/Linux resource website. It contains technical articles about Unix, Linux and general computing related subjects, opinion, news, help files, how-to's, tutorials and more.

Contact us

Printer Friendly Version

640K ought to be enough for anybody. (Bill Gates)

Linux posts

Troubleshooting posts

This post tagged:



Unix/Linux Consultants

Skills Tests

Unix/Linux Book Reviews

My Unix/Linux Troubleshooting Book

This site runs on Linode