Scripting MS-Access from PythonCom

[Christian Tismer]

Howdy,

I had a quick look into this.
Here a solution, not nice but *OK*.

BORGHI BRUNO wrote:
> 
> I'm writing a small app creating a MS-Access database from csv files, using
> OLE automation through Pythoncom.
> So far, so good. I can create a database, populate it, create computed
> reports, print them. Nice.
> There is one thing I still can't do: setting the margins in a report.
> 
> There is a property of reports named 'PrtMip' that is supposed to be used
> for that. MS-Access documentation says that PrtMip is a string packing a
> struct of 14 short integers.

Here is the problem: PrtMip is a structure. If you look into the demo
code of the MSAccess help, you see that they read and write it as some
fixed size string and transfer the data using LSet forth and back.
BTW: There are 14 long words, so 56 bytes is *ok*.
But VBA seems to allocate 16 bits for the strings internally.

> I can get the value of PrtMip, although report.PrtMip gives me a tuple of
> 56 integers, corresponding to the bytes of 14 long integers..
> But I don't know how to set a new value. I tried a tuple of 56 integers, of
> 14 integers. Doesn't work. I tried some variations about a string packing
> integers. Doesn't work either.

Yes, I tried it, the bytes are read in, but writing to the structure
gives always an error, and later on the system complains about too
few memory. I gues this is a limitation of PythonCom, which appears
to handle fixed string structures different than VBA expects? Mark?

> My questions are :
> 1/ Does anyone ever tried to set the margins of report in MS-Access through
> Pythoncom and could tell me how to achieve this ?
> 2/ I don't understand why report.PrtMip returns a tuple of 56 integers,
> rather than a tuple of 28 integers. Can someone explain ?

I guess this is an internal con(ver|fu)sion in Python's COM
interface, but here is a quick and not so dirty solution.

Access.Application.8 exports a "Run" method which is able to call an
arbitrary sub or function from Access. WHile I don't like to
recommend to use VBA, in this case it is a quick solution to write
a little module which provides a VBA proxy function for you.

The Python module wraps a little structure around a 14 element integer
list. It provides a read and write method to an opened Access report.
FOr the communication with Access, it creates some proxy functions
in VBA which transfer data as 14 word variant arrays.

I also managed to create the Access proxy on demand :-)

good luck - chris

----------------------------------- snip
----------------------------------
# PrtMip
# helper functions to access some special structures in MS Access 98
# CT 991012

import string

class PRTMIP:
    __members__ = string.split("""
        LeftMargin
        TopMargin
        RightMargin
        BotMargin
        DataOnly
        Width
        Height
        DefaultSize
        Columns
        ColumnSpacing
        RowSpacing
        ItemLayout
        FastPrint
        Datasheet
        """)
    
    def __init__(self):
        self.data = len(self.__members__)*[0]

    def __getattr__(self, key):
        if key == "data":
            return self.__dict__["data"]
        lis = self.__class__.__members__
        return self.data[lis.index(key)]

    def __setattr__(self, key, value):
        if key == "data":
            self.__dict__["data"] = value
            return
        lis = self.__class__.__members__
        self.data[lis.index(key)] = int(value)

    def display(self):
        data = self.data
        for attr in self.__class__.__members__:
            print "%s=%d" % (attr, getattr(self, attr))

    def read(self, accessobj, opened_reportno):
        """read data from a report. It must have been opened in design
mode"""
        autoload(accessobj)
        data = accessobj.Run("GetPrtMip", opened_reportno)
        self.data = list(data[0])

    def write(self, accessobj, opened_reportno):
        """write data to a report. It must have been opened in design
mode"""
        autoload(accessobj)
        accessobj.Run("SetPrtMip", opened_reportno, self.data)

# demo session

"""
>>> import win32com.client
>>> a=win32com.client.Dispatch("access.application.8")
>>> a.Visible=1
>>> a.OpenCurrentDatabase("d:/tmp/db1.mdb")
>>> a.DoCmd.OpenReport("ber",1)
>>> p = PRTMIP()
>>> p.read(a, 0)
>>> p.display()
LeftMargin=1500
TopMargin=1440
RightMargin=1440
BotMargin=1440
DataOnly=0
Width=14004
Height=384
DefaultSize=1
Columns=1
ColumnSpacing=360
RowSpacing=0
ItemLayout=1953
FastPrint=1
Datasheet=1
>>> p.LeftMargin = 1440 + 770
>>> p.write(a, 0)
"""

# Access module

access_module_glob = """
' these are there by default
' Option Compare Database
' Option Explicit

Type str_PRTMIP
    strGZF As String * 28 ' unicode
End Type

Type long_PRTMIP
    longGZF(14) As Long
End Type

Sub SetPrtMip(repno, array14)
    Dim buffer As long_PRTMIP, rebuff As str_PRTMIP, i As Integer
    For i = 0 To 14 - 1
        buffer.longGZF(i) = array14(i)
    Next
    LSet rebuff = buffer
    Reports(repno).PrtMip = rebuff.strGZF
End Sub

Function GetPrtMip(repno)
    Dim ret As str_PRTMIP, conv As long_PRTMIP
    ret.strGZF = Reports(repno).PrtMip
    LSet conv = ret
    GetPrtMip = conv.longGZF
End Function

Function I_have_been_loaded()
    I_have_been_loaded = 42
end Function
"""

# Auto-loader for Module
def autoload(accessobject):
    x = 0
    a = accessobject
    try:
        x = a.Run("I_have_been_loaded")[0]
    except: pass
    if x != 42:
        from win32com.client import constants
        a.RunCommand(constants.acCmdNewObjectModule)
        mod = a.Modules(len(a.Modules)-1)
        mod.AddFromString(access_module_glob)
        x = a.Run("I_have_been_loaded")
        if x != 42:
            raise ValueError
----------------------------------- snip
----------------------------------

-- 
Christian Tismer             :^)   <mailto:tismer at appliedbiometrics.com>
Applied Biometrics GmbH      :     Have a break! Take a ride on Python's
Kaiserin-Augusta-Allee 101   :    *Starship* http://starship.python.net
10553 Berlin                 :     PGP key -> http://wwwkeys.pgp.net
PGP Fingerprint       E182 71C7 1A9D 66E9 9D15  D3CC D4D7 93E2 1FAE F6DF
     we're tired of banana software - shipped green, ripens at home