Question Attaching image file in or along with database record! (1 Viewer)

[Waheed2008]

Hello,
I have got a problem and need guidance. I have developed a database for maintaining the records of invoices. I want to save the scanned copy of actual invoice along with database record. How should I do that? There can be two options; either I store the image into database directly or store the image in some folder and mention the path of this folder and file into DB table field. What should I do? Is there any other good option which fulfills my requirement? Basic idea is to attach image file with the relevant record in such a way that in form/report, when I click that image it should be opened in appropriate image viewing application.
Thanks

 

[ajetrumpet]

I would look into using the embedded OLE object field. That might get you down the right track. Try it out and see what you think.

 

[Mr. B]

Personally, I would never want to actually store any images in the database. I would only store the path to the image. I would, however, provide ample coding to check for the existance of the file when it is to be displayed, and if the file was not found, provide an option for allowing the user to specify the location of the file.

If you actually store the image in the database your database is going to get very large very quickly. Storing only the path to the file takes relatively very little storage space.

Just my two cents worth.

 

[Waheed2008]

Thanks for your response. I also was in favor of not storing image into DB. In Access 2007, I found a control of inserting hyperlink. Should I use this type of thing?? I could not see any OLE controll in Form Design View!

I want to add functionality into DB such that link is to be stored into table field and when I click on the image link, the image should be opened in "Windows Image and Fax Viewer", for example. How should I do that??

Thanks again

 

[Mr. B]

There is an image control in the Access tool box which can be used to display the image in an Access form. Then you could use Shell or FollowHyperlink to open that same object in the other applicaiation. Seach for either of these action commands in the VBA help file.

 

[irish634]

I'm also in favor of not storing images in the database. Generally I use links and store in a separate folder in the root folder.

However, I do have a few apps that it was requested to store images/files, tied to a record. One app stored images, and the other stored 1-3 page pdf documents. In BOTH of these cases I limited file size to help alleviate any DB bloating. If I remember I limited the images to 200kb and the files to 500kb (not sure where I came up with those).

Do a search for "BLOB" and you'll find an efficient method to embed your files.

 

[DCrake]

here is a link to a sample app that uses embedded objects.
Sample databases

David

 

[Waheed2008]

I have tried to use Shell comand nad successfully achieve my targets. Now I want to add "Select Case" statements as either the file is in pdf form or jpg form. But I dont know how to extract the extension of file (from path of text box) but error is there. I have used the following code:

Select Case Ucase(Right(Me.text1),3)

Case "PDF"
Shell Statement

End Select

How to get extension & use it
Thanks

 

[DCrake]

It seams I pointed you to a wrong demo. here is a demo that opens up files in their native language without the need to query what type of file it is.

David

 

[Waheed2008]

It shows error

"Run-time error '2455'
You entered an expression that has an invalid reference to the property FileSearch"

Perhaps I am not understanding how to run it !

 

[DCrake]

What version of access are you using and have you checked your tolls and references for missing objects.

David

 

[Waheed2008]

I am using Access 2007. Right now I have default configuration of access. How to check the required tools & References ?

 

[DCrake]

This demo has not been tested on 2007 so I cannot confirm what is causing the problem.

Sorry

David

 

[Waheed2008]

Thanks a lot for your help. I shall check it on some older version.
Thanks for your time
have a nice day

 

[Niroth]

Try search for keyword "find file" or something like that, there was a post here that helped a lot.

Separately, I have here a sample database I created for capturing image with PC cam, and viewing it in Access. The code is somewhat messy, you'll have to change the directory and play with it to get it to suit your needs. But the good thing is, you can use change the camera option to the scanner instead. In this database, I first store the image captured with the PC cam in "C:\Documents\Prolink Pictures\" and then move it to "C:\Documents\storePics\" if the user want to save it. I wrote some comment among the codes. If you have any question, PM me.

Niroth

Sorry, can't attach the file, maybe it's too big, here's some code from it:

Public Function ListFiles(strPath As String)

On Error GoTo Err_Handler
    
    Dim colDirList As New Collection
    Dim varItem As Variant
    Dim strTemp As String
    
    strTemp = Dir("C:\Documents\Prolink Pictures\") 'directory for captured pictures

    Do While strTemp <> vbNullString 'this section of code get all the paths to the picture files
        colDirList.Add strTemp
        strTemp = Dir
    Loop
    
        For Each varItem In colDirList
        DoCmd.GoToRecord acDataForm, "capture", acNewRec 'play with this section to make it do what you want. I choose to add the path to "image" table
        Forms!capture!path = strPath & varItem
        DoCmd.RunCommand acCmdSaveRecord
        Next

Exit_Handler:
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Exit_Handler
End Function

For a button called "captureImage"

Private Sub captureImage_Click()

'change the string to the path for the scanner here
Shell "C:\Program Files\Vimicro\Vimicro USB PC Camera (VC0332)\vmcam\bin\akkord.exe", vbMaximizedFocus

End Sub

"showImage" button

Private Sub showImage_Click()

Call ListFiles("C:\Documents\Prolink Pictures\")
DoCmd.GoToRecord acDataForm, "capture", acFirst

End Sub

so create a form, "capture", which contains a field "path", data to be stored in a table. On the form, add an image object and when access ask for a directory just choose an image from the anywhere. While focus is on the image, click on data tab, and set its control source to the field "path". Then add those buttons, save the function, and change the directories in the codes. If you already have the images scanned in, you don't need the captureImage button.

 

[Max D]

Its hard to write external image storage usually. You'll find some problems again and again. Beside that, its usually not easy for users to manipulate images in such a database.

There is a ActiveX control called AccessImagine, which handles external image storage automatically and makes adding images to database convenient - you can load from file, scan, paste from buffer or drag-n-drop. You can crop image right inside the database and do much hard-for-Access tasks with it.

 

[wdw]

I'm using DBPix and almost satisfied . It used to be the best for such purposes and costs only 99$

 

[Max D]

I know my judges can't be independent because I'm one of the AccessImagine developers. But if you like Ammara DBPix, it will be usefull to look at http://access.bukrek.net to compare.