On Nov 27, 7:06 pm, David Bolen <db3l....@gmail.com> wrote: > kgard <kag...@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 -- http://mail.python.org/mailman/listinfo/python-list
