A Faster Way To Search Through A Database 1

[sonper]

Hi guys!

Does anybody have an idea on a faster way to search a particular record on a database. I tried using recordset.find method and it's really slow. I'm trying to find a string on an MS Access DB of 100,000+ records. Please help.

 

[CCLINT]

To find if record exists, ONLY:

Set rs = conn.Execute("SELECT Count(*) FROM TheTable WHERE SomeField = SomeCriteria",, adCmdText

Or, try a stored proceedure, using Command and Parameter Objects

 

[sonper]

CCLINT,

Thanks again. It surely is faster than the code a previously used. One more thing sir, how will I use this method to edit all the fields or a particular field in the recordset. And could you please give me tips on how I could optimize the performance of my application. There are times that my application runs slow. I have at least 10 users on a network simultaneously opening and updating records on the database. Can MS Access handle all these jobs?

Thanks mucho!

Percy

 

[CCLINT]

rs.Open "SELECT * FROM TheTable WHERE SomeKeyField = SomeCriteria", conn, adOpenStatic, adLockOptimistic, adCmdText

If Not rs.EOF then
rs.Fields("Field1&quot.Value = "ABC"
rs.Update
End If

>There are times that my application runs slow

This subject is far too large to discuss in a few lines here. There are thousands of factors.

You need to identify the bottle neck areas individually and then handle them individually.
If you use Client Side - LockBatchOptimistic cursors for updating individual records, or small groups of records, then disconnecting the recordset from the datasource immediately, and then reconnecting and using UpdateBatch to quicky run the db updating, and again disconnecting afterwards, will solve alot of problems.
And limit the scope of records your recordset pull in locally (don't try always pulling in all records, but just the ones you really need by forcing the user to use a criteria, or only pulling in a few pages at a time).

Start new threads in TT for the different questions, limiting one thread to a single subject, or very closely related subjects, narrowing down as much as possible.