Access keeps crashing when perfomring a filter against DAO.recordet 1

[1DMF]

Hi,

I've bound a DAO recordset to a form to try to speed things up by keeping the recordset in memory.

However, as I try to apply a filter to the recordset , access just crashes.

Is it possible this is still down to SP1?

I've applied the hotfix (SP2), but ever since SP1 , Access just keeps crashing or not working properly.

Any ideas why the filter would crash the DB?

"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]

I've bound a DAO recordset to a form to try to speed things up by keeping the recordset in memory

I cannot imagine that does anything. Loading a recordset and then binding to a form should not be faster than loading a form with a recordsource and letting it create it owns recordset.

If you a leaving the recordset persistent, I would think the easier thing is to simply leave the form open and hidden when you do not need it.

As for the filter can you post the code for creating the recordset and the code for filtering the recordset, and the filter string?

 

[1DMF]

I got the impression that the recordset of a bound form is not memory resident but a VBA recordset variable is.

Is this not the case?

The code that crashes is...

Dim rs AS DAO.Recordset
Set rs = CurrentDB.OpenRecordset("spCommunicatorSearch")

Set Me.Recordset = rs

I have an unbound input on the parent form with the folowing code onchange event

Sub Do_Search(Optional var As String)

    Dim srch As String
    
    srch = ""
'Company Name
    If var = "Company" Then
        If Nz(Me.Company.Text, "") <> "" Then
                srch = "CompanyName LIKE '*" & Replace(Me.Company.Text, "'", "'") & "*'"
        End If
    Else
        If Nz(Me.Company, "") <> "" Then
               srch = "CompanyName LIKE '*" & Replace(Me.Company, "'", "'") & "*'"
        End If
    End If
    
    If srch <> "" Then
       Forms!HLP_Search.search_results.Form.Filter = srch
       Forms!HLP_Search.search_results.Form.FilterOn = True
    Else
       Forms!HLP_Search.search_results.Form.FilterOn = False
    End If

End Sub

"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]

I would just hide the form.

One thing. I think this does nothing.
Replace(Me.Company.Text, "'", "'")
I think you want
Replace(Me.Company.Text, "'", "")

Also there is a pecularity with using the currentdb function.
See this article.

http://www.accessmonster.com/Uwe/Fo...g/20638/What-s-Benefit-of-Dim-db-vs-Currentdb

at a minimum change the following (to be safe)

To
dim db as dao.database
dim rs as dao.recordset
set db = currentdb
set rs = db.OpenRecordset("spCommunicatorSearch",dbopendynaset)

I am suprised you could even attach the table to the form without specifying the type. I tried and it failed.

There are different types of DAO recordset, with different methods.

When you OpenRecordset() on a query or attached table, Access defaults to a Dynaset type (dbOpenDynaset). When you OpenRecordset() on a local table, it defaults to a Table type (dbOpenTable.) As far as I know you can not bind this to a form

The Table type has different methods (e.g. Seek instead of FindFirst), but it cannot be used with attached tables. So if you later split your database so the tables are attached, the code fails when you use a method that no longer applies.

Solution:

Always specify the type you want. Dynaset guarantees your code will work for all queries and tables, local and attached. Example:

Set rs = db.OpenRecordset("Table1", dbOpenDynaset

 

[1DMF]

lol , looks like the TT engine removed one of my single quotes when I copy pasted!

I assure you it has one single quote to be replaced with two ;-)

I alos had the type as DbOpenSnapshot, as I only need a readonly copy.

well that's what i thought the difference was between dynaset and snapshot , though you still need dbseechanges if you want to edit the data in the recordset don't you?

Anyway, instant crash as soon as the filtering is applied!

I've given up with DAO.Recordset, it's unstable in Access 2010,

So have gone back to just linking direct to the table (well query) and deal with duplicates via filtering, it's faster and more stable.



"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]

Applying a filter to a form is equivalent to filtering the recordset. This is almost never faster than opening a new recordset based on a sql query with a where clause. I would think the way you are doing it now would have been slower than just creating the new recordset.

I doubt it is DAO that is unstable. You can try applying the filter directly to the recordset and not the form (again this should be slower than just creating a new RS)

example (assuming an open global RS)

RS.filter = "some filter string"
Set RS = RS.openrecordset '(opens the recordset with filter applied)
Set Me.recorset = RS

 

[1DMF]

surely it can't be efficient to keep getting a new recordset with a where clause than filtering an exisitng record set?

