Results 1 to 2 of 2

Thread: Save/Retrieve Image from database using excel vba

  1. #1

    Save/Retrieve Image from database using excel vba



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to retrieve image form the database , but i get the error like
    'file not found' run time error '53'

    I only want to save and retrieve image from database. Any suggestions on VBA that can help me ?

    I am sending the code in My_code.txt file . Please sMy_code.txtee the attachment

  2. #2
    I am trying to retrieve image form the database , but i get the error like
    'file not found' run time error '53'

    I only want to save and retrieve image from database. Any suggestions on VBA that can help me ?

    database name is-ZlegalData.mdb, table name -ZLegalDataImage
    design:
    field name data type

    ID autonumber
    Image_ ole object
    Amount_ number


    My Code--------------------------------------------------

    Private Sub Load_Image_Click() ' for loading image
    Dim ImgFileFormat As String
    Dim pict As String

    GetPict:
    pict = Application.GetOpenFilename(FileFilter:="Pictures,*.jpg", Title:="Select a Picture", MultiSelect:=False)
    Dim ppath As String


    Open pict For Binary As #1

    ReDim bytData(FileLen(pict))
    Get #1, , bytData
    Close #1

    UserForm1.TextBox2.Text = pict
    UserForm1.Image1.Picture = LoadPicture(pict)


    Load_Image.Enabled = False
    Save8.Enabled = True
    End Sub



    Private Sub Save8_Click() ' for saving image in the database
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection

    Dim folderPath As String
    folderPath = Application.ActiveWorkbook.Path
    folderPath = folderPath & "\ZlegalData.mdb"

    If TextBox1.Text = "" Or TextBox1.Text = 0# Then
    MsgBox " Please enter the amount", vbCritical
    TextBox1.SetFocus
    Exit Sub
    Else
    With cnn
    'ZlegalData.mdb
    .Provider = " Microsoft.Jet.oledb.4.0"
    .Properties("Data Source") = folderPath
    '.Properties("Jet OLEDBatabase Password") = "Password123"
    .Open
    .Execute "INSERT INTO ZLegalDataImage (Image_,Amount_)VALUES (' bytData ' ,'" + TextBox1.Text + "')"
    .Close
    End With
    Image1.Picture = LoadPicture("")
    TextBox2.Text = ""
    Save8.Enabled = False
    Load_Image.Enabled = True
    TextBox1.Text = ""
    Load_Image.SetFocus
    End If

    End Sub



    Sub Start_Retrieve_image() ' retrieve image from the database
    Dim rst1 As ADODB.Recordset
    Dim stm As ADODB.Stream
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection

    Dim folderPath As String
    Dim sts As String

    folderPath = Application.ActiveWorkbook.Path
    folderPath = folderPath & "\ZlegalData.mdb"

    sts = "SELECT Image_ from ZLegalDataImage where ID = 10"

    With cnn
    .Provider = " Microsoft.Jet.oledb.4.0"
    .Properties("Data Source") = folderPath
    .Open
    End With

    Set rst1 = cnn.Execute(sts)

    Set stm = New ADODB.Stream

    stm.Type = adTypeBinary
    stm.Open
    stm.Write rst1("Image_").Value ' write bytes to stream

    stm.Position = 0
    ' stm.SaveToFile "c:\temp.bmp", adSaveCreateOverWrite '*** Run-time error '3004': Write to file failed error ****
    ' UserForm1.Image1.Picture = LoadPicture("c:\temp.bmp") '*** Run-time error '3004': Write to file failed error ****
    ' Me.Image0.PictureData = stm.Read
    'UserForm1.Image1.Picture = stm.Read
    UserForm1.Image1.Picture = LoadPicture(stm.Read) '*** this line'file not found' run time error '53 error ****

    End Sub

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •