I'm trying to manipulate a database by taking note of the last field values of previous week queried data then use that to do a computation and comparison using a queried data in the current week. The best solution I could think of was to open the database 2x, however, I need to do this for all various fields that I have meaning its 2x X n-fields. If I have 100-fields I will have to open it 100 X 2 = 200 open-close combination of the database.

Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
[Microsoft][ODBC Microsoft Access Driver] Too many client tasks.

' sample code of the problem

dim field_array(fields), wk

for i=1 to n-fields

sql="select * from table-name where field-type='"&field_array(i)&"' and week="&wk&" "

--- open database

--- close database
next

I have looked on google to find out about this;

from what I can make out, it seems people think it is a problem with the server (SQL Server) with limited licences, or in M$ Access problems with user rights. Also, closing recordsets/objects setting to nothing etc.

If you email your code, I will test it to see if its your server.

mymachinearray=arraymachines(machine_group_name) ' to contain all machines in tha area, filled before the following code
wk=3 'let's say week 3 of the year

' compose of different machines, nMachines determines the boundary of the machine type being queried
' if I have machine types = 100 under the machine group then fo loop does it 100 times
For i=1 to nMachines(machine_group_name)
' PREVIOUS WEEK
sql="SELECT * FROM team WHERE wk="&IntWk-1&" AND machine_name='"&mymachinearray(i)&"' ORDER BY machine,date"
' wk=IntWk-1 to get value of last week

' Do the Query
Set conn = server.CreateObject("ADODB.Connection")
connectstring = "Driver={Microsoft Access Driver (*.mdb)};" &_
"DBQ=" & Server.MapPath("machine_status_database.mdb")
conn.Open connectString
' Open the appropriate connection
Set rs = server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, adOpenKeyset,, adCmdText

If not(rs.EOF) or not(rs.BOF) Then
Do until rs.EOF
For Each item In rs.Fields

If item.name="id" Then
last_machine_status=rs.Fields("status")
end if
Next

rs.movenext
Loop
rs.close
Set rs = Nothing
Conn.Close

sql="SELECT * FROM team WHERE wk="&IntWk&" AND machine_name='"&mymachinearray(i)&"' ORDER BY machine,date"
' wk=IntWk to get value of last week

' Do the Query
Set conn = server.CreateObject("ADODB.Connection")
connectstring = "Driver={Microsoft Access Driver (*.mdb)};" &_
"DBQ=" & Server.MapPath("machine_status_database.mdb")

conn.Open connectString
' Open the appropriate connection
Set rs = server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, adOpenKeyset,, adCmdText

If not(rs.EOF) or not(rs.BOF) Then
Do until rs.EOF
For Each item In rs.Fields

If item.name="id" Then
current_machine_status=rs.Fields("status")
if last_machine_status="certain status" then
... statements ...
.... procedures ...
.... to get the a certain value ...
end if
end if
Next

rs.movenext
Loop
rs.close
Set rs = Nothing
Conn.Close

Next

Hi Russell,

I added the code in the previous reply.

I would appreciate your help.

regards, madeofer

i'm having the same problem. i think it is the host, and not your code. My code was working fine, and suddenly the error appeared, and now none of my code works - even the code that was previously working fine.

it's something to do with the opening/closing of recordsets (i'm using Access). hopefully my host will sort it out tomorrow......