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 -- http://mail.python.org/mailman/listinfo/python-list
