Table Type Recordset Hangs

[Michael J. Strickland]

Just recently, after a clean re-install of Office 2007, SP1, SP2, and
updates
through about mid-August, Access 2007 started hanging at the end of
processing table-type record sets.

The programs finish processing the record set, and the tables involved
are updated appropriately, however the programs do not exit. Access
either hangs or encounters an extremely long delay after finishing. I
noticed on smaller tables (i.e. < 5000 records, Access would just delay
for a number of seconds and then exit.

On larger tables, when it hangs, I must end Access via Task manager, and
restart it, at which time the database has ballooned to about 10 times
its size. The table involved would show all the changes from the VBA
routine, and after a Compact/Repair, the database would return to its
normal size.

It appears to be some sort of string/garbage collection process run
amok.

Changing to dynaset record sets, solved this problem, however I have
been using the default (Table type) record sets for about 10 years
without experiencing this problem.

Using:
Set rstTable = CurrentDb.OpenRecordset(mstrTableName, dbOpenDynaset)
rstTable.MoveLast
rstTable.MoveFirst

instead of

' Set rstTable = CurrentDb.OpenRecordset(mstrTableName)

solves the problem.

Anyone else experience this problem?

 

[Allen Browne]

It will be interesting to see if anyone else has experience this, Michael.

I have not, but then I never use dbOpenTable, since it doesn't work with
attached tables, and I always design database so they work when split.

If you want to narrow it down, it may be worth testing whether it makes any
difference if:
a) Name AutoCorrect is off, or
b) the database is opened exclusively (rather than shared)
c) you explicitly both close the recordset and deassign your objects, i.e.:
rstTable.Close
Set rstTable = Nothing
Set db = Nothing

To see where the problem occurs, it may also be worth adding a few lines
such as:
debug.print "About to close recordset at " & Now()

I'm assuming this is a local JET table, not some table from another database
where you used OpenDatabase().

 

[Michael J. Strickland]

Allen:

Thanks for replying.

In answer to your questions:
a. Name Auto-correct is off.
b. Database is opened exclusively (I am the only user) with No locks.
c. I did try explicitly releasing (destroying) the record set and
database
objects with no effect.

Upon further investigation, however, I noticed that the setting:
"Open databases by using record-level locking" was checked. I must have
previously been running with this setting cleared although selected
seems to be the default.

After un-checking this box, the problem goes away and table type record
sets process quickly again (do not hang).

This setting seems redundant. Above it, I had already selected:
"Default Open Mode" = "Exclusive".
"Default Record Locking" = "No Locks"

It seems to me that selecting "No Locks" or "All Records" should disable
(grey) this checkbox.


Btw, the problem seems to be that the .EOF flag was not being set when
executing a .MoveNext from the last record. Therefore, the loop (While
Not rstTable.EOF ...) runs an extra iteration. VBA hangs on the next
..MoveNext statement (without any error).

 

[Allen Browne]

Wow: thanks for posting the solution.

If I understand correctly, the 'record-level locking' has to do with the way
the page buffer is assigned. In the Unicode versions (A2000 and later), this
is a 4k page buffer. If you don't select this, Access loads as many records
as fit into the page; if you do, it loads only a single record into the
page.

It would therefore seem that there's a bug related to recognising when the
final record has been reached in a DAO recordset of type dbOpenTable, but
only when the records are loaded individually into the buffer.

I've found I have to always turn this setting off anyway, as there are (or
were) other bugs associated with it.