Migrate from Access 2010 / VBA

[kagard]

On Nov 27, 7:06 pm, David Bolen <db3l.... at gmail.com> wrote:
> kgard <kag... 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
>
>

Thanks, David, for all the helpful insights. I really appreciate the
time you took to reply. Thanks to everyone who pitched in. You've
given me a lot to think about.

Keith