Reinventing the wheel: SQL
 

I get the pleasure of taking a standard Access database and creating an SQL database out of a portion of it. To make things more interesting, I am REQUIRED to do this in Access 2002, and I have neither the developer toolset nor a proper SQL Server to run this thing on. I'm limited to a MSDE 2000 client to handle a business's database, and that has to run on a machine that is used for our web development and is incidentally a fileserver as well. I forsee many problems with this (a sales rep logging on for pr0n when I'm at lunch, crashing the machine, and the dbase going down ... of course it is MY fault), and a whole lot of screwy VB programming to make this work without a proper server.

This is what you get when you have no IT budget and a boss who wants everything for nothing. Next thing you know, he'll want me to create a Windows 2000 Advanced Server to host our web portal in-house (you should have seen him vomit when I tried to explain the concept of Linux to him) using a Pentium 133 and a copy of MSDOS 6.22. Sometimes I wonder if the pay is worth it. *sigh*

That bites.

My wife's boss recently understood the need for something bigger than MS Access: it was slow, clunky,... but they stuck with it, even though it had WAY over 50'000 records, split in 13 different tables:rolleyes:

They went to some other database type, which was faster, but when they deployed the world wide web interface for it, and had everything running "replication", it didn't work very well.:rolleyes: so they took it down.

So they just got an MS SQL server:eek: (no, they weren't affected by "slammer", but the rest of the network was).

Now if they could only understand the benefits of a transactional database...

airspirit, can you fool him and use a MySql server hidden behind ODBC ? I've used that trick for a few customers. Works well. They can even edit queries in Access (or even Excel) and run them through ODBC on the MySql server.

:D I like that. it kind of a dirty underhanded trick, yet it's probably for the better, you end up smelling like roses. Plus it's a thumbs up at management. To use one of their 10c words, "screw you paradigm."

Ain't nothing wrong with MSDE2000 - it's freely available (if you poke around enough you can find a couple places where you can get it as a free download from MS), it has all the functionality of full-blown MS SQL Server 2000 (the only limitations are no databases > 4GB, no clustering, etc. - not things you are going to hit on a small database), and it kills MySQL in functionality.

Being forced to use Access as a front-end though - I personally would take as an insult to my abilities as a software developer (and I don't even do it profesionally)

The big problem with MSDE is that you can only have a maximum of 5 connections at a time on it if you're using it as a server. I need 8.

I wish I could use MySQL like you were saying, but I don't have a machine I can leave in Linux. As I said, I need this as a general web development mill, fileserver, and resource for the guys I work with (who require WinXP for some of the specialized apps we use).

MSDE also has some issues with any kind of programming it seems. I can't use any ActiveX calls whatsoever, which makes programming forms and advanced sort/mining functions an absolute biznatch. I can't even program a frickin' refresh button without it crashing!!! Unfortunately since my budget for the month is about all of $15 (I have to get a keyboard later, so they're being generous), I can't purchase either a new Linux box, or a copy of MSSQL Server. Rauauaaaghgh!

Hey ! MySQL runs very fine on Win NT / 2000 / XP !!! Just get your hands on it it's free and incredibly easy to set up :) (de-zip, click on "start" and voila !) You'll then have a full-featured, true SQL database server...
Get MyODBC from the same site BTW, so you can make Excel / MS Query / Access scripts.
Like i said i used that trick professionally, and everyone is happy.

Interesting. Last time I looked into MySQL, I could only find Linux and BSD versions ... I wasn't aware it was available for WinXP ... I'll have to look into that.

Thanks for the tip. I'm about to the point where the lack of script functionality is going to drive me insane, especially since I'm the only one here that can do anything manually ... if they don't have nice flashy automation buttons, they're all lost. Even then, though, it is like pulling teeth to teach DB usage to them.

It took around an hour to teach one gal that she couldn't just type in data in a form and hit enter to do a search. She destroyed about two days worth of data in around a half hour before she came to me wondering why her "searches" weren't working. Fscking morons.

Now now, don't go blaming the end user entirely, as databases just aren't very idiot proof by nature. A good interface can make all the difference, and I think that's one of Access's strong points. If you redesigned the front-end, it might help reduce those types of problems.

My experienc with trying to make thing fool proof, is that I always underestimate the inginuety of fools.

You tell me how foolproof this is: There are two buttons. One is labelled "search", and one is labelled "records". If you push the "search" button, it brings up a form in which you can type in data and run queries and filters on your other form, which is brought up by the "records" button. She didn't want to use the search button, so she started entering data into the records form hoping it would do the same thing.

Mind you, the title bar of one form is labelled "INVENTORY SEARCH FORM - PERFORM SEARCHES HERE" and the other is "INVENTORY RECORDS". On the "records" form on the bottom, it refers you to the "search" button if you want to search for something.

I don't know how much more foolproof I can get it without branding the form in big red ARIAL BLACK letters "YOU CAN NOT SEARCH FROM THIS FORM YOU MORON!". Mind you, this is just one part of the entire database (part of the reason I had to do it this way was because she couldn't figure out how to synchronize, and tables started to mysteriously disappear in the *mdb access dbase I had her on ... this isolates her damage potential) ... and I had to build it this way, because she didn't want to have to worry about pop-up prompts for search data (too confusing, she said). She wanted one form to search and one to display data ... now she's saying that even THAT is too confusing.

She is not a real brainiac ... but what is scary is that she does the accounting for that business nearly singlehandedly, with an assistant that is hired solely to fix the mistakes she makes ... and this dbase is a hell of a lot easier to use than the UNIX terminal interface she uses for her accounting.

she probably had classes in ithe unix terminal.
I've met very few accountants that could work software or anything else for that matter. Hey, what happend to ECUPirate? boy observant aren't I.

I've also worked with Access and it isn't easy to make it idiot proof.
You can add events that'll save the record, anytime someone comes in and out of a field, but that'll take in typos like mad.

I find it best to put a search screen seperate, with a link to edit that particular record. It's a pain for the user, but relatively harmless to the data.

That's pretty much what I did, Ben. I have one form that runs searches and calls the second form that displays the located records. There is no real way to confuse the two since their layout is completely different.

I am having problems with the OnEvent calls, however. For some reason, under the MSDE, I'm not able to use any OnEvent calls or ActiveX calls without it crashing the form. Even the built-in access OnEvent buttons (such as a print button on a form) cause the form to crash. It makes no sense.