Change Timeout on ADODB Query

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

LVL 10
slamhoundAsked:
Who is Participating?
 
wingkoConnect With a Mentor Commented:
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
    CurrentProject.Connection.CommandTimeout=600
 
    rs.Open SQL, CurrentProject.Connection

Open in new window

0
 
momi_sabagCommented:
maybe
CurrentProject.Connection.Timeout = 0
0
 
thiyagukTech Lead Commented:
1.Try to Rebuild all Indexes
2.Try to reduce the amount of data from the select query (Use where clause in sql queries)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.