ADO error - large data set

[Gyula]

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

 

[dsavitsk]

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]

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

 

[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

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

'