Migrate from Access 2010 / VBA

[David Bolen]

kgard <kagard at gmail.com> 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 testing if nothing else), but can then retire
functionality from Access as the web app supports it.  Ideally this
will be organic by your users preferring the web.  Selecting when to
drop Access entirely can then be driven by user demand.  Or, for
example, as is true in my case, none of my end users use Access any
more, but I still have one tool for administrators that isn't worth
replacing yet so they still use Access.  One of my partners also just
feels more comfortable making ad-hoc queries/reports in Access than
tools like pgAdmin3 or iReport so I let him, as long as it's just for
his personal use and I don't have to guarantee it won't break if I
evolve the schema.

On the reporting side, I really haven't found a good Python reporting
solution that includes a solid solution for the designer side of the
coin.  Prior to this project I most often used ReportLab for
dynamically produced reports (and still do) and its great, but I was
really the only one designing those reports and do most of them in
code.  For this project, I wanted an independent report format along
with a visual designer (so others could design reports for the system
to execute).  I settled on JasperReports, which has a well-defined
jrxml format, and a nice report design tool iReport.  Someone else
posted about OpenRPT which was also on my short list at the time
(along, I think, with Eclipse BIRT).  JasperReports (and its
ecosystem) has a bit of a learning curve - then again, all these tools
do, including Access - for my part, I've been happy with the choice.

Now, while amongst report designers you can just exchange report
design files and use iReport, for end users you'll need a way to
process those reports for your web app.  There is a Jasper Server tool
that is designed to organize and publish reports, but I found it
overly-complicated for my use case and I wanted complete control of
the end user interaction (hiding the reporting behind my web app).

I settled on a really small (~150 line) Java servlet, running beneath
Jetty, and configured to access the same PostgreSQL database.  It
accepts localhost-only HTTP report requests (with report parameters as
query parameters) and delivers the result as a PDF.  So my main web
application, when needed, makes an internal http request to the
reporting server on the same host, and then delivers the returned PDF
to the end user.  Once up and running, I can design a new report in
iReport, and then drop the jrxml design file into the reporting folder
on my server and it'll be available for the web application to use.
This is the only non-Python server component (as far as my own code).

As with the web app itself, this reporting operates in parallel with
any remaining Access functionality, so you need not replicate every
Access report all at once.

To put this all in context, for myself (almost zero Access experience
at the start, but plenty of Python and PostgreSQL experience), my data
conversion probably took about 3 months overall for a relatively small
Access database until everyone was working off of PostgreSQL under the
covers.  But that was all on my time, the actual switchover was quick
once ready to release.  While some of that scales with size, most of
the time was figuring out the process and testing.

To be truthful, I'm here maybe 4 years later, and still have some uses
of Access in the system.  I suppose I could have pushed harder to
completely retire it, but honestly, by following the above process the
remaining uses just don't bother or interfere with me that much.  If
they ever do I'll replicate those remaining bits of functionality
elsewhere.

-- David