Why is DCount super slow

[rosenn]

Short version - DCount super slow. Why?

Detailed version -

I have a DBase with 1200 hospital patient records and growing. I use DCount to see if a given person's medical record number is already in the dbase to avoid duplicate entries. I think I should do it this way rather than not allowing duplicates in the field's table definition because (1)Duplicates just screw things up, (2) by not permitting duplicates for the field users get an ugly error if they enter one and I am not good at errorhandling and (3)with my little code once we identify the patient is already in the dbase I open a query directly to their record so their status can be changed from discharged to active - a convenient feature. My problem is while the DCount works it is extraordinarily slow. I have it count the number of records in the main data table that have an identical Medical Record Number to the one that was just typed in (it does the event when you leave the Medical Record Number field).

Why is this so slow. Does DCount use a linear search algorithm or a type of logarithmic quick sort. Does it search every field in the table or only the one specified in the criteria of the DCount call. Should I make a query of only Medical Record Numbers and then use that for my DCount? Is it just because our little office PC that we use for the DBase is slow?

All info or ideas appreciated sincerely,

Nelson

 

[Jack Cowley]

DCount is an 'domain aggregate' function and is slow. I suggest that you use code (ADO or DAO depending on your version of Access) to see if the particular record the user is entering already exists in the table.

hth,
Jack

 

[bjackson]

why not use a query to search the database. as well.i
think it would be almost instantanious on such a small number
of records

 

[rosenn]

What do you mean by query

Medical Record Number is an index field, but I have allowed doubles because if I don't and a user enters one, they get a most user-unfriendly error message that I want to avoid.

As to using a query, do you mean I should stick some SQL into my little code that is executed once someone tries to leave the Medical Record Number field? I have no experience writing SQL into a VBA routine. How would such a query go. My VBA now looks like this:

Numofthem = DCount([MR#], "Patient Info", "[MR#] = " & Me![MR#])
If Numofthem < 2 Then
Exit Sub
Else
MsgBox "This patient is already in the database."
End If

Also in the Else part of the If then I open the table with the patients directly to that patients record so the user can re-activate the record.

 

[WayneRyan]

rosenn,

Dim db As Database
Dim sql As String
Dim rst As Recordset

Set db = Currentdb
sql = "Select * from [Patient Info] where [MR#] = " & _
Me.[MR#] & ";"
Set rst = db.OpenRecordset(sql)
If rst.EOF and rst.BOF Then
Exit Sub ' No duplicate
Else
MsgBox "This patient is already in the database."
End If

By the way, those spaces and special symbols make things
much more difficult to deal with.

hth,
Wayne

 

[rosenn]

So far it works

Thanks for the help - so far it works - I haven't tried it on my regular version of the list but on my smaller tester version it works fine.

Oh, and yes, I have been meaning to fix all the names (i.e. spaces and special characters).

-Nelson

 

[bjackson]

is this method a lot faster.i will be interested to know

 

[rosenn]

Needs tweaking

This method is indeed very fast. However, it only really is valid, algorithmically speaking, if you are inserting a new record. When tabing through fields in records that are already there, when you leave the [MR#] field, you set off the duplicate alarm, because it already has one in the table.

Perhaps, I should only activate this duplicate checker subroutine in a form designed and used for adding new patients only. Then prevent users from adding patients through any other means. This would work in general but would fail if someone unkowingly added a duplicate patient but at the time they did the data entry they didn't have the accurate MR# - so you have to put something in there, its a required field. So they type "1234567" or something like that, just to have something in the field to allow them to input the patient, which would clear the duplicate checker of the input form. Then, they return later into the editing form, which would not have the duplicate checker, and change it to the duplicate, and I have 2 identical patients in the dbase.

Any ideas? I think we are getting close to solving this.

Thanks,

Nelson Rosen

 

[rosenn]

Best advice I've had

You are absolutely right. I am in the process of changing the dbase, doing the error trapping the right way and not allowing duplicates in the MRnum field.

Thanks for pointing out what should have been my approach all along.

-Nelson

 

[rosenn]

Finally

All I needed was:

If Err.Number = 3022 Then ....

It works great.

Thanks for all your help.