Re: Timeout with Where statement

From: Victor Koch ("Victor)
Date: 05/20/04 

Date: Thu, 20 May 2004 17:32:23 -0300

Hi Frank, try add more time in CommandTimeout property.

 cn.CommandTimeout=240
 cn.Open 

--
 Víctor Koch From Argentina.
"Frank Petrucci" <fpetrucci007@yahoo.com> escribió en el mensaje
news:AAED88D3-7888-4229-BC40-F266933133F9@microsoft.com...
> 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
>

Relevant Pages

  • Re: Reporting on nulls within a Database
    ... >>The master table in my Database is called tblApplication ... >dim tbl as DAO.tabledef ... >Dim fld as DAO.field ...
    (microsoft.public.access.modulesdaovba)
  • Re: Reporting on nulls within a Database
    ... >>The master table in my Database is called tblApplication ... >dim tbl as DAO.tabledef ... >Dim fld as DAO.field ...
    (microsoft.public.access.modulesdaovba)
  • Re: Reporting on nulls within a Database
    ... >>The master table in my Database is called tblApplication ... >dim tbl as DAO.tabledef ... >Dim fld as DAO.field ...
    (microsoft.public.access.modulesdaovba)
  • Re: Accessing multiple fields in report function
    ... The only other option would seem to be to open a recordset, ... The database already exists. ... >>> Dim Tbl As TableDef ... >>> For Each fld In Tbl.Fields ...
    (microsoft.public.access.modulesdaovba)
  • Re: Timeout with Where statement
    ... database engine will basically loop through the all records in a table to ... >it works, but when I try to loop for the same date fields, I get errors. ... > Dim cn As New ADODB.Connection ... > Dim fld As ADODB.Field ...
    (microsoft.public.data.ado)