August 3, 2005 at 9:15 pm
Hi,
I experience the following problem. We use ADODB to access a SQL server database. One table has BLOBs stored in it (general objects like documents and pictures). This all works fine in the program, but when I try to copy this record from one database to another using the code below it will generate an error on the rstRestoreTable.Update statemement: Run-time error '-2147467259 (80004005)', ONLY when a field contains a BLOB larger than around 500,000 bytes. All smaller BLOBs and any other field type copies without any trouble, just large BLOBs it errors out. I tried using an intermediate byte buffer, same effect. I also experimented with the cursor location (now client in the code) to no avail.
Do you know what I do wrong or are we looking at a bug or is there a buffer size setting somewhere?
Thanks, Edwin
Public Sub subRestoreCopyTable(strSourceTable As String, strDestinationTable As String)
Dim cnnCurrent As ADODB.Connection
Dim rstImportedTable As New ADODB.Recordset
Dim rstRestoreTable As New ADODB.Recordset
Dim fldColumn As ADODB.Field
Dim lonRecordCount As Long
Set cnnCurrent = CurrentProject.Connection
rstImportedTable.Open strSourceTable, _
cnnCurrent, adOpenStatic, conLockType
lonRecordCount = 0
If Not rstImportedTable.EOF Then
rstRestoreTable.CursorLocation = adUseClient
rstRestoreTable.Open strDestinationTable, _
cnnCurrent, adOpenStatic, conLockType
Do While Not rstImportedTable.EOF
rstRestoreTable.AddNew
For Each fldColumn In rstImportedTable.Fields
rstRestoreTable.Fields(fldColumn.Name) = rstImportedTable.Fields(fldColumn.Name)
Next fldColumn
rstRestoreTable.Update
rstImportedTable.MoveNext
lonRecordCount = lonRecordCount + 1
DoEvents
Loop
rstRestoreTable.Close
End If
rstImportedTable.Close
cnnCurrent.Close
End Sub
August 4, 2005 at 10:41 pm
I suggest that you look at using ADODB.Stream:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdobjstream.asp
...
Dim oST As ADODB.Stream
...
For Each fldColumn In rstImportedTable.Fields
If fldColumn.Name = "Icon" ' change this for your Blob field name
Set oST = New ADODB.Stream
oST.Type = adTypeBinary
oST.Open
oST.Write rstImportedTable.Fields("Icon")
rstRestoreTable.Fields("Icon") = oST.Read
If Not oST Is Nothing Then
If oST.State = adStateOpen Then
oST.Close
End If
Set oST = Nothing
End If
Else
rstRestoreTable.Fields(fldColumn.Name) = rstImportedTable.Fields(fldColumn.Name)
End If
...
Or lookup AppendChunk / GetChunk:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthappendchunkx.asp
Andy
August 6, 2005 at 9:56 pm
Thanks Andy,
Your solution helped me in the right direction. The code below I use now to copy tables is more generic, hope others will be able to use it (again I removed some progress display code). It must have been a buffer size problem why the normal copy would not work.
Regards, Edwin.
Public Sub subRestoreCopyTable(strSourceTable As String, strDestinationTable As String)
Dim cnnCurrent As ADODB.Connection
Dim rstImportedTable As New ADODB.Recordset
Dim rstRestoreTable As New ADODB.Recordset
Dim fldColumn As ADODB.Field
Dim lonRecordCount As Long
Dim stmStream As ADODB.Stream
Set cnnCurrent = CurrentProject.Connection
rstImportedTable.Open strSourceTable, _
cnnCurrent, adOpenStatic, conLockType
lonRecordCount = 0
If Not rstImportedTable.EOF Then
rstRestoreTable.Open strDestinationTable, _
cnnCurrent, adOpenDynamic, conLockType
Do While Not rstImportedTable.EOF
rstRestoreTable.AddNew
For Each fldColumn In rstImportedTable.Fields
If (fldColumn.Name <> "upsize_ts") And (fldColumn.Name <> "rowguid") Then
If (fldColumn.Type = adLongVarBinary) Then
If Not IsNull(rstImportedTable.Fields(fldColumn.Name)) Then
Set stmStream = New ADODB.Stream
stmStream.Type = adTypeBinary
stmStream.Open
stmStream.Write rstImportedTable.Fields(fldColumn.Name)
rstRestoreTable.Fields(fldColumn.Name) = stmStream.Read
If Not stmStream Is Nothing Then
If stmStream.State = adStateOpen Then
stmStream.Close
End If
Set stmStream = Nothing
End If
End If
Else
rstRestoreTable.Fields(fldColumn.Name) = rstImportedTable.Fields(fldColumn.Name)
End If
End If
Next fldColumn
rstRestoreTable.Update
rstImportedTable.MoveNext
lonRecordCount = lonRecordCount + 1
DoEvents
Loop
rstRestoreTable.Close
End If
rstImportedTable.Close
cnnCurrent.Close
End Sub
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply