I eventually had to abandon the dynamic sql because we are using ADO recordsets and they don't like it when dynamic SQL or temp tables are used.

Instead a create...

I''m sad to say i've given up using a normal query and have had to resort to dynamically created the sql statment. This returns the resultset in an instant. 

Thats what i tried original and the reason im was using a subquery is because i want SQL to execute the subquery first so that it doesn't scan the whole...

Thanks, but still no joy.

I could dynamically create the SQL statement by creating a cursor from,  Select *

  from tblSite where cust_fk = 104536 and then created the IN clause.

This seems...

I tried it that way to begin with but it gave me the same problem. I have tired the traditional join method where the join is specifed in the where...

I run the script below in query analyser and then copy and paste the results into the vb project. 

set concat_null_yields_null off

select

 'Parm String' ='Set adParm = .CreateParameter("@' + RIGHT(name,LEN(name)) +...

When naming index include the field names and the order.

e.g. IX_tblName_Field1_Field2

This helps when writing the where clause because you can tell by the name what order to write the criteria

E.g....

It makes no difference which method you use.  A value if true is returned as soon as a row matching the criteria has been found.

If you still not convinced show...

My concern isn't with locking its with inefficient use of resources.

Have i misunderstood what is meant by locking. My understanding is that you only get locking during the execution of...

I'm still not convinced.

With the second method you may still suffer locks because one or more of the client applications could log on at the same time and request data...

What do you mean by proper permissions? I read somewhere that SQL server would set up all the permissions automatically or was i just misdunderstood.

Have used profiler and suspect that access projects on certain occasions runs the reconfigure command. There is nothing in the code that we have written that even touches upon any...

How is then that I create a table and try to insert in a separate process does it timeout?

WE use access projects to access the databases. Could this be executing...

It is running under the system account. What is the minimum level of privileges that the domain account can be run under? Does the server have to be restarted? Will...