Man was I dumb!

[EdFred]

Of course I didn't know any better at the time. 8 years ago with NO programming experience or any knowledge of databases I started to build the database that runs our entire business in Access (No, that's not the dumb part) with the help of a friend who was just starting learning about it.

Well we got it working and it did / and does a lot more than some $40,000 programs out there, but wow was it bulky. I had over 200 queries at one point, and that many or more forms even though there were only maybe 30 tables.

I left it sit that way for about 8 years, because, well, if it ain't broke don't fix it, until I started to make changes to things, and realized.....holy cow this thing is bloated. Multiple querys doing the exact same thing with the only difference being one field having a defintion being grabbed from a form, and multiple forms being exactly the same except for it being defined by a combo box on another form, the same went for reports. I had queries like 4 levels deep in some spots - all because...well...you get the picture

This week I started to clean house on this thing, and got the number of queries to less than 100, and the number of forms to less than that, and the number of reports to less than 1/2 the number of original reports.

the filter property is a wonderful thing. And the good thing is....performance increase.

 

[jesse]

mySQL & postgreSQL ...are good stuff.

I've used access in the past for dynamic web applications, and as soon as it gets some traffic.. All hell would break loose.

 

[Carol]

Well, congratulations or whatever you say to someone who has accomplished something you have no idea what it means

 

[EdFred]

I've used access in the past for dynamic web applications, and as soon as it gets some traffic.. All hell would break loose.

I've heard people say that about Access. We only have, at most, 5 computers logged into it, and very little dynamic SQL, but my friend works for a large bank and has Access databases that have many, many times that number on simultaneous users with no issues.

Guess it depends on what's actually going on.

 

[The Old Man]

Guess it depends on what's actually going on.

Exactly! Access is a very powerful and useful tool, but you must use it wisely. The degradation with increasing users/usage is normally due to poor design and overly pessimistic record locking.

Good database design and program design can nearly eliminate multiuser record locking. (Note, I said nearly, not completely) The less locking employed, the less performance degradation seen as users increase.

Bottom line, the numbers of users/accesses and the quality requirements of design/programming are directly related.

 

[EdFred]

You've got that right! I hear a lot of naysayers with the MsOffice package, but it integrates so nicely. I can automatically import pricing updates from my outside sales staff via Outlook/Excel attachements into the program, process new purchase orders and sales orders the same way. Eliminated the need for entering the same data anymore than one time. Quotations, Consignmnent Inventory, Demo Products, Blanket orders and Backorders automatically migrate to invoices. the only thing I have to manually do is import vendor pricing files from my vendors because the layouts are never the same.

I have yet to see anything like Great Plains, or any of the other software packages made specifically for this sort of thing come anywhere close to what this does.

 

[Greebo]

The main problem with using Access for data storage is that it is ONLY a file based database system. Seriously important production data should get stored (in a MS based shop) in SQL Server for more reasons than I really can list right now. Access, however, makes a GREAT quick front end tool for SQL server if you don't need the heavy custom abilities offered by .NET development languages.

As for your "OMG Did I WRITE that piece of crap" experience - all I can say Ed is that EVERY programmer has moments like that. I've had too many to count, m'self. IMO you're not a REAL programmer until you have at least one "What a n00B I was!" moment, so...

Congratulations, Programmer.

 

[Greebo]

Oh yeah, and 5 users hitting access at the same time should be fine. How big is the data file? (After compression)

 

[EdFred]

Oh yeah, and 5 users hitting access at the same time should be fine. How big is the data file? (After compression)

22+MB

 

[jesse]

Exactly! Access is a very powerful and useful tool, but you must use it wisely. The degradation with increasing users/usage is normally due to poor design and overly pessimistic record locking.

Good database design and program design can nearly eliminate multiuser record locking. (Note, I said nearly, not completely) The less locking employed, the less performance degradation seen as users increase.

Bottom line, the numbers of users/accesses and the quality requirements of design/programming are directly related.

It comes down to what you are using it for really. For a small business scenario...It..works.

Problem is when you have a dynamic high traffic website, where every page load is generated on the fly using the database.

A high traffic forum is a very good example of when Access just isn't going to do the job.

That is the very moment I gave up on it. Plus I am a major fan of free database solutions that are extremely effective and no worries regarding licensing.

 

[Greebo]

22+MB

You'll have no problems then as long as you're taking regular backups and performing regular maintenance.

 

[EdFred]

You'll have no problems then as long as you're taking regular backups and performing regular maintenance.

Which is why it still runs like a champ after 8 years. The only issue is - when is my file server gonna die on me? That's 8 years old too. LOL

 

[Greebo]

In 5 minutes. Quick, buy a new server!!!

 

[Dan Smith]

Ed,

I sure don't understand the "dumb" part... With no prior experience you built a solution that has met your needs for 8 years. Want to know the percentage of brilliant software solutions that never get fielded at all, or those that get fielded but are unusable? And 8 years is a loooong time, which indicates that the SW was doing the job pretty well. Of course it got smaller and faster when you took a second look at it: you know a lot more about your requirements and Access's capabilities than you did the first time around. I'd say you were pretty successful!

Dan (MCSD.Net as of 9/1: No more tests, Chuck! Except that now I have to finish up MCDBA...)

 

[mmilano]

but my friend works for a large bank and has Access databases that have many, many times that number on simultaneous users with no issues.

i hope this isn't my bank

nice job on the optimization. i have cycles where i want to re-write code i've done in the past, sometimes even shortly after i finish it. there's so many ways to code the same thing. i've learned to resist the temptation unless something is really wrong now. i don't think i could have resisted for 8 years though =)

 

[Greebo]

i hope this isn't my bank

You'ld be surprised.

I've worked at two major national banking/financial institutions that used Access for a great deal of its tracking. Not of core account data, mind you, but for a heck of a lot of other stuff.

Its scary!