Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #33825 > unrolled thread

Migrate from Access 2010 / VBA

Back to article view | Back to comp.lang.python

Contents

  Migrate from Access 2010 / VBA kgard <kagard@gmail.com> - 2012-11-22 19:19 -0800
    Re: Migrate from Access 2010 / VBA David Hutto <dwightdhutto@gmail.com> - 2012-11-22 22:33 -0500
    Re: Migrate from Access 2010 / VBA Jason Friedman <jason@powerpull.net> - 2012-11-22 21:35 -0700
    Re: Migrate from Access 2010 / VBA Peter Otten <__peter__@web.de> - 2012-11-23 10:08 +0100
    Re: Migrate from Access 2010 / VBA Wolfgang Keller <feliphil@gmx.net> - 2012-11-25 20:48 +0100
      Re: Migrate from Access 2010 / VBA kagard <kagard@gmail.com> - 2012-11-26 04:42 -0800
        Re: Migrate from Access 2010 / VBA Wolfgang Keller <feliphil@gmx.net> - 2012-11-27 20:27 +0100
      Re: Migrate from Access 2010 / VBA Nicolas Évrard <nicoe@altern.org> - 2012-11-29 11:46 +0100
      Re: Migrate from Access 2010 / VBA emile <emile@fenx.com> - 2012-11-30 10:16 -0800
    Re: Migrate from Access 2010 / VBA Michael Torrie <torriem@gmail.com> - 2012-11-26 09:21 -0700
      Re: Migrate from Access 2010 / VBA kagard <kagard@gmail.com> - 2012-11-27 09:36 -0800
      Re: Migrate from Access 2010 / VBA Wolfgang Keller <feliphil@gmx.net> - 2012-11-27 20:24 +0100
    Re: Migrate from Access 2010 / VBA David Bolen <db3l.net@gmail.com> - 2012-11-27 19:06 -0500
      Re: Migrate from Access 2010 / VBA Michael Torrie <torriem@gmail.com> - 2012-11-27 20:33 -0700
      Re: Migrate from Access 2010 / VBA kagard <kagard@gmail.com> - 2012-11-29 07:43 -0800
        Re: Migrate from Access 2010 / VBA Michael Torrie <torriem@gmail.com> - 2012-11-29 10:22 -0700

#33825 — Migrate from Access 2010 / VBA

Greetings:

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. 

I am concerned that I'll have a hard time replacing the access form and report designers. I've worked a little with TKinter, but it's a far cry from the GUI designer in Access. Finding a professional grade report designer looks like an even bigger challenge.

I don't need to port any applications, but I will need to use the data (mdb/accede format), design a variety of reports with multi-level groupings, and deliver them to many individual recipients via email.

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? 

TIA
Keith

[toc] | [next] | [standalone]

#33827

On Thu, Nov 22, 2012 at 10:19 PM, kgard <kagard@gmail.com> wrote:
> Greetings:
>
> 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.
>
> I am concerned that I'll have a hard time replacing the access form and report designers. I've worked a little with TKinter, but it's a far cry from the GUI designer in Access. Finding a professional grade report designer looks like an even bigger challenge.
>
> I don't need to port any applications, but I will need to use the data (mdb/accede format), design a variety of reports with multi-level groupings, and deliver them to many individual recipients via email.
>
> 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?http://www.youtube.com/watch?v=DksSPZTZES0
>
> TIA
> Keith

Translate function for function to the new language(return values),
then adapt the GUI to represent the new functions on event activity
via widgets.



-- 
Best Regards,
David Hutto
CEO: http://www.hitwebdevelopment.com

[toc] | [prev] | [next] | [standalone]

#33831

> 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.
>
> I am concerned that I'll have a hard time replacing the access form and report designers. I've worked a little with TKinter, but it's a far cry from the GUI designer in Access. Finding a professional grade report designer looks like an even bigger challenge.
>
> I don't need to port any applications, but I will need to use the data (mdb/accede format), design a variety of reports with multi-level groupings, and deliver them to many individual recipients via email.

Do the employees need to be able to create their own reports, or do
you interview them and create reports based on their requirements?

Is there a need to provide slice-and-dice, or is each permutation/view
of the data its own report?

Are the recipients internal?

[toc] | [prev] | [next] | [standalone]

#33838

kgard wrote:

