Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Problem with Execute method on linked SQL Server tables

Discussion in 'Information Technology' started by scarymountains@gmail.com, Jul 28, 2006.

  1. scarymountains@gmail.com

    scarymountains@gmail.com
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

  3. Sylvain Lafontaine

    Sylvain Lafontaine
    Expand Collapse
    Guest

    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.

    --
    Sylvain Lafontaine, ing.
    MVP - Technologies Virtual-PC
    E-mail: http://cerbermail.com/?QugbLEWINF


    <scarymountains@gmail.com> wrote in message
    news:1150282696.368981.10310@y43g2000cwc.googlegroups.com...
    >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
    >
     

Share This Page