Member Avatar for kiran2012

Hi,
My problem is, the Update query process is painfully slow. Every time user updates a value to a column, it is taking more than 1 min ! I have to update a cerain field Yes/No based on some other field values from the same table. Below is my code: Please suggest some ideas.

Call openConnection
rs.Open "SELECT distinct ProjectStrategy.Strategy_No,ProjectStrategy.Strategy_Group,ProjectStrategy.D_Date,ProjectStrategy.Voice_Special_Infra,Project_Details.* FROM ProjectStrategy, Project_Details where " & _
"Project_Details.Hours_of_Ops in (" & Sstring & ") and Project_Details.Region in (" & regList & ") and Project_Details.Country_Offshore in (" & CtryList & ") and Project_Details.State in (" & StateList & ") and Project_Details.City_Offshore in (" & CList & ") and Project_Details.Facility in (" & FList & ") and Project_Details.Building in (" & BuildList & ") and Project_Details.Floor in (" & FloorList & ") and Project_Details.Wing in(" & WingList & ");", con, 1, 2

If rs!Auto_Redundancy = "No" Then

    Set rs1 = New ADODB.Recordset
     rs1.Open "select * from Project_Details where Serial_No=" & rs!Serial_No & ";", con, 1, 2
     rs1!Auto_Red_Stat = "No"
     rs1.Update
     Set rs1 = Nothing
     End If
  • 2 Contributors
  • 1 Reply
  • 223 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Reverend Jim
Member Avatar for Reverend Jim

Normally when you do a join you specify a common (related) field. When you do a join without doing this you end up with a recordset that contains the product of each table. For example, If you have 6000 records in one table and 400 in the other you will end up with 2,400,000 records in the recordset. So I suspect the query that you coded is probably not the query you want. Can you describe in English what you are trying to do? It would help if you could post the table structures as well. While you are at it, could you build the query in a string and post a sample query string here as well?

Dim qry As String

qry = "SELECT distinct ProjectStrategy.Strategy_No, ProjectStrategy.Strategy_Group, " _
    & "       ProjectStrategy.D_Date, ProjectStrategy.Voice_Special_Infra, " _
    & "       Project_Details.* " _
    & "  FROM ProjectStrategy, Project_Details " _
    & " WHERE Project_Details.Hours_of_Ops IN (" & Sstring & ") " _
    & "   AND Project_Details.Region IN (" & regList & ") " _
    & "   AND Project_Details.Country_Offshore IN (" & CtryList & ") " _
    & "   AND Project_Details.State IN (" & StateList & ") " _
    & "   AND Project_Details.City_Offshore IN (" & CList & ") " _
    & "   AND Project_Details.Facility IN (" & FList & ") " _
    & "   AND Project_Details.Building IN (" & BuildList & ") " _
    & "   AND Project_Details.Floor IN (" & FloorList & ") " _
    & "   AND Project_Details.Wing IN (" & WingList & ")"

Debug.WriteLine(qry)
Edited by Reverend Jim
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.