SQL Find or Filter slow. Use a pass through query?

Yes.  I have indexes on all the columns I''l be searching on.

It's weird: When I use MS Access to on these tables it is much faster.

I am writeing a DLL so I can do some real spacific things with the tables in Excel.

I'm staring to think that I need to create a few stored procedures on the SQL server.  Do you have any expereinece with that?

Of course.. I can do anything in DB side other than Front end applications..
And that's why not able to check for something strange in your code above(if any)

>> When I use MS Access to on these tables it is much faster.

Then there's something with your connection string or datareader in your application.
Either it uses cursor or looping around for each records instead of result set.

Here is the conncection string for the ADODB connection object:
Provider=MSDASQL.1;Extended Properties="DRIVER=SQL Server;SERVER=DaServer\test1;UID=usr;PWD=DaPass;APP=2007 Microsoft Office system;WSID=DaWSID;DATABASE=DaDB;Network=DaNetwork"

conn is the ADODB connection object

Here is the open command I use for the ADODB recordset object:
rst.Open(strQuery, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockPessimistic)

Is there something here that could be causeing my issue?

The more I read it looks like a stored procedure is the way to go.

I am trying to write a proc that will execute a simple SELECT query on any table in my DB.

I get these errors:
Msg 1087, Level 15, State 2, Procedure fxn_RetTable, Line 9
Must declare the table variable "@sstrTableToRet".
Msg 102, Level 15, State 31, Procedure fxn_RetTable, Line 14
Incorrect syntax near 'BEGIN'.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fxn_RetTable]
     (@sstrTableToRet VARCHAR(50), 
      @sstrFieldToRet VARCHAR(50), 
      @sstrFieldToSearch VARCHAR(50), 
      @sstrValToSearch VARCHAR(50)
     )
RETURNS TABLE  
BEGIN 
     SELECT @sstrFieldToRet FROM @sstrTableToRet WHERE @sstrFieldToSearch = @sstrValToSearch
END

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Thought I replied at that time itself..

In your scenario,

SELECT @sstrFieldToRet FROM @sstrTableToRet WHERE @sstrFieldToSearch = @sstrValToSearch

Column names, Table Name, Where clause and value are passed as parameters.
So you have to use Dynamic SQL which requires you to use sp_executesql stored procedure.
Since Stored procedures can't be called from Functions, you wont be able to achieve the functionality mentioned above using a fucntion.

Kindly tell me whether a Procedure would be ok for you, so that I can give you the modified procedure code..

>> I am trying to write a proc that will execute a simple SELECT query on any table in my DB.

The one you gave below is the ALTER FUNCTION and kindly confirm whether you try to achieve this in a procedure or not..

Procedure to achieve the functionality provided above

set ANSI_NULLS ON
SET NOCOUNT ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fxn_RetTable]
     (@sstrTableToRet VARCHAR(50), 
      @sstrFieldToRet VARCHAR(50), 
      @sstrFieldToSearch VARCHAR(50), 
      @sstrValToSearch VARCHAR(50)
     )
     AS 
BEGIN 
	DECLARE @sql nvarchar(4000), @sqlt nvarchar(1000);;
	SET @sqlt = 'select * from ' + @sstrTableToRet + ' into tempdb..copy where 1 = 2';
	EXEC sp_executesql @sqlt;
	SET @sql = 'SELECT ' + @sstrFieldToRet + ' FROM ' + @sstrTableToRet + ' WHERE ' + @sstrFieldToSearch + ' = ' + @sstrValToSearch;
	INSERT INTO tempdb..copy
	EXEC sp_executesql @sql;
	SELECT * FROM tempdb..copy;
END

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

Wow!  That looks complex.  I'll give it shot and get back to you soon.

I tried calling the stored procedure with the code below:
SELECT dbo.usp_RetTable('header','part_num','control_no','09030')

It didn't work.  Question: How do I call it?

It would be exec instead of SELECT.. Try the one below

EXEC dbo.usp_RetTable('header','part_num','control_no','09030')

EXEC dbo.usp_RetTable('header','part_num','control_no','09030')

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'header'.

Missed noticing earlier..

EXEC dbo.usp_RetTable 'header','part_num','control_no','09030'

EXEC dbo.usp_RetTable 'header','part_num','control_no','09030'

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'into'.
Msg 208, Level 16, State 1, Procedure usp_RetTable, Line 13
Invalid object name 'tempdb..copy'.

Msg 262, Level 14, State 1, Line 1
CREATE TABLE permission denied in database 'tempdb'.

I must have a permission problem with the database.  Anyway...  Thanks for HELP!

Is there anyway to do this without making a temp table?

Ok.. If then create that table in your database itself and delete it and the end of procedure as given below

This doesn't need any permissions

set ANSI_NULLS ON
SET NOCOUNT ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fxn_RetTable]
     (@sstrTableToRet VARCHAR(50), 
      @sstrFieldToRet VARCHAR(50), 
      @sstrFieldToSearch VARCHAR(50), 
      @sstrValToSearch VARCHAR(50)
     )
     AS 
BEGIN 
        DECLARE @sql nvarchar(4000), @sqlt nvarchar(1000);;
        SET @sqlt = 'select * into temp_copy from ' + @sstrTableToRet + ' where 1 = 2';
        EXEC sp_executesql @sqlt;
        SET @sql = 'SELECT ' + @sstrFieldToRet + ' FROM ' + @sstrTableToRet + ' WHERE ' + @sstrFieldToSearch + ' = ' + @sstrValToSearch;
        INSERT INTO temp_copy
        EXEC sp_executesql @sql;
        SELECT * FROM tempdb..copy;
END

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20: