database objects "dying"

[HotMadras]

I've written the following subroutine to "merge" two database tables. It takes two parameters, a database to copy from and a database to copy to. It cycles through the database to copy from and where there is a record which exists in the other database it edits it, and where the record does not exist in the other database, it adds it. Essentially it takes the records from the "copy from" database and fits them into the "copy to" database.

private sub mergeDB (newdb, olddb)
  	Dim dbc
	dim dbc2
	Dim strConn
	dim strConn2
	dim rs
	dim rs2
	strConn = "Driver={Microsoft Access Driver (*.mdb)};DBQ="& olddb
	strConn2 = "Driver={Microsoft Access Driver (*.mdb)};DBQ="& newdb
	Set dbc = Server.CreateObject("ADODB.Connection")
	set dbc2 =server.createobject("adodb.connection")
	dbc.open strConn
	dbc2.open strConn2
	strSQL2="SELECT * FROM parts"
	set rs2=server.createobject("adodb.recordset")
	rs2.open strSQL2, dbc2
	rs2.movefirst
	n=1
	while not rs2.eof
		strSQL="select * from parts where partno='" & rs2("partno") & "'"
		response.write &quot;SQL string: &quot; & strsql & &quot;<br>&quot;
		set rs=server.createobject(&quot;adodb.recordset&quot;)
		rs.open strSQL, dbc, 3, 3
		if rs.eof then 
			rs.addnew
			for i=1 to 8
				response.write rs2.fields(i).value & &quot;<br>&quot;
				if rs2.fields(i).value<>&quot;&quot; then 
					rs.fields(i).value=rs2.fields(i).value
				else
					rs.fields(i).value=&quot;&quot;
				end if
			next
			response.write n
			n=n+1
			rs.update
		else
			while not rs.eof
				for i=1 to 8
					response.write rs2.fields(i).value & &quot;<br>&quot;
					if rs2.fields(i).value<>&quot;&quot; then 
						rs.fields(i).value=rs2.fields(i).value
					else
						rs.fields(i).value=&quot;&quot;
					end if
				next
				response.write n
				n=n+1
				rs.update
				rs.movenext
			wend
		end if
		rs.close
		set rs=nothing
		rs2.movenext
	wend
	rs2.close
	set rs2=nothing
	dbc.close
	set dbc=nothing
	dbc2.close
	set dbc2=nothing
	
 End sub

The problem is that I always get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005' 

[Microsoft][ODBC Microsoft Access Driver] Object invalid or no longer set. 

/***********/Loader.asp, line 255

The script always fails at the same point, after processing 678 records. Does anyone know why this might be the case? Do database objects have a "shelf life"? Is there any way I can get it to process the entire table?

 

[Jonax]

Which one is line 255? This is not a bug - it's an undocumented feature...
;-)

 

[HotMadras]

Line 255 appears to be the following if statement:

if rs2.fields(i).value<>&quot;&quot; then
					rs.fields(i).value=rs2.fields(i).value
				else
					rs.fields(i).value=&quot;&quot;
				end if

I have no idea why this should cause a problem after 678 iterations, when it works perfectly before that.

 

[HotMadras]

I've fixed the problem. I eventually ended up working around it, rather than solving it directly. It seems that the object which gives up the ghost is the db2 object. If you keep recreating it every couple of hundred records or so, it seems to work fine. Though why it should have failed in the first place I have no idea. Grrr!