Linux guy seeks help w/MS Access/Python

[Alex Martelli]

"Albert Wagner" <alwagner at tcac.net> wrote in message
news:39A16CDD.ACB7B10D at tcac.net...
> I'm on a linux system, but have just taken a job in a windows shop. Is
> there a way to manipulate MS Access within Python?  Can you point me
> towards appropriate docs?

Python is very well integrated with just about everything on
Windows, thanks to Hammond's work in the 'win32all' Windows
extensions for Python (freely downloadable from the Python
site) and, it must be admitted, to Microsoft's work in making
COM (the Component Object Model) such a Windows lingua
franca.  To summarize: with the win32com package (part of
Hammond's win32all), Python can implement COM objects and
drive other COM servers; and, just about any functionality
you can think of in Windows, be it system or application level
functionality, it's exposed and can be driven via COM.

Hammond's book "Win32 Programming with Python", edited
by O'Reilly, is a good introduction to Python's abilities on
Windows (including COM).  http://msdn.microsoft.com, the
MSDN site of Microsoft, has docs on just about anything that
Microsoft has ever done on/for Windows of any sort; you
probably have a decent subset of MSDN installed on your
system, as it's also the online docs for Visual Studio and
other Microsoft development products, but the online version
is always more complete and up to date.

Coming to your specific question: I suspect you don't really
want to manipulate "MS Access" (the development-oriented
front-end for database access that is part of Microsoft Office),
but rather the relational databases which some Access apps
may also be driving.  If so, then 'MSDAC', the Microsoft
Data Access Components, are what you really need; you can
get at ADO (the richest, most complete object-model for data
access, that Microsoft has been pushing for years as part of
its UDA, Universal Data Architecture) or at older solutions such
as DAO or ODBC (for the latter, look at mxODBC, which is NOT
part of win32all; it has the advantage of complying with Python's
standard database API, so you can later move to other DB
access technologies with little pain, if need be).  I suggest the
'ADO 2.5 Programmer's Reference' published by WROX: it's
a really excellent little book (and also covers ADOX, which lets
you manage DDL and security issues connected with databases
through an ADO connection to the DB).

But if you really want to drive Access itself, the front-end,
yes, you can do that too, with win32all.  See Article ID:
Q147816 in the MSDN's 'Knowledge Base' for a summary;
it's expressed in VB terms, but so is most Automation
related information -- you'll need to pick up 'just enough
VB to get by' to follow the docs.

The type library (if you want to wrap it with genpy, which
is advisable!) is "Microsoft Access 8.0 Object Library".
>From PythonWin, Tools/COM Makepy Utility, will makepy
that library.  However -- you'll hit a peculiar bug, not even
quite a bug, in makepy when you do that.  That typelib has
several names starting with __IVba, __Editor, etc -- i.e.,
*two underlines*.  makepy reproduces them faithfully in
the Python file it generates (win32com\gen_py\
EAB22AC0-30C1-11CF-A7EB-0000C05BAE0Bx0x1x1.py).

Unfortunately, when Python, importing that file, sees names
starting with two underlines used within a class, it takes it
as "private to the class" -- and inserts the class's name in
between the first and second of the underscores.  As a result,
there will be errors when the module is imported, including
the automatic-import from the menu-entry itself.

Not to worry -- just edit the .py file thus generated, changing
each occurrence of __ at the start of a word (which does not
also *end* with __) into something else.  Now you can
successfully run:

gencache.EnsureModule("{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}", 0, 8, 0)

and have all the makepy support for Access.  Note
that the test\testAccess.py that comes with win32all
has this line commented, clearly because the fixed
regarding the two-starting-underlines were not applied
to the generated .py file so the import would fail.


You can now instantiate the only externally creatable
coclass of Microsoft Access...:

ap=win32com.client.Dispatch('Access.Application.8')

and through this instance, call whatever functionality
Access exposes to you.  For example,
    ap.Eval('2+2')
will return as its result the integer number 4.

>>> ap.SysCmd(win32com.client.constants.acSysCmdAccessDir)
'C:\\PROGRAM FILES\\MICROSOFT OFFICE\\OFFICE\\'

And so on, and so forth.  The previously mentioned entry
in the MSDN's "Knowlegde Base" has several useful examples,
and the MSDN itself has a hundred more times' worth of
docs than one'll ever find the time or energy to read, let
alone study:-).


But I'd bet that you don't really want to drive Access, but
rather the underlying databases... would I win?-)


Alex