[python-win32] driving MS Access from python

[Tim Roberts]

Phill Atwood wrote:

>
> Well, painfully I'm making some progress.  And I appreciate your help
> in this.  I think my previous problem was because I was doing
>
> rs.Open(sqlstmt)
> instead of:
> rs.Open(sqlstmt, conn)
>
>> To execute a generic SQL statement, you create an ADODB.Command
>> object and connect it to the Connection:
>>
>>     cmd = win32com.client.Dispatch("ADODB.Command")
>>     cmd.ActiveConnection = conn
>>     cmd.CommandText = "SELECT COUNT(*) FROM committees;"
>>     rs = cmd.Execute[0]
>>
>>
>> Now rs is a recordset.
>
>
> Now, I've got your above example working. Almost.  For me
>
> rs = cmd.Execute[0]
> failed with
> TypeError: unsubscriptable object


Duh, yes, I typed this from my code instead of cutting and pasting.
    rs = cmd.Execute()[0]
is what I meant, which does the same as what you have.


> Problem is when I then try
>
> data = rs.GetRows()
>
> I get
>
> Traceback (most recent call last):
>  File "<pyshell#59>", line 1, in -toplevel-
>    data = rs.GetRows()
>  File
> "C:\Python24\lib\site-packages\win32com\gen_py\2A75196C-D9EB-4129-B803-931327F72D5Cx0x2x8.py",
> line 2333, in GetRows
>    , Start, Fields)
>  File "C:\Python24\Lib\site-packages\win32com\client\__init__.py",
> line 446, in _ApplyTypes_
>    return self._get_good_object_(
> com_error: (-2147352567, 'Exception occurred.', (0, 'ADODB.Recordset',
> 'Operation is not allowed when the object is closed.',
> 'C:\\WINDOWS\\HELP\\ADO270.CHM', 1240653, -2146824584), None)


I'm not sure why you'd use GetRows.

    while not rs.EOF:
        print rs.Fields("FieldName1")
        rs.MoveNext()

I often write a small wrapper around the recordset which turns it into a
generator, because inevitably, on the first round, I forget the MoveNext
and get an infinite loop:

    def Recordset(cmd, sql):
        cmd.CommandText = sql
        rs = cmd.Execute()[0]
        while not rs.EOF:
            yield rs
            rs.MoveNext()

    for row in Recordset( cmd, "SELECT FieldName1 FROM MyTable" ):
        print rs.Fields("FieldName1")

-- 
Tim Roberts, timr at probo.com
Providenza & Boekelheide, Inc.