ADO error - large data set

[Gyula]

Ok. After several tries, I think I found out why it breaks and it has
nothing to do with the number of records...

Here is the code/ see notes below:

######################### code starts here
# First import wincom32 client
from win32com.client import *

# Create the ADO Connection object via COM
oConn = Dispatch(r'ADODB.Connection')

# Now set the connection properties via the ConnectionString
oConn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" + \
"DATA SOURCE=C:/Documents and Settings/user/Desktop/PythonWS/data/
anydata.mdb;"

# Now open the connection
oConn.Open()

# create a recordset
rs = Dispatch(r'ADODB.Recordset')
rs.Open('SELECT TOP 300 * FROM input;', oConn, constants.adOpenStatic,
constants.adLockOptimistic)

rs.MoveFirst()
counter = 1
while not rs.EOF:
    print counter   # do nothing, just cycle through
    counter += 1
    rs.MoveNext()

# cleanup
rs.Close()
rs = None
oConn.Close()
oConn = None
print 'Done!'
##################### code ends here

the line where it breaks:
rs.Open('SELECT TOP 300 * FROM input;', oConn, constants.adOpenStatic,
constants.adLockOptimistic)

'input' is supposed to be the table name, but it breaks in Python when
you want to open the recordset.

I need to use:
rs.Open('SELECT TOP 300 * FROM [input];', oConn,
constants.adOpenStatic, constants.adLockOptimistic)

as input is an SQL keyword...arrrrrgh.

Gyula


On Mar 19, 5:17 pm, Gyula <gygul... at gmail.com> wrote:
> Thanks! I will give it a try. It seems though that I get stuck on
> rs.Open that makes no sense. I was wondering about pagesize or other
> registry settings that might cause this? Will try to track down any
> bad data first...
> gg
>
> On Mar 19, 3:27 pm, "dsavitsk" <s... at ecp.cc> wrote:
>
> > Is it possible there is some bad data in the larger db? This is asinine, but
> > maybe write a small script that adds some data, then opens and closes the
> > db, then repeats this. If this is a size issue, then you can at least narrow
> > it down to where the size limit is? And, if it isn't you should be able to
> > figure that out, too. Otherwise, play around with the locking and cursor
> > options.
>
> > -d
>
> > "Gyula" <gygul... at gmail.com> wrote in message
>
> >news:475c155d-fe16-472f-a4f8-363f6db339eb at d21g2000prf.googlegroups.com...
>
> > > Hi there,
>
> > > I have been running Python to tap into an MS Access 2003 database
> > > using ADO (PythonWin+COM). Everything works great creating recordsets
> > > etc. when I open a table with a small number of records. However, when
> > > I try to run the same Python code with a large table (>100,000) I get:
>
> > > Traceback (most recent call last):
> > >  File "C:\Python25\Lib\site-packages\pythonwin\pywin\framework
> > > \scriptutils.py", line 310, in RunScript
> > >    exec codeObject in __main__.__dict__
> > >  File "C:\Documents and Settings\user\Desktop\PythonWS\scripts
> > > \readmsaccess.py", line 43, in <module>
> > >    rs.Open('SELECT * FROM ' + tblname, oConn, 1, 3)
> > >  File "C:\Python25\lib\site-packages\win32com\gen_py\2A75196C-
> > > D9EB-4129-B803-931327F72D5Cx0x2x8.py", line 2364, in Open
> > >    , ActiveConnection, CursorType, LockType, Options)
> > > com_error: (-2147352567, 'Exception occurred.', (0, None, None, None,
> > > 5003251, -2147467259), None)
>
> > > The small and large table structures are identical, all I do is change
> > > the tblname from input1000 (1000 records) to input (>100000 records).
> > > I use optimistic locking and keyset cursor..nothing out of the
> > > ordinary?
>
> > > Any ideas? ADO 2.8 is what I am using.
>
> > > Thanks a lot!
> > > GG

'