setting recordset on form hangs access 2

[1DMF]

Hi,

Can someone explain to me why after applying a filter to a DAO.recordset and then using

Set Me.accounts_search_results.Form.Recordset = rs.Recs

MS Access hangs and has to be killed.

Yet if prior to setting the form record source I include

rs.Recs.MoveLast

It works fine, so What gives?

thanks,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[PWise]

MajP and 1DMF

Just gave you stars because I enjoyed this refreshing conversation about the not much used form recordset property.

I work in an ADP and use a lot of ado recordsets for forms, combo boxes ,List Boxes.

 

[1DMF]

well what do you know, it's not me for once!

At least I have a workaround which isn't detremental to the user experience, but it certainly did my head in tracking down the problem!

I might have a go at refactoring the wrapper class to use ADO and see how that pans out.

Am I likely to see any performance improvements?

It's not like it is currently slow and needs speeeding up, but any improvement has to be a good thing.

Do you have any pointers for ADO refactoring.

Thanks for your help MajP

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[MajP]

Actually my issue is slightly different now that I investigated, but maybe similar.
My problem was that I filtered a recordset based on the recordset returned by the forms recordset property. If instead you return a recordset, bind that to a form, and then create a new recordset using openrecordset there is no problem. What is more like you are doing. The issue appears to be in returning the recordset from the form.

However, there are some real specifics especially with ODBC. This reference for DAO is really good.

http://oreilly.com/catalog/progacdao/chapter/ch08.html#19043

However this will be the best 1.34 you ever spent:
access desktop developers handbook for sale

http://www.google.com/products/cata...hT_uqAcOx0AHb1sD_CA&ved=0CGEQ8wIwBA#scoring=p

I do not know if they have something more current, but this is the bible for developing in Access. Has a very good discussion of ADO and DAO.

I think if this was me, I would not try to create a DAO filtered recordset and bind it to the form. Seems to be a lot of nuances doing it that way. Unless you got this working.
Either return a filter string and apply that to the form
Use ADO
Build the filtered recordset from a query

There are certain things that ADO does very well and some that DAO does. So you may get better performance in some places and less in others. My guess is this specific problem will be fixed.

Also noone says you can not use both within the same application.

 

[1DMF]

Thanks MajP.

What I know does crash is if you bind a recordset object and then try to use the form filtering!

That kept giving me 'Access has stopped responding' application death.

Anyway, I have it running fine and as long as you call 'MoveLast' after filtering the clone, it works like a charm.

I don't think the overhead of the clone is a biggie, and at least my wrapper now gives me the power i need to implement easy solutions now that I am moving away from bound queries through linked tables and am using recordset objects via SQL stored procedures.

The performance increase using my wrapper class as opposed to the old bound query / linked table method is phenomenal, so putting up with a few MS Access nuances is a small trade of, hey it's access of course it has 'issues'!

As always obliged of your input.

P.S.
Thanks PWise, glad at least you were entertained! I was pulling my hair out till I solved the problem




"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[henew]

1DMF seems like you solved the problem but you don't really realize why, I think that code somewhere behind the "set form.rst = object.rst" (which probably has to make deep copy of the rst)tries to allocate memory for form.rst of a size based on object.rst.RecordsCount property => this property just at once after creation of a recordset object is set to one (bookmark of the recordset is at the first record), but (assuming) there are more records in this recordset (to little memory allocated to make a deep copy), when you perform object.rst.MoveLast the object.rst.RecordsCount is set to the correct value - therefore the memory is allocated with the right amount of memory and program doesn't hang because of wrong memory being referenced
in my opinion it is a small, overlooked bug

 

[MajP]

I think that code somewhere behind the "set form.rst = object.rst"(which probably has to make deep copy of the rst)

For sure it does not make a deep copy, it does not even make a shallow copy, it simply sets a pointer to the recordset object. This can be easily verified.