Program not responding while querying MS Access database of 1000000 records

I am trying to search for the occurence of a string within MS Access database with 1000000 records using VB.Net. The program does not respond once it is run or it is taking a very long time. However, if I reduce the size of the database the program runs. Below is the code Could anyone let me know how do I go about it.. Thanks in advance.

Imports System.Data.SqlClient

Public Class Form1

    Dim rcount As Integer
    Dim conn As New ADODB.Connection
    Dim rcset As New ADODB.Recordset
    Dim sqlStr As String

    Public Sub openConn()
        Dim strConnect As String
        strConnect = "DRIVER={Microsoft Access Driver (*.mdb)}; DefaultDir=" & Application.StartupPath & ";DBQ=atg;UID=;PWD=;"
        If conn.State = 0 Then
            conn.ConnectionString = strConnect
            conn.Open()
        End If
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.Even开发者_开发知识库tArgs) Handles MyBase.Load
    End Sub

    Public Sub rcdSet(ByVal sqlStr As String)
        If conn.State = 0 Then
            Call openConn()
        End If
        If rcset.State = 1 Then
            rcset.Close()
            rcset = Nothing
            rcset = New ADODB.Recordset
            rcset.CursorLocation = ADODB.CursorLocationEnum.adUseServer
            rcset.CursorType = ADODB.CursorTypeEnum.adOpenDynamic
            rcset.LockType = ADODB.LockTypeEnum.adLockOptimistic
        End If
        rcset.Open(sqlStr, conn)
    End Sub

    Private Sub btn_search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_search.Click
        Dim sql As String
        Dim rs As New ADODB.Recordset
        Dim rs2 As New ADODB.Recordset
        Dim res As String
        res = ""
        Call openConn()
        Dim SearchString_exist As New List(Of String)(txt_exist.Text.Split(Environment.NewLine))
        '  Dim SearchString_not_exist As New List(Of String)(txt_not_exist.Text.Split(Environment.NewLine))
        '  Do Until SearchString_exist.Count = 0
        sql = "SELECT * FROM ATG WHERE Term like '%cam%'"
        rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
        While (rs.EOF <> True)
            res += rs.Fields("Term").Value + Environment.NewLine
            rs.MoveNext()
        End While
        rs.Close()
        SearchString_exist.RemoveAt(0)
        txt_exist.Lines = SearchString_exist.ToArray
        'Loop
        txt_result.Text = res
    End Sub
End Class

---

If you're saying that the application is unresponsive once you click the button, that's because you're searching your Access database on the same thread that updates you application - that means that the application will appear busy until your database search is done (which could take a while). If you want to do this in a background thread and leave your application responsive during the search, you can do that using the threadpool:

http://www.dotnetperls.com/threadpool-vbnet

Also, the query runs better with fewer records because it has less data to search through, suggesting that it may not be indexed well. You could add an index on the "Term" field, which will help speed things up - you'd have to see if it helped. Access will have to do an index scan, not a faster index seek, so the response won't be immediate, but it will may help cut down your wait time significantly. Here's a Microsoft article that shows how to do this:

http://office.microsoft.com/en-us/access-help/create-and-use-an-index-to-improve-performance-HA010210347.aspx

---

If you have 1,000,000 records, why aren't you using SQL Server? How much traffic is this application going to get? I wouldn't expect any site to scale with MS-Access as its data store.