Search and Find - SLOW

[Joshua K Briley]

I just recently migrated from Access 2000 to 2003. It is being used as a
front end to SQL 2000. All of the "code" is in Access, where SQL is being
used as a dumping ground. In A2000, we wrote a routine that helped speed up
our searches (see below). However, this doesn't seem to be working well in
2003. I understand that they use different versions of VB. Any advice on
how to fix this problem? Thanks in advance.


Option Compare Database

'--------------------------------------
' Use DStart()instead of DFirst() to return
' the first sorted record in a domain.
'--------------------------------------
Function DStart(FieldName As String, DomainName As String, Optional _
Criteria As Variant)

Dim MyDB As Database, MySet As Recordset

' Error out if there is no fieldname sent.
If Len(FieldName) = 0 Then
MsgBox "You Must Specify a Field name", , "DStart"
Exit Function
End If

' Error out if there is no domain sent.
If Len(DomainName) = 0 Then
MsgBox "You Must Specify a Domain name", , "DStart"
Exit Function
End If

Set MyDB = CurrentDb()
Set MySet = MyDB.OpenRecordset(DomainName, dbOpenDynaset)

' Apply a filter to the recordset if a criteria is sent.
If Not IsMissing(Criteria) Then
MySet.Filter = Criteria
Set MySet = MySet.OpenRecordset()
End If

' If there are no records, return the null, else return the value
' of the first record.
If MySet.EOF Then
DStart = Null
Else
MySet.MoveFirst
DStart = MySet(FieldName)
End If

MySet.Close
MyDB.Close
End Function

'-------------------------------------------
'Use DEnd()instead of DLast() to return
' the last sorted record in a domain.
'-------------------------------------------

Function DEnd(FieldName As String, DomainName As String, Optional _
Criteria As Variant)

Dim MyDB As Database, MySet As Recordset

' Error out if there is no fieldname sent.
If Len(FieldName) = 0 Then
MsgBox "You Must Specify a Field name", , "DEnd"
Exit Function
End If

' Error out if there is no domainname sent.
If Len(DomainName) = 0 Then
MsgBox "You Must Specify a Domain name", , "DEnd"
Exit Function
End If

Set MyDB = CurrentDb()
Set MySet = MyDB.OpenRecordset(DomainName, dbOpenDynaset)

' Apply a filter to the recordset if a criteria is sent.
If Not IsMissing(Criteria) Then
MySet.Filter = Criteria
Set MySet = MySet.OpenRecordset()
End If

' If there are no records, return the null, else return the value
' of the last record.
If MySet.EOF Then
DEnd = Null
Else
MySet.MoveLast
DEnd = MySet(FieldName)
End If
MySet.Close
MyDB.Close
End Function

 

[Joshua K Briley]

In debugging the script it states that I should use dbSeeChanges with Open
Recordset.... What about with query statements such as:

Set rs = db.OpenRecordset("Select * From tblSecFunctions")

rs was declared
Dim rs as recordset....

I'm stumped and could seriously use anyone's help.

 

[Tim Ferguson]

Set MySet = MyDB.OpenRecordset(DomainName, dbOpenDynaset)

This seems just a tad 60's.

What about changing it to

set myset = mydb.Openrecordset( _
"select onefield from onetable where criterion=True", _
dbopensnapshot, dbforwardonly )


as that seems to be faster, kinder and moves the work to the server where
it belongs. Don't see the point of buying a dog (SQL server) and barking
yourself (doing sequential searches in the client).

Just a thought


Tim F

 

[Joshua K Briley]

Thank you Tim,

I found another little issue on the search and find. That miserable "search
fields as formatted" check box is defaulted to be selected. Unchecking that
box also made a big difference in search performance. I've done searches on
changing that default to be unselected. I notice that the same question has
gone unanswered in google groups for 2 years. Any ideas?

I agree wholeheartedly with your statement about letting SQL do the barking
for me. We have TONS of data, and that's the reason for SQL - a mere dumping
ground, if you will.

Thanks,
Josh

 

[Tim Ferguson]

=?Utf-8?B?Sm9zaHVhIEsgQnJpbGV5?=

I agree wholeheartedly with your statement about letting SQL do the
barking for me. We have TONS of data, and that's the reason for SQL -
a mere dumping ground, if you will.

Well, since you asked, no I won't. SQL Server -- or any decent datbase
server, for that matter -- is a highly engineered, massively intelligent
software engine for controlling, finding, organising and retrieving
information. Calling it a dumping ground is a bit like saying that the M25
is a place for storing old tyres. Silly attitude.

All the best

Tim F

 

[Joshua K Briley]

Tim,

Are you suggesting that the decision to use SQL in this manner was mine?
The attitude, however silly it may be, is simply a reaction to a decision
that was made YEARS before my involvement.

 

[Tim Ferguson]

=?Utf-8?B?Sm9zaHVhIEsgQnJpbGV5?=

Are you suggesting that the decision to use SQL in this manner was
mine? The attitude, however silly it may be, is simply a reaction to
a decision that was made YEARS before my involvement.

Oh dear, this is getting personal and I didn't mean to sound like that at
all. All that I was complaining about is the "SQL Server is a dumping
ground" You don't have to agree.

B Wishes


Tim F