speed up query execution

for you wath code speed up the query execution:

Code:

---

....
SQL = "SELECT SP from tab1 WHERE DT='" & Me.DT3.Text & "'GROUP BY SP"
....
Set RST0 = New ADODB.Recordset
    With RST0
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockReadOnly
    End With

    Set CMD = New ADODB.Command
    With CMD
        .CommandTimeout = 0
        .CommandText = SQL
        .ActiveConnection = CONN
        .CommandType = adCmdText
        '.Prepared = True
        DoEvents
        Set RST0 = .Execute
        Set CMD = Nothing
    End With
....

---

or directlly:

Code:

---

  With RST0
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockReadOnly
    End With
Set RST0= New ADODB.Recordset
    RST0.Open sql, CONN, adOpenKeyset, adLockOptimistic, adCmdtext

---

note:

are approx 75.xxx records in access table:-(

Your query looks pretty simple (although it's not recommended to just concatenate strings to your WHERE clause, as it opens your application for SQL Injections, use parametrized query instead ). But in any case, make sure your table has indexes on fields you used in WHERE clause and ORDER BY. In your case, check the table and make sure it has indexes on DT and SP fields.

I'm surprised it runs at all... it's an incomplete SQL Statement...

-tg

Quote:

---

Originally Posted by techgnome

I'm surprised it runs at all... it's an incomplete SQL Statement...

-tg

---

Is new for me... my queru run!
Suggest me where is incomplete.
Tks

Code:

---

SQL = "SELECT SP tab1 WHERE DT='" & Me.DT3.Text & "'GROUP BY SP"

---

I think there should be a "From tab1" and if you are using a "GROUP BY" there should be some aggegate function like a sum, count, average, etc.

Quote:

---

Originally Posted by techgnome

I'm surprised it runs at all... it's an incomplete SQL Statement...

-tg

---

corret with suggestion rasinc...
sorry... but copy and paste with a pice of code i have the from statement:p:D;)