Timeout with Where statement

From: Frank Petrucci (fpetrucci007_at_yahoo.com)
Date: 05/20/04

  • Next message: Victor Koch: "Re: Timeout with Where statement" 
    Date: Thu, 20 May 2004 12:46:06 -0700

    I am using MS Access 2000 to access a database on SQL Server 2000 and get a time out error running the query below. If I take away the Where statement, it works, but when I try to loop for the same date fields, I get errors.
    Thanks,
    Frank
    Option Compare Database

    Private Sub Form_Load()

    ' Initialize variables.
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    Dim sql As String
    Dim fld As ADODB.Field
    Dim ServerName As String
    Dim DatabaseName As String

    ' Put text box values into connection variables.
    ServerName = "sec-app-1101"
    DatabaseName = "MS_ROD_TEST"

    ' Specify the OLE DB provider.
    cn.Provider = "sqloledb"

    ' Set SQLOLEDB connection properties.
    cn.Properties("Data Source").Value = ServerName
    cn.Properties("Initial Catalog").Value = DatabaseName
    'Stop
    ' Windows NT authentication.
    cn.Properties("Integrated Security").Value = "SSPI"
    'cn.ConnectionTimeout = 60
    cn.Open
    sql = "Select top 10 * from (GRS.IDX_DOC INNER JOIN GRS.IDX_DOC_PROP ON GRS.IDX_DOC.DOC_ID = GRS.IDX_DOC_PROP.DOC_ID) INNER JOIN GRS.IDX_PROP ON GRS.IDX_DOC_PROP.PROP_ID = GRS.IDX_PROP.PROP_ID where (grs.idx_doc.recorded_date > '1/2/2003') and (grs.idx_doc.recorded_date < '1/16/2004')"
    'Set rs.ActiveConnection = cn

    rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly

    Set Flds = rs.Fields
    Dim TotalCount As Integer
    TotalCount = Flds.Count
    'If Flds("DOC_NUM").Value > 732761 Then
    '"1/2/1987" And Flds("GRS.IDX_DOC.RECORDED_DATE").Value < "1/16/1988" Then
        Do While (Not rs.EOF)
          Debug.Print Flds("DOC_NUM").Value
          Debug.Print Flds("RECORDED_DATE").Value
            'For Each fld In Flds
            ' Debug.Print fld.Value
            Stop
            'Next
            'Debug.Print ""
            rs.MoveNext
        Loop
    'End If
    'For Each fld In Flds
    ' Debug.Print fld.Name
    ' Stop
    'Next
    rs.Close

     'MSFlexGrid1.Clear
     ' MSFlexGrid1.AddItem "Product" & Chr(9) & "Value"
     ' Do While (Not rs.EOF)
     ' MSFlexGrid1.AddItem rs.Fields(0).Value & Chr(9) & rs.Fields(1).Value
     ' rs.MoveNext
     ' Loop

    ' Open the database.

    cn.Close

    End Sub

  • Next message: Victor Koch: "Re: Timeout with Where statement"

    Relevant Pages

    • Re: Establish connection with and transferring data to Access
      ... If you Google for SQL +"Insert into" you'll a dozen tutorials. ... The specific problem with your code is that you need to quote string ... Dim vConnection As New ADODB.Connection ... Doug's code opens and writes to an existing database. ...
      (microsoft.public.word.vba.general)
    • Re: WHERE statement syntax
      ... which is what database you're using. ... SQL syntax for dates, but I'll just cover the gamut and you can decide based ... > Dim myConnString ... > myConnString = Application ...
      (microsoft.public.frontpage.programming)
    • Re: Error Message: Two few parameters, Expected 2
      ... reasons the records are not saving properly to the database. ... Here the sql statement is referencing the form for the susgrant ... >> Dim rst As Recordset ...
      (microsoft.public.access.modulesdaovba)
    • Re: How to read data from stand alone SQL CE database on mobile device from VB.net 2005
      ... also use Rapi to delete the file on the device when you're finished with it. ... I have a stand alone SQL CE database on my windows mobile device. ... Dim conn As New SqlCeConnection("Data Source=\My ...
      (microsoft.public.sqlserver.ce)
    • Re: Inserting a concatenated string into a text field
      ... Dim db As Database ... Dim LSQL As String ... 'Create SQL to insert item numbers 1 to PregNum into table Pregnancy ...
      (microsoft.public.access.modulesdaovba)