Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Upper Limit for Recordsets?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Great - thanks.

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •