can't save 1000 records in one go?

Results 1 to 4 of 4

Thread: can't save 1000 records in one go?

  1. #1
    Join Date
    Dec 1969
    Posts
    56

    Default can't save 1000 records in one go?

    I wish to save 1000 records into a MS Access DB using asp on the web. Every time I got 63 records saved and got the following error message:<BR>"Microsoft OLE DB Provider for ODBC Drivers error &#039 80040e4d&#039 <BR>[Microsoft][ODBC Microsoft Access Driver] Too many client tasks."<BR>I&#039ve considered the curser type and locking type to fast the speed, but it is still not work. Any suggestions about it? <BR>thanks a lot ! !<BR><BR>The code I used is as follows:<BR>str=split(plate,chr(44)+chr(32))<BR>fo r each item in str<BR> <BR>Set DataConn = Server.CreateObject("ADODB.Connection")<BR>DataCon n.ConnectionTimeout = Session("DataConn_ConnectionTimeout")<BR>DataConn. CommandTimeout = Session("DataConn_CommandTimeout")<BR>DataConn.Ope n session("DSN")<BR>Set cmdTemp = Server.CreateObject("ADODB.Command")<BR>Set RSPlate = Server.CreateObject("ADODB.Recordset")<BR>cmdTemp. CommandText ="select* from TblInquiryPlate where "<BR>cmdTemp.CommandType = 1<BR>Set cmdTemp.ActiveConnection = DataConn<BR>RSPlate.Open cmdTemp, , 0, 3<BR> <BR>RSPlate.addnew<BR>RSPlate("plate")=item<BR>RSP late("UserID")=session("login")<BR>RSPlate.update< BR> <BR>next<BR><BR>can you have a look to see if any part is causing this problem.<BR>You mentioned before about MS Access only have 64 dataconnection.....<BR><BR>thanks heaps !!!!!<BR>

  2. #2
    John Weeflaar Guest

    Default RE: can't save 1000 records in one go?

    I think you only need to create one instance of the connection, of the recordset, and of the Command (instead of 1000). Then, add as many records as needed to the recordset. To do that, try to move the "for each ..." statement to a line just before the "addnew" statement.<BR>for each item in str &#039 to this line<BR>RSPlate.addnew<BR>RSPlate("plate")=item<BR >RSPlate("UserID")=session("login")<BR>RSPlate.upd ate<BR>next<BR><BR>Hope that helps.<BR><BR>John

  3. #3
    Mark Wilson Guest

    Default RE: can't save 1000 records in one go?

    You are opening a new connection and recordset for each iteration of the loop. Try moving most of the code outside the for..next loop. i.e.<BR><BR>str=split(plate,chr(44)+chr(32))<BR>Se t DataConn = Server.CreateObject("ADODB.Connection")<BR>DataCon n.ConnectionTimeout = Session("DataConn_ConnectionTimeout")<BR>DataConn. CommandTimeout = Session("DataConn_CommandTimeout")<BR>DataConn.Ope n session("DSN")<BR>Set cmdTemp = Server.CreateObject("ADODB.Command")<BR>Set RSPlate = Server.CreateObject("ADODB.Recordset")<BR>cmdTemp. CommandText ="select* from TblInquiryPlate where "<BR>cmdTemp.CommandType = 1<BR>Set cmdTemp.ActiveConnection = DataConn<BR>RSPlate.Open cmdTemp, , 0, 3<BR><BR>for each item in str<BR><BR> RSPlate.addnew<BR> RSPlate("plate")=item<BR> RSPlate("UserID")=session("login")<BR> RSPlate.update<BR><BR>next<BR><BR>You should also close recordsets and connections explicitly once you are finished with them (Access leeks!)<BR><BR>RSPlate.close<BR>Set RSPlate = nothing<BR>cmdTemp.close<BR>Set cmdTemp = nothing<BR>DataConn.close<BR>Set DataConn = nothing<BR><BR>Hope this helps<BR><BR>Mark Wilson<BR><BR>

  4. #4
    Join Date
    Dec 1969
    Posts
    56

    Default RE: thanks heaps

    I got it, thank you very much for your advice. I should know that<BR>access only open 64 connections, but Ijust couldn&#039t think of in that way. That&#039s why somebody&#039s advice is very import. You saved<BR>me a lot of headache.<BR><BR>kathy


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •