Re: Timeout with Where statement

From: Val Mazur (group51a_at_hotmail.com)
Date: 05/21/04 

Date: Thu, 20 May 2004 22:47:55 -0400

Hi Frank,

If you have some fields involved into joins or WHERE clause and these fields
are not indexed (you do not have an index for the fields in a database),
then your join could take pretty big amount of time to execute, because
database engine will basically loop through the all records in a table to
make an actual join. Try to work in this area and it should help rather than
increasing timeout time 

-- 
Val Mazur
Microsoft MVP
"Frank Petrucci" <fpetrucci007@yahoo.com> wrote in message 
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: Is This Possible ... ? Yes - Upload images to an Access database
    ... It looks like your databases folder is outside the root of your web ... I created a database exactly as you said, the only change I made was to ... and underneath an 'upload' button and a 'view images' button. ... Dim con As New Data.OleDb.OleDbConnection ...
    (microsoft.public.dotnet.framework.aspnet)