Access Front End / SQL 2K Back End / Slow linked Query 1

[LearningSql]

Good day to All --

First off Thank You for all the advice everybody has given to me. I need to start to mark posts as Helpful as I have not given my dues to those that have assisted me. My apologies for that -- I'll do better.

Second -- I do not know if this is a post for this Forum and/or the Sql Server Forum. It could be for both but as noted below you'll understand why I posted here also.

I am the sole programmer for both a SQL 2K Backend and Access97 Front End for my group (An unfortunate circumstance but a nice challenge from my POV). I have developed a Db Backend with a table holding about 1,000,000+ rows of data with a 3 Field Primary Key (not my design -- I'm taking over this one).

On the Front End I have an Access97 DB with 1 table consisting of the Primary Keys only (the same 3 fields as the SQL 2K Table) and an ODBC link to the associated table.

When I query the SQL db (via INNER JOIN) using only 1 record in the table the process locks up for about 30-45 minutes. When I individually specify the 3 fields in the Query (no INNER JOIN) the 1 record comes up within 5 seconds. I will usually have about 3500+ records to query when I get past this point.

Any pointers I should be looking for in the Back End with SQL Server? I know that a Pass-Through Query may help in using the processing power of the server but I think I need help developing the statement. If I have a Local Table A and a Linked Table B, how would I write the Pass Through Query to use the information from Local Table A?

Any information will be useful to me in researching this issue.

Thank you and have a great day!
Pru

 

[cmmrfrds]

One way to do it is to push the local access table (only fields you need) down to sql server to do the joins - should be fast on the server. I am assuming the access table only has a couple of thousand records. You can do this with some vba code using an ADO connection. Make sure you have one of the later MDAC libraries on your PC such as 2.6 or 2.7.
Steps in code.
1. make ado connection to sql server.
2. insert the local access records to an sql server temp table. This can be done with the execute method on the ado connection.
3. make a stored procedure that joins the temp table to the sql server table to return the resultset you want. As long as you leave the connection open the temp table will hang around in sql server.
4. on the same connection execute the stored procedure and

 

[cmmrfrds]

I ran out of space typing in on the last response. To continue on step 4. Is tek-tips limiting the size??

You can return the resultset from the stored procedure to an access recordset. Unfortunately, access 97 does not have a recordset object on the Form so the recordset cannot be returned directly to a Form as in access 2000.

 

[LearningSql]

Thanks cmmrfrds. Logically speaking it sounds good as I was thinking about the same but did not complete the thought process and was sidetracked on another issue.

I'll check your thoughts.
Thanks again.