limit records seached in a DB

[Tailgun]

I have a form that the user creates a new record in the DB
He types a Claimnumber in a box then retypes in a second box and I check that they match so I know he hasn't made a typo. However I also must check that the Claimnumber isn't already in the db. Here is the partial code when he clicks a cmdbutton.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.ConnectionString = "DSN=eDoan;UID=sa"
cn.Open
 rs.Open "Select ClaimNo from tblClaim Where ClaimNo = '" & txtRetypeClaimNo.Text & "'", cn, adOpenDynamic, adLockPessimistic
   If rs.EOF And rs.BOF And txtFields(1) = txtRetypeClaimNo Then
 
go on with the process
 
Else
 
MsgBox etc

It works just fine with no problems. However as the DB grows it will take longer and longer to check for no duplicate Claimnumber. My question is can I somehow code this to only check the last 4000 records in the DB ?

Thanks for any help

[goatsucker]

I'm not usre about limiting the number of records searched, but I may have an alternative solution:

Presumably sooner or later the claim number entered is entered into the tblClaim table? If ClaimNo has a unique index on it (And if it is the primary key of that table, then it should have), this INSERT will fail. Yopu can trap the error message returned by the INSERT and tell the user that this claim has already been entered. You do not need to check explicitly that it has been already entered, if you wait unitl the insert fails.

[Tailgun]

Thanks for the help but I can't do it that way for several reasons but your right the Claim number gets put in tblClaim right after

THEN

here is part of what I do after the THEN

Dim cn1 As ADODB.Connection
Dim rsMetrix As ADODB.Recordset

Set cn1 = New ADODB.Connection
cn1.ConnectionString = "DSN=eDoan;UID=sa"
cn1.Open

Set rsMetrix = New ADODB.Recordset
With rsMetrix
.Open "tblClaim", cn1, adOpenForwardOnly, adLockPessimistic
.AddNew

!CompanyName = DataCombo1.Text
!ClaimNo = txtFields(1).Text
!OwnerFirst = txtFields(2).Text
!OwnerLast = txtFields(3).Text
!DateRecd = txtDateRecd.Text
!DispatcherName = DataCombo2.Text
!CompanyID = txtCoID.Text

.Update
.Close


Wish I could somehow use your suggestion but I have a Primary Key set on another column.

But thanks alot for your help.

[techgnome]

Actually, the best thing to do is put an Index on the field being search and leave it alone. That will ensure that it's at it's optimum. If you start limiting it, you end up having to run two searches, which will cause more degradation. -- The first being to limit the last X # of rows, and the second to see if that # is in that returned list. It's better if you just put an Index (a clustered one would be best) and leave the query alone.

Another tip: When you open the recordset to add the record, do a "SELECT TOP 0 * FROM tblClaim" instead of the table itself. This will return an empty recordset with the field structure in it. That way you aren't returning the entire table (which will get larger as you go.)

[Tailgun]

Thanks techgnome

I really appreciate the help.