Is there a better way to insert blob images from SQL to Access DB?

I know your not gonna like this but you should not at any time be inserting images into the database. As you see, it greatly ruins the performance of the database let alone the size it keeps increasing by. When you update a single column record (any record) it doesnt just update that value but copies a completly new matching record in memory. So if you update any column in that table, you are creating a new record. Look at the image size and picture it accumaliting by that size for every update you ever do on that record.

It is much better to store your images in a specified directory and instead simply store the file name & path to your database.

Please check the modify code which might improve the performance.

<%
Set rs = Server.CreateObject("ADODB.Recordset") 
rs.Open "SELECT * FROM "&lbxblobtable&" WHERE d_busdate >= CAST('"&BeginDate&"' AS smalldatetime) AND d_busdate <= CAST('"&EndDate&"' AS smalldatetime)", Connection 

Set Connection2 = Server.CreateObject("ADODB.Connection") 
Set sqlcmd = CreateObject("ADODB.Command") 
Connection2.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("WebLITS\App\"&randomNumber&"_"&lockboxname&".mdb")  
Connection2.Open 
Dim Ctr 
Ctr = 0
If not rs.EOF Then  
	Do While NOT rs.Eof 
		    If Ctr = 0 then 'Execute only for the first time
				SQL = " " 
				for each column in rs.fields 
				SQL = SQL & " ?," 
				next 
				SQL = left(SQL,len(SQL)-1) 
				SqlStmt = "INSERT INTO "&lbxblobtable&" VALUES (" & SQL & ")" 
			end if	
	 
	        sqlcmd.ActiveConnection = Connection2 
	        sqlcmd.CommandType = 1 
	        sqlcmd.CommandText = SqlStmt 
	 
	        sqlcmd.Parameters.Append sqlcmd.CreateParameter("ID", 3, 1, , rs("ID")) 
	        sqlcmd.Parameters.Append sqlcmd.CreateParameter("BUSDATE", 202, 1, Len(Trim(rs("D_BUSDATE"))), Trim(rs("D_BUSDATE"))) 
	        sqlcmd.Parameters.Append sqlcmd.CreateParameter("REFNO", 202, 1, Len(Trim(rs("D_REFNO"))), Trim(rs("D_REFNO"))) 
	        sqlcmd.Parameters.Append sqlcmd.CreateParameter("IMAGE", 205, 1, 2147483647, rs("imgData")) 
	        Set sqlrst = sqlcmd.Execute() 
	 
	        sqlcmd.Parameters.Delete ("ID") 
	        sqlcmd.Parameters.Delete ("BUSDATE") 
	        sqlcmd.Parameters.Delete ("REFNO")       
	        sqlcmd.Parameters.Delete ("IMAGE")               
	        rs.MoveNext              
	        Ctr = Ctr + 1
	Loop 
End If 
rs.Close
%>

Select allOpen in new window

I've decided to limit the blob images I want to retrieve from the lbxblobtable table.  I want to only copy over the BWF and BWR images.  What I did is create a recordset from the data table that has the 2 fields I need.  I want to then loop through the lbxblobtable table and copy only the records where the ID in the blob table match the BWF or BWR id's in the data table.  The problem I'm having is that when I loop it's copying only the first BWF id that it finds 20 times which is the correct number of images I need copied.  Can you see what I'm doing wrong?  If you look at the screen prints, the image from the data table, it shows all the id's I need from the blob table, but if you look at the blob table, all the images are BWF 212, it's not getting the other id's.

Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.Open "SELECT * FROM "&lbxdatatable&" WHERE d_busdate >= CAST('"&BeginDate&"' AS smalldatetime) AND d_busdate <= CAST('"&EndDate&"' AS smalldatetime)", Connection

Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM "&lbxblobtable&" WHERE ID = '" & rs1("BWF") & "' OR ID = '" & rs1("BWR") & "'", Connection

Set objFirstTable = Server.CreateObject("ADOX.Table")
objFirstTable.Name = ""&lbxblobtable&""

for each column in rs.fields 
ct = column.type 
	if ct = 200 then 
		ct = 202
	elseif ct = 131 then 
		ct = 3		
	else
	end if 
	objFirstTable.Columns.Append column.name, ct
next
	
objADOXDatabase.Tables.Append objFirstTable 
		
Set Connection2 = Server.CreateObject("ADODB.Connection")

Set sqlcmd = CreateObject("ADODB.Command")

Connection2.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("WebLITS\App\"&randomNumber&"_"&lockboxname&".mdb") 

Connection2.Open

Dim Ctr  
Ctr = 0 

Do While NOT rs1.Eof

	If Ctr = 0 then 'Execute only for the first time 
	Set rs = Server.CreateObject("ADODB.Recordset")
	rs.Open "SELECT * FROM "&lbxblobtable&" WHERE (ID = '" & rs1("BWF") & "' OR ID = '" & rs1("BWR") & "')", Connection	
		SQL = " "
		for each column in rs.fields
		SQL = SQL & " ?,"
		next
		SQL = left(SQL,len(SQL)-1)
		SqlStmt = "INSERT INTO "&lbxblobtable&" VALUES (" & SQL & ")"
	End If
	sqlcmd.ActiveConnection = Connection2
	sqlcmd.CommandType = 1
	
	sqlcmd.CommandText = SqlStmt

   	sqlcmd.Parameters.Append sqlcmd.CreateParameter("ID", 3, 1, , rs("ID"))
	sqlcmd.Parameters.Append sqlcmd.CreateParameter("BUSDATE", 202, 1, Len(Trim(rs("D_BUSDATE"))), Trim(rs("D_BUSDATE")))
	sqlcmd.Parameters.Append sqlcmd.CreateParameter("REFNO", 202, 1, Len(Trim(rs("D_REFNO"))), Trim(rs("D_REFNO")))
	sqlcmd.Parameters.Append sqlcmd.CreateParameter("IMAGE", 205, 1, 2147483647, rs("imgData"))
	Set sqlrst = sqlcmd.Execute()

	sqlcmd.Parameters.Delete ("ID")
	sqlcmd.Parameters.Delete ("BUSDATE")
	sqlcmd.Parameters.Delete ("REFNO")	
	sqlcmd.Parameters.Delete ("IMAGE")		
	rs1.MoveNext
	Ctr = Ctr + 1    	
Loop

rs1.Close

Select allOpen in new window

ScreenHunter-03-Mar.-26-13.51.gif
ScreenHunter-02-Mar.-26-13.50.gif