On Nov 27, 7:06 pm, David Bolen wrote:
> kgard writes:
> > I am the lone developer of db apps at a company of 350+
> > employees. Everything is done in MS Access 2010 and VBA. I'm
> > frustrated with the limitations of this platform and have been
> > considering switching to Python. I've been experimenting with the
> > language for a year or so, and feel comfortable with the basics.
> (...)
> > Has anyone here made this transition successfully? If so, could you
> > pass along your suggestions about how to do this as quickly and
> > painlessly as possible?
>
> I went through a very similar transition a few years ago from
> standalone Access databases (with GUI forms, queries and reports, as
> well as replication) to a pure web application with full reporting
> (albeit centrally designed and not a report designer for users).
>
> I suppose my best overall suggestion is to migrate the data first and
> independently of any other activities. Unless your uses for Access in
> terms of GUI or reporting are extremely limited, don't try to replace
> your current system in one swoop, and in particular, be willing to
> continue allowing Access as long as necessary for GUI/reports until
> you're sure you've matched any current capabilities with an alternate
> approach. For all its warts, as a database GUI and reporting tool,
> Access has a lot going for it, and it can be more complex than you may
> think to replicate elsewhere.
>
> So the first thing I would suggest is to plan and implement a
> migration of the data itself. In my case I migrated the data from
> Access into PostgreSQL. That process itself took some planning and
> testing in terms of moving the data, and then correcting various bits
> of the schemas and data types (if I recall, booleans didn't round-trip
> properly at first), so was actually a series of conversions until I
> was happy, during which time everyone was using Access as usual.
>
> To support the migration, I created a mirror Access database to the
> production version, but instead of local Jet tables, I linked all the
> tables to the PostgreSQL server. All other aspects of the Access
> database (e.g., forms, reports, queries) remained the same, just now
> working off of the remote data. This needed testing too - for
> example, some multi-level joining in Access queries can be an issue.
> In some cases it was easier for me to migrate selected Access query
> logic into a database view and then replace the query in Access to use
> the view. You also need to (painfully) set any UI aspects of the
> table definitions manually since the linking process doesn't set that
> up, for which I used the original Access db as a model. I ended up doing
> that multiple times as I evolved the linked database, and I'll admit that
> was seriously tedious.
>
> While not required, I also wrapped up my new linked Access database
> into a simple installer (InnoSetup based in my case). Prior to this
> everyone was just copying the mdb file around, but afterwards I had an
> installer they just ran to be sure they had the latest version.
>
> If you do this part carefully, for your end users, aside from
> installing the new database, they see absolutely no difference, but
> you now have easy central access to the data, and most importantly can
> write other applications and tools against it without touching the
> Access side. It turns Access into just your GUI and reporting tool.
>
> If you have power users that make local changes they can continue to
> design additional queries or reports in their own local mdb against
> the linked tables. They'll need some extra support for updates
> though, either instructions to re-link, or instructions on exporting
> and importing their local changes into a newly installed version of
> your master mdb.
>
> Having done this, you are then free to start implementing, for
> example, a web based application to start taking over functionality.
> The nice thing is that you need not replicate everything at once, you
> can start slow or with the most desirable features, letting Access
> continue to handle the less common or more grungy legacy stuff at
> first. There are innumerable discussions on best web and application
> frameworks, so probably not worth getting into too much. In my case
> I'm using a CherryPy/Genshi/SQLAlchemy/psycopg2 stack.
>
> As long as you still have Access around, you'll have to take it into
> consideration with schema changes, but that's not really that much
> harder than any other schema migration management. It's just another
> client to the database you can run in parallel as long as you wish.
> If you do change the schema, when done, just load your master Access
> database, update the links, and rebuild/redistribute the installer to
> your users. Many changes (e.g., new columns with defaults) can be
> backwards compatible and avoid forced upgrades.
>
> You can operate both systems in parallel for a while even for similar
> functionality (for test
