方法一: ADO Stream 需要ADO 2.5以上 http://support.microsoft.com/default.aspx?scid=kb;EN-US;258038方法二:AppendChunk Public Sub AppendChunkX() Dim cn As ADODB.Connection Dim rstPubInfo As ADODB.Recordset Dim strCn As String Dim strPubID As String Dim strPRInfo As String Dim lngOffset As Long Dim lngLogoSize As Long Dim varLogo As Variant Dim varChunk As Variant Const conChunkSize = 100 ' Open a connection. Set cn = New ADODB.Connection strCn = "Server=srv;Database=pubs;UID=sa;Pwd=;" cn.Provider = "sqloledb" cn.Open strCn 'Open the pub_info_x table. Set rstPubInfo = New ADODB.Recordset rstPubInfo.CursorType = adOpenDynamic rstPubInfo.LockType = adLockOptimistic rstPubInfo.Open "pub_info_x", cn, , , adCmdTable 'Prompt for a logo to copy. strMsg = "Available logos are : " & vbCr & vbCr Do While Not rstPubInfo.EOF strMsg = strMsg & rstPubInfo!pub_id & vbCr & _ Left(rstPubInfo!pr_info, InStr(rstPubInfo!pr_info, ",") - 1) & vbCr & vbCr rstPubInfo.MoveNext Loop strMsg = strMsg & "Enter the ID of a logo to copy:" strPubID = InputBox(strMsg) ' Copy the logo to a variable in chunks. rstPubInfo.Filter = "pub_id = '" & strPubID & "'" lngLogoSize = rstPubInfo!logo.ActualSize Do While lngOffset < lngLogoSize varChunk = rstPubInfo!logo.GetChunk(conChunkSize) varLogo = varLogo & varChunk lngOffset = lngOffset + conChunkSize Loop ' Get data from the user. strPubID = Trim(InputBox("Enter a new pub ID:")) strPRInfo = Trim(InputBox("Enter descriptive text:")) ' Add a new record, copying the logo in chunks. rstPubInfo.AddNew rstPubInfo!pub_id = strPubID rstPubInfo!pr_info = strPRInfo lngOffset = 0 ' Reset offset. Do While lngOffset < lngLogoSize varChunk = LeftB(RightB(varLogo, lngLogoSize - _ lngOffset),conChunkSize) rstPubInfo!logo.AppendChunk varChunk lngOffset = lngOffset + conChunkSize Loop rstPubInfo.Update ' Show the newly added data. MsgBox "New record: " & rstPubInfo!pub_id & vbCr & _ "Description: " & rstPubInfo!pr_info & vbCr & _ "Logo size: " & rstPubInfo!logo.ActualSize rstPubInfo.Close cn.CloseEnd Sub
HOWTO: 使用 ADO Stream 对象访问并修改 SQL Server BLOB 数据
http://support.microsoft.com/default.aspx?kbid=258038
方法一:
ADO Stream 需要ADO 2.5以上
http://support.microsoft.com/default.aspx?scid=kb;EN-US;258038方法二:AppendChunk Public Sub AppendChunkX() Dim cn As ADODB.Connection
Dim rstPubInfo As ADODB.Recordset
Dim strCn As String
Dim strPubID As String
Dim strPRInfo As String
Dim lngOffset As Long
Dim lngLogoSize As Long
Dim varLogo As Variant
Dim varChunk As Variant Const conChunkSize = 100 ' Open a connection.
Set cn = New ADODB.Connection
strCn = "Server=srv;Database=pubs;UID=sa;Pwd=;" cn.Provider = "sqloledb"
cn.Open strCn 'Open the pub_info_x table.
Set rstPubInfo = New ADODB.Recordset
rstPubInfo.CursorType = adOpenDynamic
rstPubInfo.LockType = adLockOptimistic
rstPubInfo.Open "pub_info_x", cn, , , adCmdTable 'Prompt for a logo to copy.
strMsg = "Available logos are : " & vbCr & vbCr Do While Not rstPubInfo.EOF
strMsg = strMsg & rstPubInfo!pub_id & vbCr & _
Left(rstPubInfo!pr_info,
InStr(rstPubInfo!pr_info, ",") - 1) & vbCr & vbCr
rstPubInfo.MoveNext
Loop strMsg = strMsg & "Enter the ID of a logo to copy:"
strPubID = InputBox(strMsg) ' Copy the logo to a variable in chunks.
rstPubInfo.Filter = "pub_id = '" & strPubID & "'"
lngLogoSize = rstPubInfo!logo.ActualSize
Do While lngOffset < lngLogoSize
varChunk = rstPubInfo!logo.GetChunk(conChunkSize)
varLogo = varLogo & varChunk
lngOffset = lngOffset + conChunkSize
Loop ' Get data from the user.
strPubID = Trim(InputBox("Enter a new pub ID:"))
strPRInfo = Trim(InputBox("Enter descriptive text:")) ' Add a new record, copying the logo in chunks.
rstPubInfo.AddNew
rstPubInfo!pub_id = strPubID
rstPubInfo!pr_info = strPRInfo
lngOffset = 0 ' Reset offset. Do While lngOffset < lngLogoSize
varChunk = LeftB(RightB(varLogo, lngLogoSize - _
lngOffset),conChunkSize)
rstPubInfo!logo.AppendChunk varChunk
lngOffset = lngOffset + conChunkSize
Loop rstPubInfo.Update ' Show the newly added data.
MsgBox "New record: " & rstPubInfo!pub_id & vbCr & _
"Description: " & rstPubInfo!pr_info & vbCr & _
"Logo size: " & rstPubInfo!logo.ActualSize rstPubInfo.Close
cn.CloseEnd Sub