none
[Microsoft][ODBC SQL Server Driver]Query timeout expired (#)

    Question

  • Hello:

    I have searched for a solution to this issue with no results yet.  Here's my scenario:

    Windows 7 Professional 64 bit

    Office 2010 32 bit

    From Access, using an ODBC link to SQL Server 2000, any Query (Append, Make Table) that exceeds about 2 minutes crashes with the timeout message [Microsoft][ODBC SQL Server Driver]Query timeout expired (#0).  Some of the posts suggest changing the ODBC timeout, but that option did not appear to be in the File, Options, Client Settings, Advanced section.  I also tried some VBA setting to change the QueryTimeout property of the database.

    Option Compare Database
    Option Explicit
    
    Public Sub SetTimeout()
          Dim Mydb As Database
          Set Mydb = CurrentDb
          Mydb.QueryTimeout = 1000
      End Sub
    

    I am not using VBA to run this query... just a regular query that links to the ODBC connected tables.

    Any suggestions?

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Monday, August 05, 2013 6:22 PM

Answers

  • When you open the query in design view, there is a property ODBC Timeout. Have you tried setting it to a higher value?
    • Marked as answer by RichLocus Tuesday, August 06, 2013 3:52 PM
    Monday, August 05, 2013 8:51 PM

All replies

  • Greetings,  Here is an ADODB routine that I use to write data from Access to Sql Server.  I use an ADODB command object to insert (append) data from an Access table to a Sql Server table.  Note:  Nulls and ADODB appear to be quite touchy, so in my routine I have 2 loops.  The first loop goes through the source data/recordset and adds only the values for the primary key column.  The 2nd For loop goes through the same data/recordset and updates the sql server table with data from the remaining columns -- updates each field individually if it is not null (which is actually a 3rd loop as I loop through each field in each record).  This is how I got around the problem with null values.  Also note:  there isn't really a bulk insert capability for pushing bulk data from Access to Sql Server (well, you could use openrowset in ADODB which acts like a bulk insert -- except that the Access DB has to reside on the same machine as the Sql Server DB -- won't work remotely - I have tested this out extensively).  So here is my routine for writing data from an Access Table to a Sql Server Table:

    Sub WriteDataToSqlServer()
       Dim cmd As New ADODB.Command, RSdao As DAO.Recordset, i As Integer, j As Integer
      
       cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=yourSqlSvr;Database=yourSqlDB;Trusted_Connection=Yes;"
       cmd.ActiveConnection.CursorLocation = adUseClient
       cmd.CommandType = adCmdText

       Set RSdao = CurrentDb.OpenRecordset("tbl1_Test1") '--table in the Access mdb

       cmd.CommandText = "Truncate Table tbl1"  '--clear out contents of table in Sql Server DB
       cmd.Execute
       DoEvents
       Do While Not RSdao.EOF
          '--add one column of data -- all rows -- from the Access table to the sql server Table
          '--this column will contain data in every row -- usually a Foreign Key column
          cmd.CommandText = "Insert Into tbl1(fldA) Select '" & RSdao(1) & "'"
          cmd.Execute
          RSdao.MoveNext
          i = i + 1
       Loop
         
       '--the following For Loop will loop through the rest of the columns in the same Access table and UPDATE
       '--the sql server table with rows that contain values and exclude rows where that column value is null
       '--for each remaining column in the Access table -- this is how you deal with Null values
       DoEvents
       For j = 2 To RSdao.Fields.Count - 1
          RSdao.MoveFirst
          i = 1  '--here - i - servers as a Unique Key value -- rowID
          Do While Not RSdao.EOF
             If Not IsNull(RSdao(j)) Then
                cmd.CommandText = "Update tbl1 set " & RSdao(j).Name & " = " & v1 & " Where rowID = " & i
                cmd.Execute
             End If
             RSdao.MoveNext
             i = i + 1
          Loop
       Next
       RSdao.Close
       cmd.ActiveConnection.Close
       Debug.Print "Done"
    End Sub


    Rich P

    Monday, August 05, 2013 8:12 PM
  • Rich:

    Thanks for posting the code.  It's not exactly what I am doing, since I'm downloading data FROM the SQL Server to a local Access Table.  Also, even though I use VBA, I was hoping to do it from a query.  But I will experiment with your code, make changes, and see what happens.  So far, though, anything I tried.. including a pass-through query, ended up crashing with the timeout.

    Thanks for sharing,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Monday, August 05, 2013 8:37 PM
  • When you open the query in design view, there is a property ODBC Timeout. Have you tried setting it to a higher value?
    • Marked as answer by RichLocus Tuesday, August 06, 2013 3:52 PM
    Monday, August 05, 2013 8:51 PM
  • Alphonse:

    I will give it a try and let you know.  I found it on the Web, but could not locate the property.  Your post allowed me to keep clicking on the query builder until that property was displayed... Thanks.


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Monday, August 05, 2013 9:50 PM
  • Alphonse:

    Your solution worked.  What threw me off is that I had to click outside the field areas in the blank spot of the query builder to show the properties of the entire query.  Otherwise you don't see the ODBC parameter.  Thanks for the solution.

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Tuesday, August 06, 2013 3:54 PM
  • Glad to help. Yes, I have always found it annoying the when one opens a query in design view and the property sheet defaults to a field instead of the query itself.

    BYW, I think the database querytimeout property only applies to queries created after the database property is set. Which makes sense. A global property should not override an individual object's property.

    Tuesday, August 06, 2013 4:13 PM
  • Rich

    You can also set the timeout to zero so it stays open as long as it needs to, but you could end up locking up your application if it fails to connect so be careful.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, August 06, 2013 4:29 PM
  • Bill:

    Thanks for the tip.

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Tuesday, August 06, 2013 5:38 PM