> Greetings:
> 
> 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.
> 
> I am concerned that I'll have a hard time replacing the access form and
> report designers. I've worked a little with TKinter, but it's a far cry
> from the GUI designer in Access. Finding a professional grade report
> designer looks like an even bigger challenge.
> 
> I don't need to port any applications, but I will need to use the data
> (mdb/accede format), design a variety of reports with multi-level
> groupings, and deliver them to many individual recipients via email.
> 
> 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?

These guys are coming from Foxpro, but I'd expect a huge overlap of the 
problem spaces:

http://dabodev.com/

(Disclaimer: I have not tried Dabo myself)

[toc] | [prev] | [next] | [standalone]

#33916

> 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. 
> 
> I am concerned that I'll have a hard time replacing the access form
> and report designers. I've worked a little with TKinter, but it's a
> far cry from the GUI designer in Access.

The list of Python frameworks for rapid development of desktop 
(i.e. non-Web) database applications currently contains: 

using PyQt (& Sqlalchemy): 
Pypapi: www.pypapi.org 
Camelot: www.python-camelot.com 
Qtalchemy: www.qtalchemy.org 

using PyGTK: 
Sqlkit: sqlkit.argolinux.org (also uses Sqlalchemy) 
Kiwi: www.async.com.br/projects/kiwi 

using wxPython: 
Dabo: www.dabodev.com 
Defis: sourceforge.net/projects/defis (Russian only) 
GNUe: www.gnuenterprise.org 

Pypapi, Camelot, Sqlkit and Dabo seem to be the most active and best 
documented/supported ones. 

> Finding a professional grade report designer looks like an even
> bigger challenge.

LibreOffice is imho quite useful for database reporting. It comes with a
native (SDBC) driver for PostgreSQL and allows Python scripting.
LibreOffice Base can even be useful for CRUD GUIs.
 
> I don't need to port any applications, but I will need to use the
> data (mdb/accede format),

Don't. Put your data into an *actually* transaction-safe RDBMS (which
"Jet" is *not*), such as e.g. PostgreSQL.

> design a variety of reports with multi-level groupings, and deliver
> them to many individual recipients via email.

Sincerely,

Wolfgang

[toc] | [prev] | [next] | [standalone]

#33934

On Nov 25, 3:48 pm, Wolfgang Keller <felip...@gmx.net> wrote:
> > 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.
>
> > I am concerned that I'll have a hard time replacing the access form
> > and report designers. I've worked a little with TKinter, but it's a
> > far cry from the GUI designer in Access.
>
> The list of Python frameworks for rapid development of desktop
> (i.e. non-Web) database applications currently contains:
>
> using PyQt (& Sqlalchemy):
> Pypapi:www.pypapi.org
> Camelot:www.python-camelot.com
> Qtalchemy:www.qtalchemy.org
>
> using PyGTK:
> Sqlkit: sqlkit.argolinux.org (also uses Sqlalchemy)
> Kiwi:www.async.com.br/projects/kiwi
>
> using wxPython:
> Dabo:www.dabodev.com
> Defis: sourceforge.net/projects/defis (Russian only)
> GNUe:www.gnuenterprise.org
>
> Pypapi, Camelot, Sqlkit and Dabo seem to be the most active and best
> documented/supported ones.
>
> > Finding a professional grade report designer looks like an even
> > bigger challenge.
>
> LibreOffice is imho quite useful for database reporting. It comes with a
> native (SDBC) driver for PostgreSQL and allows Python scripting.
> LibreOffice Base can even be useful for CRUD GUIs.
>
> > I don't need to port any applications, but I will need to use the
> > data (mdb/accede format),
>
> Don't. Put your data into an *actually* transaction-safe RDBMS (which
> "Jet" is *not*), such as e.g. PostgreSQL.
>
> > design a variety of reports with multi-level groupings, and deliver
> > them to many individual recipients via email.
>
> Sincerely,
>
> Wolfgang
>
>

Thanks to everyone who replied.

