Hi Peter,
After posting my solution I saw the rest of the thread and see that it
solves a problem you don't have. Sorry about that.
I don't know of any viable conversion tools from Access to anything.
There is one outfit out there somewhere that promises conversion to
VB. I believe I tried it back when VB3 was the target. It failed
miserably. So, No.
I do have other ideas but they are only relevant in terms of your
business model and your application. Otherwise you're asking us to
answer your specific questions without a clue as to relevance.
What is your application? What is its domain? What problems does it
solve?
What's your business model. Why do you have people in the field?
How do they interact with your customer base?
What the bloomin' H... is going on that you need 3 changes to your
application each week?
With that degree of instability, why are you using an MDE? I know,
it's because you don't want someone to see your commented VBA. Who;
your field reps? Your customers? Both?
If the distrust ("trust but verify") is of your field reps, can they
be bonded ("trust but insure")? If so there are workable solutions
that involve their using MDB, converting to MDE, etc...
Diplomats find nice ways to candy coat things. A skill I never
learned: Take all of the following with a large grain of salt and the
belief that I really do intend to help.
YOU are the source of your problems!
You wrote the code that
swelled to that huge 80 meg.
You had the vision to see what could be and the drive to get things
done and sold and out there serving your customers. However, I'm
going to assume that you did a lot of work along the way that was done
in the quickest, simplest way you could and that you then Pressed On!
For example: I can't believe that your application actually requires
300 discrete and separate queries! Some of those have to be simple
variations on a theme and good candidates for collapsing into
parameterized queries. FWIW the query window on most of my
applications is empty. I embed most queries into their forms and
reports. 125 forms? mmm...maybe. In my ignorance of your application
I will still hazard a guess that the front end should be less than
half that size.
On your own you're unlikely to quickly recognize and resolve the
problems. Coming from the Cobol world you probably brought a lot of
Cobol thinking with you. In most instances that's good because most
of what you knew there is true in Access as well. However you
probably brought over and applied many things that shouldn't be
applied in Access but that worked. I don't know what those things
might be. I never danced Cobol. I've looked at some Cobol code at
the insistence of one of my mainframe buddies. I could follow it but
I never got into it.
If you carried forward the good things you should have been using in
the Cobol world then you documented things as you began; at least to
the point of Problem Statement, Product Specification and Functional
Specification. With 3 changes per week you may not have been able to
keep things current.
I recommend that you tweak the Specifications to reflect what's going
on now and to then look around for an Access expert. I don't mean
just someone who makes a living selling services using Access but a
consultant with many successful projects who is capable of quickly
mastering the business arena of your application.
Give her or him the updated specifications and a verbal walk through
of everything just as though you were hiring that person to replace
you as the Software Engineer on this project. Tell that person that
you would like them to become familiar with the specifications and to
then give you a preliminary design for an Access based product. Tell
them they have a week to get it done and that they have unrestricted
access to you. That means that when they're interacting with you the
phone doesn't ring and there will be no taps at the door. If they
really whine and cry give them two weeks - same conditions. They will
need particular help identifying the entities in play. Don't look at
their work until the agreed time is up.
At the end of the agreed upon time, look at their preliminary design.
If it's significantly different from yours, find out why.
Take everything said here with the idea that it's intended to help
you. I do believe you can find workable solutions while staying
within Access.
HTH