constant requery of the SQL db on each keystroke can't be good on network traffic can it.

I am reading what you said right aren't I?

--------------------------------------------
keep running the SP with a new @param into a record set and attaching that to the form is better than filtering an already fetched and bound recordset?
--------------------------------------------

Of course this is assuming i can get the filtering of the recrod set to work without crashing.







"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]

AKAIK Yes. Test it and see.

In many cases, it's faster to open a new Recordset object by using an SQL statement that includes a WHERE clause than filtering an open recordset.

 

[1DMF]

wow, doesn't sound logically right, but hey, i trust you!

On the other problem of crashing, I have narrowed it down to crashing when performing a filter on the form when the record set is DAO.Recordset and not a direct form source.

Filtering the recordset using the rs.filter notation via VBA code works fine.

Perhaps another corruption from Office 2010 SP1?

I must say I was impressed with how fast the filtering runs, though I'm still a little green with all this so would appreciate your help further.

Once the filter is applied to the recordset, I can't seem to remove it to perform a different filter?

I found this thread

http://bytes.com/topic/access/answers/557302-filter-recordset

which implies you have to keep a separate holding variable of the full recordset and move the record set to another recordset variable for perfoming filters, when you want to apply a new filter, you have to go back to the full recordset and clone it again and then perform the filter again?

Is this correct?

I came up with this test

    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    
    Dim db As DAO.Database
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("spCommunicatorSearch")
    Set rs2 = rs.Clone
    rs2.Filter = "(MembershipLevel = 'Appointed Rep') OR (ContactTypeID = 'Prospect - AR')"
    Set rs2 = rs2.OpenRecordset
    Set Me.Recordset = rs2
    MsgBox "pause"
    Set rs2 = rs.Clone
    rs2.Filter = "(MembershipLevel = 'Adviser')"
    Set rs2 = rs2.OpenRecordset
    MsgBox "pause 2"
    Set Me.Recordset = rs2

Which seems to be working, is this the correct way of handling recordsets and filtering against them using a master record set and then a separate filtering recordset?

Seems a bit clunky and will mean having to rewrite the entire search functionality, but if this is the best way and correct way of doing it, then i'm all ears!

Thanks for your help MajP, as always its been very usefull and enlightening.

regards,
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

 

[MajP]

If I remember correctly that is how you would have to do it. Again, I doubt this design will improve performance. I still think just changing the recordsource will be the fastest. However, if you are just doing this for academic reasons, then why not use ADO and bind an ADO recordset? I think the filter can be added and removed.

 

[1DMF]

ADO ? When I had to remove all hard binding to Office to allow it to work with any version, and use 'late binding' I was shown/told to use DAO.

What's the difference between ADO & DAO , and what benefit does one have over the other?

I have yet to try the requery method you mention, I need to go learn how to change my SP to include parameters and then how to edit my passthrough query to use the parameters, before i can then try your suggested requery method.

I'll have a go later this afternoon, got web development as the priority currenty and the replacement router from BT has turned up this morning so need to get the load balancing reconfigured for the dual wan setup we are meant to be running, but has been down for three weeks!

I go on holiday and everything breaks! At least it shows the boss this stuff doesn't just run itself and needs maintenenace!

"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

 

[1DMF]

Well this is what I tested with...

Private Sub test()

    Dim strSQL As String
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim rs As DAO.Recordset
    
    strSQL = "EXEC spCommunicatorSearch 'Appointed Rep', 'AR'"

    Set db = CurrentDb
    Set qd = db.QueryDefs("spCommunicatorSearch")
    qd.SQL = strSQL
    
    Set rs = qd.OpenRecordset()
    Set Me.search_results.Form.Recordset = rs
    
    MsgBox "pause 1"
    
    strSQL = "EXEC spCommunicatorSearch 'Introducer', 'Int'"

    Set db = CurrentDb
    Set qd = db.QueryDefs("spCommunicatorSearch")
    qd.SQL = strSQL
    
    Set rs = qd.OpenRecordset()
    Set Me.search_results.Form.Recordset = rs
    
    MsgBox "pause 2"
    
    Dim rs2 As DAO.Recordset
    
    Set rs = db.OpenRecordset("spCommunicatorSearch2")
    Set rs2 = rs.Clone
    rs2.Filter = "(MembershipLevel = 'Appointed Rep') OR (ContactTypeID = 'Prospect - AR')"
    Set rs2 = rs2.OpenRecordset
    Set Me.search_results.Form.Recordset = rs2
    MsgBox "pause 3"
    Set rs2 = rs.Clone
    rs2.Filter = "(MembershipLevel = 'Adviser')"
    Set rs2 = rs2.OpenRecordset
    Set Me.search_results.Form.Recordset = rs2
    MsgBox "pause 4"
    