The reporting question is the one that gives me the greatest concern
when I think about switching to Python. I haven't seen a simple,
powerful report writer like Access has, or Crystal Reports, in Python.
Is generating XML / HTML a workable alternative? (In most cases, end
users don't need to design reports.)

Thanks again,

Keith

[toc] | [prev] | [next] | [standalone]

#33982

> The reporting question is the one that gives me the greatest concern
> when I think about switching to Python.

Not Python, but FOSS, cross-platform and it works with PostgreSQL:

http://www.xtuple.com/openrpt

Apart from that one, among the mentioned DB RAD frameworks, at least
Dabo and Camelot include report builders.

And, again; Libreoffice Base comes with a reporting framework, although
the latest version afaik has a currently unresolved bug, so you'll have
to retrograde to an older version if you want to use it.

Sincerely,

Wolfgang

[toc] | [prev] | [next] | [standalone]

#34060

[Multipart message — attachments visible in raw view] — view raw

* Wolfgang Keller  [2012-11-25 20:48 +0100]: 
>> 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.
>>
>> I am concerned that I'll have a hard time replacing the access form
>> and report designers. I've worked a little with TKinter, but it's a
>> far cry from the GUI designer in Access.
>
>The list of Python frameworks for rapid development of desktop
>(i.e. non-Web) database applications currently contains:
>
>using PyQt (& Sqlalchemy):
>Pypapi: www.pypapi.org
>Camelot: www.python-camelot.com
>Qtalchemy: www.qtalchemy.org
>
>using PyGTK:
>Sqlkit: sqlkit.argolinux.org (also uses Sqlalchemy)
>Kiwi: www.async.com.br/projects/kiwi
>
>using wxPython:
>Dabo: www.dabodev.com
>Defis: sourceforge.net/projects/defis (Russian only)
>GNUe: www.gnuenterprise.org
>
>Pypapi, Camelot, Sqlkit and Dabo seem to be the most active and best
>documented/supported ones.

I'd like to add to the list 

      Tryton http://www.tryton.org/

Which framework can be used to create a business application without
even using the already existing modules (one of our customer uses only
the 'party' modules in order to manage insurance products, the GNU
Health (http://www.gnuhealth.org/) project uses more official modules
to create their HIS software).

Reporting is done through relatorio (http://relatorio.openhex.org/),
which uses ODF templates to generate ODF reports (or other format
thanks to unoconv) the client is written in GTk (we're writing one in
JavaScript right now (and I miss python badly)).

-- 
(°> Nicolas Évrard
( ) Liège
   `¯

[toc] | [prev] | [next] | [standalone]

#34103

On 11/29/2012 02:46 AM, Nicolas Évrard wrote:
>
> I'd like to add to the list
> Tryton http://www.tryton.org/
>
> Which framework can be used to create a business application <snip>

Let me second this, although for openERP (the parent from which
Tryton was forked)...

> Reporting is done through relatorio (http://relatorio.openhex.org/),
> which uses ODF templates to generate ODF reports (or other format
> thanks to unoconv) the client is written in GTk (we're writing one in
> JavaScript right now (and I miss python badly)).

... for which the web client is fully up and running.

In any case it's a rich environment for business applications.

Emile

[toc] | [prev] | [next] | [standalone]

#33938

On 11/22/2012 08:19 PM, kgard wrote:
> 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.

Python is just a language, just like VBA itself is just a language.  You
can't just replace an MS Access VBA app with one in Python.  You have to
replace your *tools* with open source alternatives, that hopefully
python can glue together.  Wolfgang provided a nice list of such tools.

One program that claims to be working towards Access replacement is
Kexi.  It's not written in Python, but I think it does use Python as a
scripting language, just as Access uses VBA.  I doubt it's anywhere near
Access yet, but it's worth a look:

http://kexi-project.org/about.html


> <snip>
> 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?

It will not be painless at all.  There is no "transition" path, really.
 That's partly the result of Microsoft product lock-in, partly because
you want to replace a complete system that happens to be glued together
with, simply, "Python."

I think Python could be a great fit if you could find the right tools to
go with it, but it's not going to be easy at all.  Complete MS Access
replacements is one of the may extremely weak spots in the open source
world.  Partly because web-based apps often work better than a desktop
DB solution, and you might want to go there too, perhaps using a python
web development toolkit like django.

[toc] | [prev] | [next] | [standalone]

#33973

On Nov 26, 11:21 am, Michael Torrie <torr...@gmail.com> wrote:
> On 11/22/2012 08:19 PM, kgard wrote:
>
> > 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.
>
> Python is just a language, just like VBA itself is just a language.  You
> can't just replace an MS Access VBA app with one in Python.  You have to
> replace your *tools* with open source alternatives, that hopefully
> python can glue together.  Wolfgang provided a nice list of such tools.
>
> One program that claims to be working towards Access replacement is
> Kexi.  It's not written in Python, but I think it does use Python as a
> scripting language, just as Access uses VBA.  I doubt it's anywhere near
> Access yet, but it's worth a look:
>
> http://kexi-project.org/about.html
>
> > <snip>
> > 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?
>
> It will not be painless at all.  There is no "transition" path, really.
>  That's partly the result of Microsoft product lock-in, partly because
> you want to replace a complete system that happens to be glued together
> with, simply, "Python."
>
> I think Python could be a great fit if you could find the right tools to
> go with it, but it's not going to be easy at all.  Complete MS Access
> replacements is one of the may extremely weak spots in the open source
> world.  Partly because web-based apps often work better than a desktop
> DB solution, and you might want to go there too, perhaps using a python
> web development toolkit like django.
>
>

I understand your comment about replacing tools. Since things tend to
fall apart at the seams, though, I wouldn't mind keeping the seams to
a minumum. That's why I had been thinking about something like Django
or Web2Py. Web2Py seems to more correctly represent MVC, and I like
that its template scripting mirrors Python syntal.

Thanks for your reply.

I will now cower at my keyboard and await my "Django kicks Web2Py's
butt" lashing.

[toc] | [prev] | [next] | [standalone]

#33981

> One program that claims to be working towards Access replacement is
> Kexi.  It's not written in Python, but I think it does use Python as a
> scripting language, just as Access uses VBA.  I doubt it's anywhere
> near Access yet, but it's worth a look:
> 
> http://kexi-project.org/about.html

Unfortunately, Kexi doesn't support composite keys yet, so it's
essentially useless for real-world databases so far. :-(

Rekall appears to be dead and so does Knoda.

Sincerely,

Wolfgang

[toc] | [prev] | [next] | [standalone]

#33988

kgard <kagard@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

[toc] | [prev] | [next] | [standalone]

#33992

On 11/27/2012 05:06 PM, David Bolen wrote:
> 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.  
> <snip>
> Having done this, you are then free to start implementing, for
> example, a web based application to start taking over functionality.

Very informative post, David.  This is the only practical way forward,
and had I not been away from the field so long (been a couple of years
since I was in such a position of doing this professionally), I would
have remembered this!

MS Access is a very powerful tool; it just has a lousy default database
engine.  ODBC isn't a perfect interface, but Access does speak it, and
so does a real DBM like PostgreSQL.

There are a number of tools out there for converting an access database
to SQL schema (if you don't have access to the schema).  I used it on a
commercial program once to check on their database parameters (shudder
using mdb in a production program!), and actually had a script to sync
it to read from it via ODBC and push it to a MySQL database.

All this reminds me of a project I wanted to do, but hit a road block.
My problem was I had a proprietary binary app, with an MDB data file (in
the program directory no less). I'd like to share it across users,
without worrying about corrupting the database, or even have a web front
end.  If I could set it up as David suggests with some kind of live
mirroring, and so that the program didn't know it was a database server
it was talking to, that would be good.  But I had no control over the
ODBC parameters (they were hard coded in the program).  Would have loved
to have separated out the access database part from the rest of the
program though.  I could then graft on a web front end.

thanks again for the post, David.

[toc] | [prev] | [next] | [standalone]

#34069

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

[toc] | [prev] | [next] | [standalone]

#34073

On 11/29/2012 09:05 AM, Joel Goldstick wrote:
> This looks promising:
> http://www.codediesel.com/data/migrating-access-mdb-to-mysql/

Unfortunately I have not found mdb tools to be sufficient.  You can use
them to convert the schema to sql, and to reveal any mdb password (great
for looking at the data structures of compiled apps), but it can't
handle all the data types properly.  To get data out of an mdb file, I
wrote a simple python program that used pyodbc to get the data.  pyodbc
implements a standard python db api interface.  I opened the access
database file with:


MDB = 'C:/Path/to/frs_or_mdb_file'
DRV = '{Microsoft Access Driver (*.mdb)}'
PWD = 'ifneeded'

conn = pyodbc.connect('DRIVER=%s;DBQ=%s;UID=admin;PWD=%s' % (DRV,MDB,PWD))

curs = conn.cursor()

Then you can run queries with standard python db abi calls in standard
SQL syntax, and it's pretty easy to pull out the data and insert it into
a MySQL or PostgreSQL database.

This is for python on Windows of course, and has to have Access
installed, or at least the access engine.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web