searching access database

Hello,

I have an access database with 100,000+ records that I need to search through. I only need to work with the records that have a value in the account number field. Right now I have it going down the table and whenever it finds a row with an account number in it, it does the computations. This process only allows a few hundred records to be done every minute. I need to speed this up, but I have no clue how. Any help would really help me out :)

Code:

---

For i = 1000 To x 'x is number of rows in table

   
   
    Adodc1.RecordSource = "SELECT * FROM tSaleTransactions Where cdeTrans='A" & i & "'"
 
    Adodc1.Refresh
 
    If Adodc1.Recordset.Fields("cdeCustCode") <> Empty Then
'does computations

---

Each SQL call is going to take a long time. Faster would be to ditch the databound controls and go with direct access using ADO. And when I say faster, I mean the entire loop would probably take a few seconds from start to finish.

I will admit I know almost nothing about coding, but isn't ado what I am using in my code example? If you could point me in the direction I would really appreciate it.

Thanks! :)

Well I probably wouldn't use that ADODC because it carries a lot of baggage with it. I'm not saying to avoid it, just avoid it for this operation. For example:

Code:

---

Dim rsCompute As ADODB.Recordset

Set rsCompute = New ADODB.Recordset
With rsCompute
    .Open "SELECT only the fields you need FROM tSaleTransactions", _
          Adodc1.Recordset.ActiveConnection, _
          adOpenForwardOnly, adLockReadOnly, adCmdText
    .MoveFirst
    Do Until .EOF
        If Not IsNull(!cdeCustCode.Value) Then
            'Do computations.
        End If
        .MoveNext
    Loop
    .Close
End With
Set rsCompute = Nothing

---

Notes:

• You might want a WHERE clause in the SQL statement if there are values below A1000 that you want to exclude.
• You might want another LockType if you are updating values as part of your "computations."
• You might need to add .Update before .MoveNext if you change field values as part of your "computations."
• You might not want to use Not IsNull() depending on how your "no value" values are stored. For empty String values you should probably use Len() > 0 as a test instead if you didn't store NULLs.

This might be off a little from what you want, but it should be close.

Quote:

---

Originally Posted by mooe

isn't ado what I am using in my code example?

---

Well, you aren't using ADO; the databound control is. If you use ADO directly you'll greatly improve your control over what's going on, which will allow you to greatly speed up execution.

ETA: dillettante showed you the basic idea. You can also edit data inside the loop. Post any specific questions you have.

Edit: Realized my mistake. I'm an idiot.

No, you're just not used to doing this stuff all the time.

Thread moved to Database Development forum (the "VB6" forum is meant for questions which don't fit in more specific forums)