How To Make Search on MSAccess Database using ASP

Results 1 to 2 of 2

Thread: How To Make Search on MSAccess Database using ASP

  1. #1
    Join Date
    Dec 1969

    Default How To Make Search on MSAccess Database using ASP

    I have a table in Access which has more than 13000 records.<BR>When i fire a query which retrieve me almost all the records it dosen&#039;t work. Its taking a lot of time. Can you suggest a method to make my search faster.<BR>How does google search work. It retrieves thousands of records.<BR>i&#039;m also showing my ASP page Page wise.<BR>

  2. #2
    Join Date
    Dec 1969

    Default Index your tables

    I don&#039;t think Google is using MS Access ;-)<BR><BR>Powerhouse search engines have powerhouse database engines like Oracle and SQL Server (well I&#039;d like to think that SQL Server could handle it!). I also think they hold several MILLION rows worth of data.<BR><BR>The first thing I would suggest is to move to SQL Server (if you can afford it). Access is really supposed to hold small databases. If you are performing text searches with LIKE statements you will be in a world of hurt if you use access.<BR><BR>If you are stuck with Access, you should make sure that your tables are indexed on the fields that you are doing searches and joins on. If you are using LIKE statements, keep them down to a minimum. Build your SQL query by hand and make sure you don&#039;t add worthless conditions to your SQL statement. For example, if you have a text box on your ASP page for "Last Name" and the user leaves it blank, make sure you are not doing performing a SQL Query that looks like<BR><BR>WHERE <BR> First_NAME LIKE &#039;%BOB%&#039;<BR> AND Last_Name LIKE &#039;%%&#039; <BR><BR>Just leave the Last_Name LIKE statement out.<BR><BR>Personally I have worked with Access tables with over 25000 records with no problems. Of course, we were doing lookups on the primary key, which are very quick.<BR><BR>Question, are you trying to DISPLAY all 13000 rows on a single page? That would be pretty nuts. If so, you need to consider paging the recordset and/or increasing the Script.Timeout parameter.

Posting Permissions

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