Slow Loading, continued...

[APD]

Hello All,

Thank you every one for all your ideas. I haven’t written as I am still trying to resolve this thing.

My new theory now is:

1- figuring out how many Mondays, Tuesdays, … are there in the given date range

2- using a SQL SUM GROUP BY fldDay on my usual orders

3- Multiplying the results from #1 by the results from #2

4- Adding a fldProjected to my real orders

5- using SQL SUM of both fldProjected and fld Real

6- #3-SumOffldProjected are my projected orders.

7- Every time my usual orders change for one day, I use a SQL UPDATE to also change fldProjected in my real table, but this is where I’m stuck.

I keep getting:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
/irs/tpl_cust-orders-save.asp, line 150

And my code is:

irsPath = Server.MapPath("\___subwebs\SOMESITE.com\irs")

connectstr = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & irsPath & "\irs.mdb;" & _
"Uid=;Pwd="

Set cnn = Server.CreateObject("ADODB.Connection")

cnn.Open connectstr

Set rsReal = Server.CreateObject("ADODB.Recordset")
With rsReal

realSQL = "SELECT fldDate FROM [tblDIP_Cust-Orders] " & _
"ORDER BY fldDate DESC"

.Open realSQL, cnn, 1, 2
.MoveFirst
lastFutureDate = .Fields("fldDate")
.Close

End With

Set rsReal = Nothing

d = Session("OpenDate")

Do While (d <= lastFutureDate)

Response.Write( d &"'s day no = " & DateToDay(d) & " <BR>")

If (CInt(DateToDay(d)) = CInt(intDay)) Then

If strDates = "" Then

strDates = "(fldDate = #" & d & "#)"

Else

strDates = strDates & " OR (fldDate = #" & d & "#)"

End If

End If

d = d + 1
Loop


cnn.BeginTrans

...other code...

realSQL = ""
If (old <> order) And (strDates <> "") Then

realSQL = "UPDATE [tblDIP_Cust-Orders] " & _
"SET fldProjected = " & order & " " & _
"WHERE ((fldProdID = " & prodID & ") " & _
"AND (fldCustID = "& custID & ") " & _
"AND (" & strDates & "))"

cnn.Execute(realSQL) ‘Line 150 that gives me the err

End If
...other code...


cnn.CommitTrans

I know my SQL is correct, because I do Response.Write(realSQL), and copy/paste the output into Access, it updates my tbl w/o errors, so I’m thinking it ma be the way I’m executing my SQL?

I’ll appreciate any help.

Thank you