Problem with Execute method on linked SQL Server tables

[scarymountains]

I have an odd problem with running a query in Access. I have an Access
97 front end (although I've been able to reproduce the problem Access
2000), with linked SQL Server tables. Everything was originally
developed in Access, and works with an Access 97 back end. I don't have
much experience with SQL Server, and I'm not administering it, so it's
taking longer to work through. I'm wondering if SQL Server stored views
would be a better alternative in some cases.

I have 3 tables:

tblTempImport
tblAccountDetails
tblPendingActions

All tables have a matching field of the same type (fldCRN, 11 character
text data type), which is the primary key in each case. tblTempImport
holds about 17,000 records. All three tables have other fields. I don't
think they're relevant to the problem, but if anyone wants the
information I'll post it later (it's all stored on my work computer at
the moment).

I have the following stored queries:

a_qselTest

SELECT tblTempImport.fldCRN
FROM tblAccountDetails RIGHT JOIN tblTempImport ON
tblAccountDetails.fldCRN = tblTempImport.fldCRN
WHERE (((tblAccountDetails.fldCRN) Is Null));

This should return all records from tblTempImport where there is no
matching record in tblAccountDetails.

a_qappTest

INSERT INTO tblPendingActions ( fldCRN )
SELECT a_qselTest.fldCRN
FROM a_qselTest;


This should append all records returned from a_qselTest into table
tblPendingActions.

I have the following procedure to automate this:


Sub TestAppend()

Dim dbs As Database, qdf As QueryDef, strSQL As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("a_qappTest")
qdf.Execute

Debug.Print dbs.RecordsAffected

qdf.Close
dbs.Close

End Sub

This had been working with no problems in Access 97, until I switched
to the linked SQL Server tables.

I can manually run the append query and get the expected result pretty
much instantly. Using the Execute method on a querydef object as in the
above procedure works, although takes about 30 minutes to run (I'd
originally thought that it caused Access to crash, not expecting it to
take anywhere near as long) .

I've tested a few things. If there are significantly less (I've tried
with two or three) records in tblTempImport, then it works. If I change
the join in a_qselTest to an inner join (although this wouldn't give me
the results I want), then it works. The number of records that
a_qselTest returns doesn't seem to make a difference, just the records
in the underlying table. It seems to be the outer join causing me
problems.

Any help, thoughts or suggestions would be very gratefully received.

thanks
Ann

 

[Sylvain Lafontaine]

The first step would be to use the SQL-Server Profiler to see what happens
on the server. Probably that you will see an horror story in all its
(glorious)details.

The first possibilities that comes to my mind is that Access is performing
the Right Join or the "Where fldCRN is Null" test locally instead of running
them on the server.

What happens if you integrate the first query a_qselTest directly into the
second as a subquery? (Don't know if you can do this in Access).

The best thing to do would be to use instead a sql pass-through query or a
stored procedure to make sure that everything is done directly on the
SQL-Server and don't make any round-trip locally.