Validation

F

fred

I have a scheduling program that validates for duplicates
records on three fields (EMP_ID, APPT_DATE & APPT_TIME).
This validation is done using a SQL statement below:

sqlSTR = "SELECT * FROM tblEMPLOYEE WHERE EP_DATE = #" _
& Me.txEP_DATE & "# AND EP_TIME = #" _
& Me.txEP_TIME & "# AND EP_ID = '" & Me.txEP_ID & "'"

Set rstEMPLOYEE = db.OpenRecordset(sqlSTR)

If Not (rstEMPLOYEE.EOF) Then
pub_msgres = MsgBox("ERROR...EMP HAS PREVIOUS APPT " _
, vbOK + vbExclamation, "DUP APPOINTMENT")
End If


My problem is this database is on as server and the SQL
statement takes approximately 5 seconds to respond (there
are about 50,000 records). Is there a way to speed up
this type of validation?
 
J

Jeff Boyce

Fred

If tblEmployee contains 200 fields, and you only need to confirm the
existence of a row (does this date/time already exist?), why are you
returning all fields (SELECT *)? A COUNT * might be faster.

If you are providing selection criteria (date/time info), are these fields
indexed in the underlying SQL Server table?

One approach to speeding this up would be to build a view in SQL Server that
only contains the fields you are checking (it appears to be EmployeeID,
EP_Date and EP_Time). Then link to that view as you would a table, and run
your query/SQL (using Count *) against that.
 
Top