Access - SQLServer2000

[rann]

HI,
I have a application which has Access as Front end and SQLServer as BackEnd.
I have a table which has student details.

Table Fields are:
Record#,FirstName,LastName,MiddleName,Address,City,State,Pin,.....
The table has about 1200 records.

Every Thing works fine but in the front end when i try to do a search by
FirstName it is VERY SLOW.I need to use ctr-break to stop the process or it keeps going on.But it is okey if i search by the Record#.I need to search by lastname or Firstname and speed up the process.I even tried creating a FullTextIndex but it was of no use.


Any Help will be appreciated,
Thanks in advance,
Rann.

 

[AshleyRickards]

First thing you want to check are the name and address fields indexed? I'm assuming the record number is the PK that's why it's faster. Also .. network bandwidth, user activity .. etc .. Ashley L Rickards
SQL DBA

 

[rann]

Hi,
The number of users using it is just 10.All the 10 users
dont have the Application open at the same time.Probably
about 4 users use at a time.
The Network used is Novell netware and the bandwidth is pretty fast.Yes the Record# is the PK.I will index the Names and try again.

Thanks for you Help,
Rann

 

[moonshadow]

It's likely that MS Access is pulling all your records over the network, then searching them 'manually'. You don't mention how long your records are, but if they're substantial, I bet that's the problem. I've no idea how your interface works, but the trick is to get SQL Server to run the query. Either use a pass through query or (better) a stored procedure. Pass it the name you're looking for and voila - your results almost immediately.