This example uses the AppendChunk and GetChunk methods to fill an image field with data from another record, 32K at a time. In a real application, one might use a procedure like this to copy a record containing a photo or graphic image field from one table to another. In this example, the record is simply being copied back to same table. Note that all the chunk manipulation takes place within a single AddNew-Update sequence. Public Sub AppendChunkX() Dim cnn1 As ADODB.Connection Dim rstPubInfo As ADODB.Recordset Dim strCnn 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 cnn1 = New ADODB.Connection strCnn = "driver={SQL Server};server=srv;" & _ "uid=sa;pwd=;database=pubs" cnn1.Open strCnn
' Open the pub_info table. Set rstPubInfo = New ADODB.Recordset rstPubInfo.CursorType = adOpenKeyset rstPubInfo.LockType = adLockOptimistic rstPubInfo.Open "pub_info", cnn1, , , 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 ' Delete new record because this is a demonstration. rstPubInfo.Requery cnn1.Execute "DELETE FROM pub_info " & _ "WHERE pub_id = '" & strPubID & "'" rstPubInfo.Close cnn1.Close End Sub
Dim rstPubInfo As ADODB.Recordset
Dim strCnn 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 cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
' Open the pub_info table.
Set rstPubInfo = New ADODB.Recordset
rstPubInfo.CursorType = adOpenKeyset
rstPubInfo.LockType = adLockOptimistic
rstPubInfo.Open "pub_info", cnn1, , , 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 ' Delete new record because this is a demonstration.
rstPubInfo.Requery
cnn1.Execute "DELETE FROM pub_info " & _
"WHERE pub_id = '" & strPubID & "'" rstPubInfo.Close
cnn1.Close End Sub