ADP searches vs. MDB

I'm a newbie to ADP using Access 2003, WinXP sp2, SQL Server 2000 sp3a. I thought about which group to post this message in -- and chose ADP since I need people who understand both MDB and ADP to consider my question.

I'm an Access user since 1.0 and love the flexibility. But I want to put my database on the Internet and so have upsized my MDB to ADP with SQL Server back end. I am concurrently using (1) the MDB with ODBC connection to the SQLDB for all those neat things that can be done fast and easy; (2) an ADP to manage the SQLDB and learn how to adapt my MDB tricks to the ADP world; (3) SEM, QA and other SQL Server tools when needed; and (4) plan to use Visual Studio 2003 to create the ASP.NET web browser interface.

My first question is about searches (and I know next to nothing about SQL Server). I have an identical Access form in both the ADP and MDB front ends based on a full table dataset of 30,000 records. In ADP, a Ctrl-F search on a non-PK indexed nvarchar (255) text field is lightening-fast. The same search in the ODBC linked MDB form takes 26 seconds (or 35 seconds if the entry is not found). Can someone help me understand why the dramatic speed difference? The search dialog options are Match = Any Part Of Field, Search = All, Match Case not checked, Search Fields As Formatted not checked. The underlying SQL Server table column allows nulls but has a restraint to not allow zero-length entries.

Chris Hopkins 

.

Relevant Pages

  • Re: Upsizing design considerations
    ... reports, with a ADP, or mdb (actually, it should be mde, or a ADE when you ... you would LINK the tables to sql server, and NOT USE a adp (the reasons is ... the first two remote users ...
    (microsoft.public.access.gettingstarted)
  • Re: Official Status of SQLServer 2005 ADP
    ... solution might be to use ADP. ... With MDB and Linked tables, the only ways of accelerating things are the use ... of Views and the cumbersome use of SQL passthrough queries. ... > SQL Server, and carry on using Access like I aways had. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: From .mdb to .adp database
    ... I decided to go on with adp project, and i have created a store ... But queries are different stories. ... In SQL Server, there are Views, Stored Procedures, UDFs. ... Also, when you decide to use MDB front-end, you can choose use MDB ...
    (microsoft.public.access.adp.sqlserver)
  • Re: From .mdb to .adp database
    ... there is no exact equivalent query object in SQL Server to MDB's query. ... Most likely, the wizard converts MDB queries to Viwes or SP, if the queries are convertiable. ... Also, when you decide to use MDB front-end, you can choose use MDB queries in the front end or use SQL Server side query objects. ... SQL Server is very powerful server software, whether you use MDB, ADP or anything else to access data from it, you MUST learn how to use it and almost for sure you need to learn another programming environment. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: From .mdb to .adp database
    ... there is no exact equivalent query object in SQL Server to MDB's query. ... Most likely, the wizard converts MDB queries to Viwes or SP, if the queries are convertiable. ... Also, when you decide to use MDB front-end, you can choose use MDB queries in the front end or use SQL Server side query objects. ... SQL Server is very powerful server software, whether you use MDB, ADP or anything else to access data from it, you MUST learn how to use it and almost for sure you need to learn another programming environment. ...
    (microsoft.public.access.adp.sqlserver)