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

Click here to add your comments




Tue Aug 23 18:53:55 2005:   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:   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:   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:   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:   drag


Well, I went to MS's site and found pretty much exactly what I mentioned above.
http://www.microsoft.com/sql/accessmigration.mspx

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:   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:   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:   drag


Realy?

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




Thu Aug 25 14:32:19 2005:   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:   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


Don't miss responses! Subscribe to Comments by RSS or by Email

Click here to add your comments

If you want a picture to show with your comment, go get a Gravatar