Upper Limit for Recordsets?

[JHSam]

I have encountered an overrun problem working with an Access recordset in an Excel VBA program (I'm using ADO). The recordset is large (1,000,000 values) but the Loop that I use to run through the recordset can't get much past 32,700 values (I get a Run-time error "6": Overflow). Am I approaching one of the upper limits of the recordset (I haven't found any documentation for this)? I am creating the recordset from a SQL string, which works OK within Access. Here's the code string that loads the recorsdet:

rst.Open "SELECT (Table).(Field) FROM (Table);", cn, adOpenForwardOnly, adLockReadOnly, adCmdUnknown

Any suggestions would be appreciated.

Thanks.

 

[dk]

I have encountered an overrun problem working with an Access recordset in an Excel VBA program (I'm using ADO). The recordset is large (1,000,000 values) but the Loop that I use to run through the recordset can't get much past 32,700 values (I get a Run-time error "6": Overflow). Am I approaching one of the upper limits of the recordset (I haven't found any documentation for this)? I am creating the recordset from a SQL string, which works OK within Access. Here's the code string that loads the recorsdet:

rst.Open "SELECT (Table).(Field) FROM (Table);", cn, adOpenForwardOnly, adLockReadOnly, adCmdUnknown

Any suggestions would be appreciated.

Thanks.

Hi,

According to the MSDN CD there is no limit to the numbers of records you can store in an ADO recordset - it's just limited by your hardware capabilities. I think your problem is because the variable you're using to loop through the recordset has been defined as Integer. Integer can only store numbers up to 32,767. Define the variable as Long (maximum value of 2,147,483,647) and the problem should disappear.

If this doesn't work then it may be worth posting your full code.

Regards,
Dan

 

[JHSam]

[No message]

 

[JHSam]

Great - thanks.

It's always something simple, ya'know ...?