[AccessD] Problematic code storing an image

Gustav Brock Gustav at cactus.dk
Fri May 20 04:52:49 CDT 2005


Hi Arthur

Not going into detail with your code, but have a look here on storing
blobs:

http://www26.brinkster.com/alzowze/blobs.asp 

/gustav

>>> artful at rogers.com 05/20 1:10 am >>>
Following is the code that I'm using in the attempt to allow the user
to 
browse to select an image file, read the image and then store it into a

column in SQL (not Access) defined as Image data type. The code
executes 
fine, the debug messages post the correct file sizes and so on, but the

image does not appear on the Access form.

New eyes often help, so I'm asking for new eyes....

<code>
'form declarations code:
Option Compare Database
Option Explicit
'declarations
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mStream As ADODB.Stream
'Revise this constant to suit the production or development
environment.
'This constant is produced by running the following command in the
debug 
window of Access 2003.
'? currentproject.Connection
'To reproduce another version that may differ, run the application and

then re-run the above command.
Const conCNN = "Provider=Microsoft.Access.OLEDB.10.0;Persist Security 
Info=False;Data Source=rock;" & _
    "User ID=sa;Password=xxxxx;Initial Catalog=TallGirl;Data 
Provider=SQLOLEDB.1"

'this code is tied to a button on the form that should display the
table 
data and image if any:
Private Sub PictureImport_cmb_Click()
    Dim strFileName As String
    Dim strPK As String
    Dim strSQL As String
   
    With Me
        strFileName = .PictureFileName
        strPK = .StyleCode
        strSQL = "Select * from tblStyle WHERE StyleCode = " & Chr(39)
+ strPK + Chr(39)
        Debug.Print strSQL

        Set cn = New ADODB.Connection
        cn.Open conCNN
       
        Set rs = New ADODB.Recordset
        rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic

        If rs.EOF Then
            Debug.Print "No record found for StyleCode " + strPK
            Return
        Else
            Debug.Print rs.RecordCount & " row(s) found."
        End If
       
        Set mStream = New ADODB.Stream
        mStream.Type = adTypeBinary
        mStream.Open
        mStream.LoadFromFile .PictureFileName
        Debug.Print "Size of Picture column before update = " &
Len(rs.Fields("Picture"))
        Debug.Print "Size of mStream after read = " & mStream.Size
        rs.Fields("Picture").Value = mStream.Read
        rs.Update
        Debug.Print "Size of Picture column after update = " &
Len(rs.Fields("Picture"))
        Debug.Print "Size of Fields(Picture) mStream after read = " &
mStream.Size
       
        rs.Close
        cn.Close
    End With
    Me.Refresh
    Debug.Print "Length of Picture Column now: " & Len(Me.Picture)
End Sub
</code>

TIA,
Arthur




More information about the AccessD mailing list