Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: This makes no sense

    So, in Access 2010, I open an ADO recordset returned from a stored procedure in SQL server and cache it (disconnect it from the datasource) and set it to be the recordsource for a form.

    Works great - or so I thought.

    The rs has 9194 records, loads really fast and populates nicely. As I scroll back, it continues to work nicely - I can see all of the records.

    But, I cannot select records before a certain point. The earlier records are shown, but I can not select them.

    If I use the record nav buttons built into the form, I can move backwards until I hit a wall.

    If I press the "Goto First" button - and it jumps to a record much earlier in the rs (which I actually can select) but if I select an adjacent record, it jumps way forward.

    If I press "Goto First" then repeatedly "Goto Next" it jumps and skips whole groups of records until it comes to that "wall" I mentioned above, after which it moves forward 1 record at a time.

    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Not really sure about this, but from my experience of other programming languages, it sounds like your overflowing the cache. So access is taking a 'snapshot' of the details, but not actually caching the records for them.

    If you cache less records, does the 'wall' change? i.e. Do you always have the same number of accessible records for this table?

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Honestly, I cannot answer your question.

    Yesterday, I applied the Cache parameter to a number significantly bigger than what I need (but still well within the computer, database and object capabilities) and persisted in seeing the same problem.

    This morning, I switched to DAO recordsets for the form, which worked but introduced other challenges/limitations (such as requiring ODBC and PassThroughs).

    I switched it back to ADO tried again (so I could test limiting the row count returned by the sp) and discovered it now works, correctly.

    Should I rejoice because it works now or worry because I did not resolve why it wasn't working?
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Found it

    Aha! Right after I posted this, I went to do some final clean up and discovered the problem came back. This time, though, I only had about 4 small changes between working and not working so it was a lot easier to discover the cause.

    An interesting feature/problem with ADODB recordsets:

    When you CLONE a recordset and then apply a filter to the clone, it apparently also applies to the original. However, the form does not quite catch up.

    I'm pretty sure that is not the case with DAO recordsets which allow your to filter the clone without affecting the original.

    I have a function that uses filters on a clone of the form recordset to provide summary stats. What was happening was the last filter applied to the clone was also applied to the original rs. The form did not refresh and still showed all the records, but did not let me select the records not present in the filtered rs.

    So, for the fix, I simply remove the filter from the clone before setting the clone equal to nothing.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Again, I can't speak for access, but in general, when altering the cache size of a running program, the changes will not take effect until either the program or the computer (or at least the OS) is restarted.

    The Access cache is using physical memory, and memory has to be reserved on startup, more available memory being added at runtime is not usually possible (although exceptions do exist).

    So it's possible that you increased the size, continued editting and getting a headache, went home, came back the next day, the PC and/or Access were restarted, and BAM! more cache memory available, and so no more issues.

    Sound likely?

Posting Permissions

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