Conversion of 1.0 DB, to 97, to 2002, now to SQL

[ng]

Help, please. I don't even know where to begin. In Access 1.0 days, our
comany hired a man to create a sales database for our company.
Unfortunately, most of his code was and still is in macro format, I've
always hated macros, you can't see what they're doing lest you convert them
all to VB! It looks like there was no such thing as a global variable,
because he's doing things like storing global variables in temporary tables.
This type of "old style" programming as I'll call it is just SO difficult to
decipher. I don't know if I should quit my job, or just tell them it can't
be done (I know what that would result in.) Instead of making decisions up
front in a coding level, or in queries, he makes his decisions at the report
level. Nearly every field has an IIF statement to check for Foreign, or
Domestic, if there are more than X number of lines on the quote, we use this
report, if there are less lines than X, we use this other report. I've
never seen a more screwed up database in my life!

I'm not an access newbie, I can definitely program my way out of a paper
bag, but it seems like they've changed to plastic now. I feel like the
database needs to be completely re-engineered, however the sales department
is quite strict in that it must look, act, and perform exactly the way the
old application did. This seems silly in my mind, it'd be like asking Ford
specifically to create you a Model T because you had one, and liked the way
it drove.

There really is no specific "question" here, it's just more, "What do you do
when you have a REALLY old, monolith database, that was programmed with
pre-2K technology?"

If anyone can contribute any moral support, or any other advice if they've
had such an issue in the past, I'd appreciate any advice. <deep breath>

I'll be in macro land.

-Brian

[Lyle Fairfield]

ng wrote:

I'm not an access newbie, I can definitely program my way out of a paper
bag, but it seems like they've changed to plastic now. I feel like the
database needs to be completely re-engineered, however the sales department
is quite strict in that it must look, act, and perform exactly the way the
old application did.
You're right; they're wrong.
I'd be inclined to re-write it my way, give them the totally
re-engineered application and .... who knows?
This seems silly in my mind, it'd be like asking Ford
specifically to create you a Model T because you had one, and liked the way
it drove.

General Motors already has this in hand with their push rod engines ...
also Harley.
--
--
Lyle
--

[jimfortune]

Program it using "new school" programming but make the user interface
look and act similar to the way the old one did. They don't need to
see what's going on "under the hood" 8-) 8-). So give them something
that looks similar to the Model T but bigger with a modern engine (no
pun intended). Afterwards, let them know that they'll not be safe
without airbags with that kind of power so add those too.

I once converted a PowerBase application to Access and discovered that
the best way to make everyone happy was to color the main form's
background the same color as before and to duplicate all the
functionality from the old one before suggesting new possibilities made
possible by programming in Access. That was five years ago and the
only color change I was able to suggest successfully was to lighten the
background color :-(. But once they saw the Access version doing
everything the PowerBase app did and then started realizing the amount
of customization made possible by Access they gave me enough work to
make a boatload of money on it. They nearly went wild when they
realized they could come up with their own ideas and then see them
magically appear as part of the database. You can also write queries
to convert the data into normalized tables. They won't even know the
table structure has been fixed.

I know it's painful cleaning up someone else's database mess. Make it
flexible enough so that the programmer who comes in after you looks
like a genius, or at least as good as you are for awhile. Leave a mess
and the next programmer looks like an idiot. That's the penalty we
have to bear when doing things the right way. The best example I can
think of is Rational Fortran. Rational Fortran resulted from a
programmer's boss' requirement that the final code be in "old school"
FORTRAN. The programmer wanted to use modern constructs like
While...Wend so he came up with a translater that converted his
While...Wend and other control loops into "old school" FORTRAN. It was
a win-win situation. You need to find a similar path of wisdom if the
messy database allows. Clean up the mess, be patient and good things
will come of it.

One other note. Because it was custom programming, I trained the users
to watch for any abnormalities like a hawk. They use common sense when
looking at reports so when the rare case happens that my logic is wrong
they almost always catch it.

James A. Fortune

[ng]

Thank you for your support gentlemen. I feel much better after reading both
of your posts. I'll put my nose to the grind stone and eat this elephant
the one bite at a time that it needs to be. I like the "They don't need to
look under the hood" part, but I'm afraid there is ONE person, who may just
fancy themselves to be a mechanic. They tried very hard to be a mechanic in
Access Land, but going around creating tables like they're going out of
style in the back end, I'm just not going to allow this. I think the person
should extraxt their data from my application and run it through word, or
WHATEVER it is that they're trying to accomplish. Nobody but the programmer
should be under the hood, right? In this old access world, they thought
they were pretty good mechanics! So instead of prompting the user for a
criteria, we get 20 queries with the differing criteria.

Okay. To the grindstone. Thanks guys.

-B

<ji********@compumarc.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Program it using "new school" programming but make the user interface
look and act similar to the way the old one did. They don't need to
see what's going on "under the hood" 8-) 8-). So give them something
that looks similar to the Model T but bigger with a modern engine (no
pun intended). Afterwards, let them know that they'll not be safe
without airbags with that kind of power so add those too.

I once converted a PowerBase application to Access and discovered that
the best way to make everyone happy was to color the main form's
background the same color as before and to duplicate all the
functionality from the old one before suggesting new possibilities made
possible by programming in Access. That was five years ago and the
only color change I was able to suggest successfully was to lighten the
background color :-(. But once they saw the Access version doing
everything the PowerBase app did and then started realizing the amount
of customization made possible by Access they gave me enough work to
make a boatload of money on it. They nearly went wild when they
realized they could come up with their own ideas and then see them
magically appear as part of the database. You can also write queries
to convert the data into normalized tables. They won't even know the
table structure has been fixed.

I know it's painful cleaning up someone else's database mess. Make it
flexible enough so that the programmer who comes in after you looks
like a genius, or at least as good as you are for awhile. Leave a mess
and the next programmer looks like an idiot. That's the penalty we
have to bear when doing things the right way. The best example I can
think of is Rational Fortran. Rational Fortran resulted from a
programmer's boss' requirement that the final code be in "old school"
FORTRAN. The programmer wanted to use modern constructs like
While...Wend so he came up with a translater that converted his
While...Wend and other control loops into "old school" FORTRAN. It was
a win-win situation. You need to find a similar path of wisdom if the
messy database allows. Clean up the mess, be patient and good things
will come of it.

One other note. Because it was custom programming, I trained the users
to watch for any abnormalities like a hawk. They use common sense when
looking at reports so when the rare case happens that my logic is wrong
they almost always catch it.

James A. Fortune

[Larry Linson]

When you release to the users, give them an MDE... that will prevent the
"wannabee developer" from messing with your forms, reports, and modules, and
from creating new ones in your database.

Use Access security... get the Security FAQ from microsoft.com, read, and
carefully follow it. In your application, do not directly access the tables
but only use RWOP queries (that's discussed in the Security FAQ) to keep
them out of any Tables and Queries in the FrontEnd, and out of the Tables in
the back end.

The "wannabee" could break security if he was willing to spend US$140 or so,
and if he knew where to look for the software to spend it on, but I'd bet it
isn't that important to him.

And, every place I have ever worked, and at all my clients, having an
employee even try to break security on an application or secured DB would be
a career-ending decision. That is, respecting the security of applications,
etc., was a "condition of continued employment".

Larry Linson
Microsoft Access MVP