End Sub

All i can say is WOW , the filtering method with the cloned recordset is unbeleivably fast.

As I suspected keep calling the SP with a new @param is too slow as it keeps having to query the SQL Server and being a remote user with a crappy VPN connection, it's resource/network intensive.

However, it would seem the clone/filter method is done client side, so I guess is memory resident, exactly what I was looking for.

The second part of the above test, when applying the filter was basically instantanious, I'm well impressed.

Now it means a complete rewrite of the mechanism for searching (filtering) using the new DAO.Recordset.

I think my remote users are going to be well happy when I release this new version.

Many thanks for all your help MajP, it's much appreciated.

Regards,
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

 

[MajP]

You will have to read up on the differences between DAO and ADO. But in short DAO is the older object model designed specifically for JET. Although older, simpler, and far less robust, it is optimized for JET. So frequently the performance is still better when working in Access. ADO was meant to work with almost every data source. real flexible and portable. When Access 2000 came out everyone was saying that DAO was on the out. But DAOs optimized benefits have kept it around.

In this case, I think if you bind the form to an ADO RS you will achieve the same results with a little less code, and easier to modify.


One trick I always do when working with building a sql where clause it to put that in a seperate function. That way you can reuse in lots of different places form filter, rs filter, qry, code, or part of a Sql string.

something more like

Public Function getSearch(var as String)as string
  
'Company Name
  'your code here
   srch = .....
  getSearch = srch    
End function

I cannot imagine late binding would favor either object model. I cannot understand that statement.

If you can try this in ADO, I would be curious of the results.

You have also raised a question that I thought I new the answer.
When you apply a filter to a form, I assumed it filters the underlying recordset. Basically doing what you are doing. In that way the performance difference should be equivalent. But maybe a form filter creates a new recordset from a new query. Maybe this is the cause of error. Maybe it is not possible to filter a form without a recordsource, but bound to a RS. I need to try.

Sorry I missed the orginal part that this was an external server through VPN and a bad connection. That does kind of change the design solutions.

 

[MajP]

FYI. If working with ADO it uses different wildcards not *.

 

[1DMF]

Hi MajP,

I've got it running real sweet using DAO and my own little helper class....

Class : clsRS

Option Compare Database

Private mainRS As DAO.Recordset
Private cloneRS As DAO.Recordset
Private sQuery As String
Private db As DAO.Database
Private qd As DAO.QueryDef

Private Sub Class_Initialize()
    
    Set db = CurrentDb
    Set qd = db.QueryDefs("spPassThrough")
    
End Sub

Public Property Get Recs() As DAO.Recordset

    If cloneRS Is Nothing Then
        MsgBox "Recordset is null"
    End If

    Set Recs = cloneRS
    
End Property

Public Sub setQuery(spQuery As String)
    
    If Nz(spQuery, "") = "" Then
        MsgBox "Invalid query string! format : ""spName 'param1', 'param2'..."""
    Else
        sQuery = "EXEC " & spQuery
    End If
    
End Sub

Public Sub Run()

    If Nz(sQuery, "") = "" Then
        MsgBox "Invalid query string, process aborted!"
    Else
        qd.SQL = sQuery
        Set mainRS = qd.OpenRecordset
        Set cloneRS = mainRS.Clone
    End If
        
End Sub

Public Sub Filter(sFil)

    Set cloneRS = mainRS.Clone
    
    If Nz(sFil, "") <> "" Then
        cloneRS.Filter = sFil
        Set cloneRS = cloneRS.OpenRecordset
    End If
    
End Sub

so then on any form I can simply use.

Dim rs As clsRS
Set rs = new clsRS

rs.setQuery "mySP 'param1', 'param2'"
rs.Run

Set Me.Recordset = rs.Recs

To filter is then easy.

rs.Filter "CompanyName LIKE '*" & Me.Company & "*'"
Set Me.Recordset = rs.Recs

To clear the filter I simply call rs.Filter with no query string.

I know it's basic, but it's made a world of difference to my app and the way I think in general, so your help has been much appreciated.

1 Day My Friend ;-) 1 Day!

"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