need faster data search

I need to know if anyone can supply me with code to replace this code example that would speed up the recordset creation.(SQL Server or ACCESS)

Set recordset = Mydb.OpenRecordset(SQL, dbOpenForwardOnly)
Do Until recordset.EOF
  recordset.MoveFirst
   'set a variable = to record matches'
  recordset.MoveNext
Loop
apwbeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
You need to execute an UPDATE query like this:

Update Table1
Set Field1 = 'NewValue'
Where Field2 = 'Criteria'
0
hesCommented:
Set recordset = Mydb.OpenRecordset(SQL, dbOpenForwardOnly)
recordset.MoveFirst

Do Until recordset.EOF
 
   'set a variable = to record matches'
  recordset.MoveNext
Loop
0
kprestageCommented:
Is SQL in the openrecordset a SELECT statement?  Use a SQL SELECT statement to find the matches for you, and the recordset returned will only have the data you need.  If you need help in doing this, let me know and I will see what I can do, but I need more info first.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

fregCommented:
Dim strSQL As String

strSQL = "SELECT * FROM <tablename> WHERE <criteria>
Set recordset = Mydb.OpenRecordset(strSQL, dbOpenForwardOnly)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
apwbeAuthor Commented:
Currently I use a SQL statement such as

'SQL = SELECT * FROM DATABASE'

my code is:

SQL2 = "SELECT * FROM MUNIVIEW_PARCEL"
Set Mydb2 = OpenDatabase(App.Path & "\..\..\isa\data\anchorage\db2cama.mdb")
Set rs2 =Mydb2.OpenRecordset(SQL2,dbOpenForwardOnly)
           
Do Until rs2.EOF
   If rs2.Fields("APN").value = g_tin Then
       'BLA BLA BLA'
End If
rs2.MoveNext
Loop

A question might be 'Would putting the WHERE statement in the SQL string speed this process since the recordset should only contain the one matching record (given that all the records in the file are unique). Currently it builds a recordset of all the records then loops through and selects a match from the recordset. Am I thinking along the correct path or am I just talking turkey?

I read somewhere that by using a "record count" at the begining of the loop that somehow it can be set up so that the search does not have to manipulate the pointer as much (something like going through all the records for each loop) I wish I could find that article, but the result I want is a faster query and recordset creation.
0
Éric MoreauSenior .Net ConsultantCommented:
Changing your code this way may help:

SQL2 = "SELECT * FROM MUNIVIEW_PARCEL WHERE APN = " & g_tin
Set Mydb2 = OpenDatabase(App.Path & "\..\..\isa\data\anchorage\db2cama.mdb")
Set rs2 =Mydb2.OpenRecordset(SQL2,dbOpenForwardOnly)
             
Do Until rs2.EOF
       'BLA BLA BLA'
rs2.MoveNext
Loop

This will help because, as you said, you limit the number of records processed.

Regarding the RecordCount, it can't help!

If your «'BLA BLA BLA'» is always the same, nothing will be faster than this syntax:
"Update MUNIVIEW_PARCEL
Set Field1 = 'NewValue'
Where APN = " & g_tin
0
apwbeAuthor Commented:
emoreau

I do not have to update the database tables, just set some variables to select item values. In other words, how would and the update code speed up my query? I am querying 80,000 records. Not really a big database but since this table is queryied from a WEB request, it has to be as fast as possible on the server side.

I am trying the SELECT-WHERE code right now. FREQ was the first expert to suggest using this approach. If it works it would only be fare to give him the points.
0
Éric MoreauSenior .Net ConsultantCommented:
You need to consider 2 things in data retrieval:
-select only records needed (by inserting the appropriate Where clause).
-select only fields needed (instead of saying «SELECT * FROM ...» you can write «SELECT Field1, Field2, Field3 FROM ...» which will only retrieve selected fields)
0
apwbeAuthor Commented:
Thanks to all the experts that replied.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.