Change Timeout on ADODB Query

Posted on 2008-11-17
Last Modified: 2012-06-27
I have an Access 2000 database with a MS SQL 2005 back end. I'm running queries that are timming out as the database grows. We have a long term solution but I need something to get them up and running now.

How do I increase the timeout to run the query on the code below?
Dim rs As ADODB.Recordset

    Set rs = New ADODB.Recordset

    SQL = "Select * from MyTable"

    rs.Open SQL, CurrentProject.Connection

Open in new window

Question by:slamhound
    LVL 37

    Expert Comment

    CurrentProject.Connection.Timeout = 0
    LVL 2

    Accepted Solution

    Dear slamhound,

    Just sharing what I usually do when getting performance issue in my access application:
    1. compact & repair database then test the performance
    go to menu: Tools  - Database utilities

    2. If it still doesn't work, then you can change the commandtimeout property in connection object. commandtimeout is in seconds.

    this is what the code should like :

    Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset
        SQL = "Select * from MyTable"
        'in 10 minutes, default is 30
        rs.Open SQL, CurrentProject.Connection

    Open in new window

    LVL 7

    Expert Comment

    1.Try to Rebuild all Indexes
    2.Try to reduce the amount of data from the select query (Use where clause in sql queries)

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now