Rewriting a "developed in tandem with learning" database application

[ExcessionOCP]

Hi All,



Bit of a philosophical question here. I have been working on an Access system for a number of months now, and what started out as quite a basic system has become (for me) quite complex and feature-packed. I have been learning as I go, and the system now is written in a more logical, efficient way but is definitely built on a bit of a house-of-cards with uncommented VBA, badly-written queries and inconsistent naming conventions and very little in the way of error-checking and error-handling.

So, I'm thinking that I could spend a quiet weekend restarting from scratch and redeveloping the system. Is this a good idea? My main concern is that I have probably forgotten why or how I did things in the early days, but I think that even if I have, I can redevelop it in perhaps a more efficient, robust manner.

Also, my application is currently a mixture of VBA SQL functions as well as "stored" queries (designed in the query designer and saved in the main application). Does anyone have any view on which is better? Is it better to have all VBA functions and no stored queries? Or all stored queries and no VBA functions?

Thanks in advance for any guidance that you gurus can offer this jack-of-all-trades, master of none.

Cheers,

Phil

[CJ_London]

Use what is right for the job. Sometimes better to use queries, sometimes better to use sql constructed in vba. I tend to favour the latter as I develop for clients and I don’t want to tempt them to change a query or delete by accident which is possible with a .accde.

Some will say the vba approach is slower because of the overhead of creating a query plan. However that time is minimal with modern computers. Plus a query plan does not reoptimise as tables get longer. So a query developed on a relatively small dataset (as used during testing) might be different if created on much larger tables. But i emphasise the time difference is very small

[moke123]

So, I'm thinking that I could spend a quiet weekend restarting from scratch and redeveloping the system.

My quiet weekend is going on about 10 years or so now. I couldn't even guess how many times I've written and re-written my main
app that I use for work everyday. Each time you learn something new or a more efficient way of doing something it's back to a re-write of something.
It's a never ending process, IMO.

I have one colleague in another office who swears by an app I wrote way back when. When I look at what I wrote back then I cringe.
I've offered to upgrade him numerous times but nope he wont do it, it's almost embarrassing to have it out there.
I may lie to him and tell him the runtime version he's using has exceeded its end of life and will stop working.

I say go for it. You'll enjoy it and learn even more. Just plan on your quiet weekend being a little extended.

[ExcessionOCP]

Haha! I can totally see myself in the same position as you within a few years! I love learning different and (sometimes!) better ways to do things.

To be honest, developing the system is way more interesting to me than using it. I am about to roll it out to a couple of colleagues though, which has focused my attention on the lack of error handling and recovery - I know what to do to not break things, but no-one else will, and I'm sure they will break it in the first 5 minutes.

[Micron]

I agree with the comments above. As for queries vs vba sql I also think it depends on how volatile you think the query design may be (not talking about parameters here). It's easier to add/remove fields as an example, in a query than it is to re-write the vba generated sql for it. It's also easier to hide sql from nosy users if it's all in code that they can't get to. I'd lean towards vba if my project was for sale.

In case any of my usual links would help with your redesign project, here's a link (see post 15).
https://www.accessforums.net/showthread.php?t=87892