Write select query that will search the data from Bottom to top of the table

[aman]

Hi All

Please see below the code . The select statement searches the Printpoolno value from the top to bottom in table tblmaster. As in my table tblmaster there are thousands of records and it takes long to search for that Printpoolno from the table . Is there anyway we can write a query that will search the table from bottom to top as the Printpoolno will always be in the bottom records and not in the top records.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=J:\WilliamsLea-AIMM\Critical Document Handling\ODH System.mdb;"
      Set rs = CreateObject("ADODB.Recordset")
      strsql = "select * from tblmaster where Printpoolno='" & TextBox1.value & "'"
      rs.Open strsql, cn

Thanks

 

[namliam]

is this code being executed from within the ODH System.MDB? or is it another database?
PoolNO sounds like this is a number field, is it?
Do you have an index on your PoolNO?

Thousands is really nothing much and shouldnt be an issue at all

 

[aman]

Hi namiliam

The code is written in Exce/vba and the backend is in Access . The Printpoolno has text data type.

Pls help me in this.

Thanks

 

[namliam]

Does your column have an index on it?

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Debug.print timer, "Start"
    Set cn = New ADODB.Connection
Debug.print timer, "Created CN"
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=J:\WilliamsLea-AIMM\Critical Document Handling\ODH System.mdb;"
Debug.print timer, "Opened DB"
      Set rs = CreateObject("ADODB.Recordset")
Debug.print timer, "Created RS"
      strsql = "select * from tblmaster where Printpoolno='" & TextBox1.value & "'"
      rs.Open strsql, cn
Debug.print timer, "Opened RS"

Execute this code with the timers in so you can identify where the "long time" is....
Run it and post back the output of the immediate window
I am kindoff exepecting it to be in the opening of your db