Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Access Frontend - Sql 2000 Backend

Options
  • 26-05-2008 5:22pm
    #1
    rebellad
    Registered Users Posts: 227 ✭✭


    I am writing an app with an access frontend and sql 2000 backend. In one screen I mark records for processing(Set a variable to L), I then click on a button to process the records it works fine when up to ten records are marked together but if I mark anymore than ten it still only processes the ten and does not finish the Stored Procedure that is used to process them ( I know this as I right to a table when the stored procedure is finished).I do not get a timeout error or any error for that matter. It seems as if the Stored Procedure is returning to Access before it processes all the records?? Any suggestions would be great??


Comments

  • Options
    #2
    Seth Brundle
    Moderators, Politics Moderators Posts: 39,827 Mod ✭✭✭✭Seth Brundle


    Presumably then you are using VBA?
    What query is being run?


  • Options
    #3
    rebellad
    Registered Users Posts: 227 ✭✭rebellad


    I am indeed using VBA. What the sp does is uses a cursor to go through all the marked records and puts them into some tables. But everytime it gets past 10 records it just stops and returns to access. It's as if there is a time restriction on how long it(access) waits for the SP (The sp only take 5-6 seconds on query analyser)


  • Options
    #4
    stevenmu
    Moderators, Society & Culture Moderators Posts: 9,689 Mod ✭✭✭✭stevenmu


    There is a query time-out value in the server properties in SQL Server, altough it's usually not set, not sure about VBA through access.

    Do you really need to use a cursor, you could very possibly achieve the same thing much quicker without it. What is your SP doing?


  • Options
    #5
    kayos
    Registered Users Posts: 2,494 ✭✭✭kayos


    Cursors are the work of the devil, or a VB programmer let lose on a DB. As said above you should get away without using them in most cases and have faster code as a result.

    As to why its only processing 10 rows and failing without reporting any error thats a strange one. Try running SQL Profiler when your running your query, if there is a problem with the SP you should be able to see what it is. Or even just run the procedure in Query Analyser and see what comes back.

    How long does the query take for the 10 records?


  • Options
    #6
    rebellad
    Registered Users Posts: 227 ✭✭rebellad


    After a lot of testing and head banging we finally managed to get this sorted. We were using print statements in the sp for debugging and for some reason when the information from the print statements was being returned to Access it was causing the connection to drop. As soon as we got rid of the print statements it worked fine!!!


  • Advertisement
Advertisement