Results 1 to 2 of 2
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    speed up searching value in MDB (2000 sr 1)

    Hi have this piece of code to serach value in a field,
    But in mdb filed have similar 800.000 record...
    is possible to speed up the searching?
    in effect the macro use 30/40 seconds to scroll all records!!!!

    You must know:
    the records in filed init only for the first character with 45 or 65
    cope to found init for the first 2 character for 45 or 65
    my idea... when select with the query... instead to select all record is possible to set this condition:
    if the first 2 cahrater is 45 select with quesry only records init for 45 else select query for the first 2 caharcetr 65...
    in this case i hopr the time of searching is half, or not
    naturally ither way are welcome.

    .......
    Dim CONNESSIONE As ADODB.Connection
    Dim record As ADODB.Recordset
    Set CONNESSIONE = New ADODB.Connection
    CONNESSIONE.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Sheets("CARTELLE DI LAVORO").Range("A1") & "ANAGRAFICA.mdb;'"
    Set record = New ADODB.Recordset
    ' leggi record
    record.Open "SELECT * FROM [ANAGRAFICA1] where COPE = '" & cope & "'", CONNESSIONE, adOpenStatic, adLockOptimistic
    If record.Fields("COPE").Value = cope Then affidato = record.Fields("NOMINATIVO").Value & " - " & record.Fields("LUOGO RESIDENZA").Value
    record.Close
    CONNESSIONE.Close
    Set CONNESSIONE = New ADODB.Connection
    CONNESSIONE.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Sheets("CARTELLE DI LAVORO").Range("A1") & "ANAGRAFICA.mdb;'"
    Set record = New ADODB.Recordset
    'ricerca record
    record.Open "SELECT * FROM [PROPOSTE] WHERE COPE ='" & cope & "' AND TIPOMODULO = 6 order by DATAINS", CONNESSIONE, adOpenStatic, adLockOptimistic
    '
    If record.Fields("COPE").Value = cope Then
    RIGA = 0
    Do While Not record.EOF
    elencoproposte.AddItem
    elencoproposte.List(RIGA, 0) = (record("ID"))
    elencoproposte.List(RIGA, 1) = (record("DATAINS"))
    elencoproposte.List(RIGA, 2) = (record("AFFIDATO"))
    elencoproposte.List(RIGA, 3) = (record("COPE"))
    record.MoveNext
    RIGA = RIGA + 1
    Loop
    End If
    End If
    Else
    cancellaproposta.Enabled = False
    copiaidatiproposta.Enabled = False
    End If
    If Not cope = "" And Not affidato = "" Then
    If Not settore = "" Then inserisciproposta.Enabled = True
    stampaproposta.Enabled = True
    Else
    inserisciproposta.Enabled = False
    stampaproposta.Enabled = False
    End If
    Exit Sub
    ......

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: speed up searching value in MDB (2000 sr 1)

    I don't understand what you're trying to do, but there is no need to close and reopen the CONNESSIONE object, since you're connecting to the same database both times.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •