[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1188
  • Last Modified:

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

0
slamhound
Asked:
slamhound
1 Solution
 
momi_sabagCommented:
maybe
CurrentProject.Connection.Timeout = 0
0
 
wingkoCommented:
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
 
thiyagukCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now