searching access database

[mooe]

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

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

[Ellis Dee]

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.

[mooe]

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!

[dilettante]

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:

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.

[Ellis Dee]

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.

[mooe]

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

[dilettante]

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

[si_the_geek]

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