Start Free Trial

asked on

Working with images from a network

Hi, I have an Access 2002 database that I am trying to add an Image Library to. The images are jpg's, pdf's or gif's...no bmp's. I do not want to store them in my database, I simply want to give the user access to the images that are already stored on our company's network drive and the ability to see the image and possibly print the image.

As background info, I have a table that houses specific information about the image such as a response code field. The image is named as the response code - Description. (i.e. - my table might have the response code field as being "T81" and the image on the network is called "T81 - Something Something".

I'm thinking that there are two ways to go about this...one - only displaying the images of the response codes that exist on the other table (which is very limiting and many images may not exist on the network for these) or just keeping my Image Library as it's own little application on my database in which it displays each image on the network, which can get very large (at least 200 images there now). I think for this to work, I would have to be able to cut down the title of the image based on just the response code part of the naming convention and allow users to search based on that response code.

Which would be the best way to go and should I use the linked image object or the OLE object?

Greetings Mia143!

The unbound image control, when given the proper path and name of a file can be very flashy (not to be confused with actual Flash)
Do the files use naming conventions to identify content or usages?

Lists of files in a folder can be obtained using api calls and class objects like the file browser control

As long as the thing is organized properly, you can work magic with this.

regards
:)-j-

Scott McDaniel (EE MVE )

Generally speaking it's not a good idea to store Images in your db .. a much better method is to store the path to the image in a Text field, and then use the Image object to view the image ... the Image control exposes the .Picture property, which you can set like this:

Me.YourImageControl.Picture = YourImgePathField

You would normally do this in the Current event of your form ... or perhaps on a button click, if the image isn't needed all the time.

ASKER CERTIFIED SOLUTION

shanesuebsahakarn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

As an alternative to OLE objects, you could look into using BLOBs (Binary Long Objects).
These are much smaller and can be stored in your database.
They obviously have a size attached to them - but this is nowhere near the size of storing bmps or jpegs in your app.

ASKER

JadeData,

"The unbound image control, when given the proper path and name of a file can be very flashy (not to be confused with actual Flash)
Do the files use naming conventions to identify content or usages?

Lists of files in a folder can be obtained using api calls and class objects like the file browser control

As long as the thing is organized properly, you can work magic with this."

You give an interesting edge on your reply - these files are in a folder on the network and the naming convention is the same, basically, once placed there, they should not change....but more are added each week that would have to be updated to the DB Image Library. A list would be great in the Image Library, but how would I go about it and update it?

As a note, I'm checking out the other options offered by LMS and Shanes as well...you guys are all so fast..*L*

shanesuebsahakarn

Well, the thing to do is to scan the folder say, on a daily basis and add the files to the table if they don't already exist.

The Dir() command can be used to retrieve the names of files in a folder - you'd just have to scan it and check to see if the the filename is already in the table.

Alternatively, do you actually need these image names stored in a table? If you are wanting to display them at a user's request (for example, if they are linked to a record), you could just check to see if they exist when you need them and then set the Image control's picture as and when you want to show them.

Presuming that the path and name of the images are stored, you can inspect the folders with the FSO (File Scripting Object) or my other commands like
*** air code ***
set currentfiles = db.openrecordset("tFiles",dbopendynaset)
xString = dir$(Path & FileName)
do while len(xString)>0
currentfiles.findfirst "([Path] = '" & sPathExpression & "' and ([FileName]='" & sFileNameExpression & "')")"
if currentfiles.nomatch then
'add the newly found file to your table now.
endif
loop

ASKER

Ok, so many options here....Danny I think I read a little about BLOBs on another solution earlier and that one is viable as well.

Let's look at it this way at square 1. I'm adding an item to my database Record management switchboard for an Image Library. These images will not always match my database table for record management, therefore I do not want to really link them. I think it might be better to have this Image Library contain just links to the network folder that houses the images. A user can browse the Image Library Links by title to determine which image they want to display and possibly print from an image viewer. I'd prefer not to store the actual image in the library. (Note: A user could actually do the same thing on our network drive at the folder level, but it would be easier for them to use this database to get to the image.)

The Image Library, when initiated, will need to browse the network folder to look for any additions prior to displaying all of the links. Should I build a table to house the links and just update each time the table is opened and what do I need to do to allow the user to display the image in an image viewer?

Be gentle, I'm now well versed in VB or other areas of programming.

ASKER

oops, edit....not well versed in VB....

shanesuebsahakarn

I'd update the table each time the form is opened (note - you should build a form to do this). Have a look at the link I gave you - it contains instructions on how to display the image in a viewer which you can create yourself as a new form. Alternatively, you could launch the image in its default application using a short snippet of API code.

Build Table and plan operation to update when it is strategically needed.
Long data harvesting operations are an annoyance when the user would be satisfied with just what is already there.

You need to draft up a plan of these operations first before you begin building anything. You can begin your education in VB(A) by writing details instructions that are NOT in VBA on how you want the operation to function INCLUDING branch points (ifs and other conditionals)

You humans have a nasty habit of considering binary operations simple, when in fact they are quite complex.

I'm with Jadedata... if you fail plan, you will plan to fail.

Building the db is the easier part - but changing a running system is a big no no and can only lead to bigger probs.

There are a few ways that you can do what you require - but it depends on how much maintainence you plan to do and how much space you have to store the db.

1. Store the images on a drive ad have an image box link to the record.
Benefits:
Images are external, so smaller size.
If nomanclature is similar, is easy to maintain.
Can be controled by the user from a pop-up dialog box.
Disadvantages:
Images are external, so requires maintainence in two areas.
If names are changed (including drives), will cause problems in the db.

2. Store as BLOBs in the db.
Benefits:
Internal - everything under one roof.
Space saving - smaller than OLEs.
No linking to external sources - database can be portable.
Disavantages:
Can still bloat a db
Require maintainence everytime a new image is required (needs to be converted to a BLOB).

You need to weigh up the pros and cons.
External is easier, BLOBs are more professional
However, at the end of the day, if one works better for you than the other, there is no wrong choice.

:-)

shanesuebsahakarn

> BLOBs are more professional

In what way?

ASKER

To Danny's point, this DB is going on the network, so space is not a problem, but if I run into it using a lot of resouces to update it, someone might come knocking on my door from IT. I really do not want to have to perform much maintenance after this DB is loaded for pulling in these images and really don't want to have to manually enter these link addresses for each image. I would want to find a way to automate that table grabbing the info from the network folder.

To Shane's point, I have created the ImageTable and put in the path with an image property. I changed the on Current and After update properties for the form, but can I get the table to automatically update the path information and add records to the table each time the form is called for display?

Hope I haven't started any arguments here...<smile>

>In what way?

I personally believe that keeping everything within the db is the best method.

I keep security in house, mimic external applications where possible and try to make a db as portable as possible.
Also, (in this instance) by using BLOBs, the database speed would not be affected by network performance - this gives the impression of a faster and more efficient app.

However, I also realise that one way is not perfect for everyone. For example, many people (including yourself, I understand) believe that Access has security for a reason and that it should be used.

Likewise, the difference between linking across a network to a file location, or using an internal file for an image will have arguements for and against also.

:-)

Mia - to be honest, Shane knows far more about Access than any other person in this room. :-)

However, the only right answer is the one that gets the job done without annoying the IT bods..
:-)

ASKER

Hey Jade, I get your point and hey ...I'm only human...such a fallacy <smile> However, I'm really not going to overcomplicate this thing with a bunch of If statements because this is for a Marketing group...believe me, they overcomplicate things enough everyday.

Basically, they want an easy way to get to these images and I'm just tagging this little Image Library into a Record Center Management Database for them, simply because they forget where on the network the folder resides. I could probably make it all roses for them by allowing them to search by title, description...response code...etc, but the name of each of the images should be self-explanatory for them enough. I'm sure they will probably all suggest it, but my database was not built for this....that is a convenience measure and really another database, this is just the nice way I could incorporate their forgetfulness.

shanesuebsahakarn

Mia143,

You can run some code like this to insert images into the table. You'd need to set the Index in the image field to "Yes (no duplicates)" to prevent images from being added to your table twice:

Dim strFile As String
Dim dbs As Database

Set dbs=CurrentDb()
strFile=Dir("C:\MyFolder\*.bmp")
While strFile<>""
dbs.Execute "INSERT INTO ImageTable (MyField) VALUES('" & strFile & "')"
strFile=Dir()
Wend

This will insert any images into the table. I'd run it when the form opens but you'll also need to requery the form after the code finishes, i.e.
Me.Requery

I think that should do it for you. The performance of Dir() is actually quite good, even over a network.

Told you he knew a lot... :-)

shanesuebsahakarn

Danny,

My main objection is to the term "professional" - each solution has pros and cons, and I wouldn't want to give people the impression that one or another way is the "right" or "proper" way to do it. You have to weigh your requirements and decide which one is most appropriate. For example, BLOBs don't export or link well via ODBC to some databases but equally, you have to remember to copy the pictures as well if you move your database.

As far as Access security is concerned, I don't believe that it is any kind of robust security at all. It's great so long as you understand its limitations, so I tend to build my own security functions in rather than use the in-built system. Access security is absolutely no good for protecting data, not if someone is determined. If data confidentiality is required, a more robust backend is needed.

There are plenty of people in this forum who have at least as much experience as I do - I just post more :)

Shane.

Mia143:
In MSAccess, no matter what kind of image you may store, they are managed as UNCOMPRESSED images. There is a HUGE storage difference between a file stored JPEG and a DB store JPEG.
Settings, (the who, what and where) of this filing system will be part of your database storage. A very simple table can contain these one or more locations. The reason databases were invented was because you humans don't want to have to remember anything.

danny:
Disavantages:
Can still bloat a db
Correction:
Will absolutely bloat a db

I would only strongly dispute the part about Shane knowing far more about Access than "anybody" in the room. I think I might know one or two things myself after 12 years (20 in databases, 12 in Access). Shane and I both have the Eagle Scout badge for MSAccess.

shanesuebsahakarn

Actually - the IT bods are the ones you can get away with annoying, so long as the MD is happy....

IT administrators often forget that they're supposed to be serving the needs of the *users*, rather than running their own little network empire...

shanesuebsahakarn

I dunno about having the Eagle Scout badge - I still learn new things about Access every day!

In his reference to still bloating the db, I think Danny is referring to BLOBs (long binary objects), in which you essentially store a byte-by-byte representation of the original file, rather than the uncompressed version+overhead that OLE fields use. The increase in file size isn't hugely more than the original filesize, but I'm still not convinced about the benefits of BLOBs - at least, not in an Access environment.

ASKER

Hi Shane,

Why would I not want to set a field on my table for Image and make it a hyperlink, then set the property for Image on my form to Picture Type "Linked"? Just wondering as I noticed this as an option in the properties.

Guys, guys, guys...
Apologies for stating that Shane has more knowledge than anyone else in the room - although he certainly has more experience than I!.
Cap - You've always been fantastic - please don't take my (misguided) comment personally.

As for Shanes comment:

<<In his reference to still bloating the db, I think Danny is referring to BLOBs>>
He is correct.
BLOBs do still bloat the db, although, if you must keep images in teh db, they are smaller than having a form with an embedded image on.

In hindsight, I think that the best solution would be to use a combination of Shanes and another solution:
Link to external files, with the file location names stored in a table.

List the file names in a combo box and use a dialog box if an error occurs in the code (file not found).
That way, you can solve the forgetful nature of your users (to a degree), but still have the flexibility for a user to fetch their own file.

:-)

Sorry again guys.. :-)

shanesuebsahakarn

The main issue is that a hyperlink only launches an image - you can't use it to automatically display one in an Image control (since they can't be bound to fields). Hyperlinks are very useful though to allow your user to launch the actual image file although I tend to use API code for, so that the image opens in the default application instead of Internet Exploder.

You can set the Image control's HyperlinkAddress property to the image path though so that the user can both look at the picture in your form and launch it as well by clicking on it.

ASKER

>Dim strFile As String
Dim dbs As Database

Set dbs=CurrentDb()
strFile=Dir("C:\MyFolder\*.bmp")
While strFile<>""
dbs.Execute "INSERT INTO ImageTable (MyField) VALUES('" & strFile & "')"
strFile=Dir()
Wend

One problem, it doesn't like the DIM dbs as Database. I changed the strFile value for where to look for the file and changed the MyField value to ImagePath to place the value in that field location. What am I missing?

shanesuebsahakarn

Ah - if you're using Access 2000 or later, it doesn't have DAO (which the above code uses) referenced by default. While in a code module, click Tools->References and make sure Microsoft DAO 3.6 is checked. You should also change the line to:

Dim dbs As DAO.Recordset

just to be 100% sure :)

ASKER

> Hyperlinks are very useful though to allow your user to launch the actual image file although I tend to use API code for, so that the image opens in the default application instead of Internet Exploder.

I think I like that solution better just for the fact that I would rather not use my DB to store a copy of the image. Another thing is that the images can be jpg's, pdf's (mostly) and gif's. This way I wouldn't be storing the image and would rather launch another application for them to view the image and print from it. No one should be able to manipulate the image except for our Table Maintenance staff anyway.

What API would you use and how do I incorporate that?

shanesuebsahakarn

This is the code I use to launch files:

Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal Hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Add that into a new module, and you can use it in this way:

ShellExecute hwnd,"open",strFile,Chr$(0),strPath,False

strFile is the file name (without the path) and strPath is the path to the file, both passed as strings.

ASKER

> make sure Microsoft DAO 3.6 is checked. You should also change the line to:

Dim dbs As DAO.Recordset

It was unchecked, checked it and changed the code....having a little problem with dbs.Execute now though.

http://www.mvps.org/access/api/api0004.htm (shell and wait for a file "can opener") Uses the windows default application for registered file type.

The code there can be dropped into an app for running.

shanesuebsahakarn

What error are you getting with dbs.Execute?

ASKER

Compile error:

Method or data member not found

ASKER

Eeeks, that API option is looking very ugly for someone very inexperienced like me.....maybe stick to what I am currently doing after all....

shanesuebsahakarn

It's actually quite straightforwards to use - you just have to make sure you pass the file and path to it, and Windows will take care of the rest :)

ASKER

Well, I'm wondering if creating the module for it in VB (that is where I have to create it correct?) will be a problem with my network gurus. Our VB runs over the network so that is probably going to be a red tape IT request to work it in. I can do it here at home to test it and make sure it works....but work is another story.

shanesuebsahakarn

No, you'd do it in the Modules page of your database - that's where you put functions that aren't necessarily associated with forms. Access/Office VB (known as VBA) is almost as functional as "real" VB (more so in some cases!). Just create a new module, paste the declaration into it and you can then use it anywhere in your database.

All the code you've written so far for this thread uses VBA :)

ASKER

Ah..duh...got it. I've written APICallImage as a module to the Access DB module. Still having a problem with the compile error for dbs.Execute. I'm still going to need that to pull the image names from the network folder right?

shanesuebsahakarn

Yes, although what that particular line does is add the data into the table. Can you paste the line as you have it in your database, so we can troubleshoot it?

ASKER

sure thing,

Private Sub Form_Open(Cancel As Integer)
Dim strFile As String
Dim dbs As DAO.Recordset

Set dbs = CurrentDb()
strFile = Dir("C:\Market\DMG\BRC Keyer Info\BRC Image Library\*.jpg")
While strFile <> ""
dbs.Execute "INSERT INTO ImageTable (ImagePath) VALUES('" & strFile & "')"
strFile = Dir()

shanesuebsahakarn

D'oh!

The Dim line should read:
Dim dbs As DAO.Database

not DAO.Recordset!!! My bad, as the Americans say :-)

ASKER

Good deal! It's starting to come together now. It has written the 4 records I had for sample data to the table and they are showing up in the form. I think I just need to do some cleanup now.

The image isn't changing, but since I'm not using the image field anymore due to the new module, I should probably just delete that field right?

shanesuebsahakarn

Yes, you probably need to do that since the control won't be able to handle things like PDF files. You could just set the control to display the image if it's a gif, jpg or bmp though, and be blank otherwise.

ASKER

Eeep!! Uh-Oh....I guess I might need to go back to adding a copy of the image then....this is horrible...but I would say that almost 90% of the images in that network folder are pdf's. They are manipulated in Adobe by the Table Maintenance person and edited then a final copy of the image is placed in that network folder I'm grabbing images from.

shanesuebsahakarn

In that case, I'd use the API code to open the PDF file in the default application when the user clicks a button. While there is a PDF viewer ActiveX control, I haven't used it personally so I have no idea how easy it is to use.

ASKER

By default application you are referring to Adobe correct?

shanesuebsahakarn

Yes, Adobe or Acrobat Viewer (whichever one they have on their PC) - sorry for the late reply.

ASKER

Not a problem, I went ahead and awarded the points. I've been playing around with it some more and sort of went back to the old way of just storing a copy of the image....but the image stays constant with each new record...even though the table has updated the new file location. Went by that tutorial for Northwind in the link that was supplied earlier. Not sure how to get the image to update.

shanesuebsahakarn

You basically set the image's Picture property in the OnCurrent event of the form. Something like this:

Me!MyImageBox.Picture=Me!txtImagePath

ASKER

I think the problem comes in when I add the code to update the Image Table, the images were changing as long as I manually typed in the complete path for the image on the table. When I used the code for the DIM statement on the form open action, it only grabs the image name instead of the path and displays just the image name in the ImagePath text box on the Image Form.

This is all of the code that I am using for the Image form:

Private Sub Form_AfterUpdate()
On Error Resume Next
Me![ImageFrame].Picture = Me![ImagePath]

End Sub

Private Sub Form_Current()

On Error Resume Next
Me![ImageFrame].Picture = Me![ImagePath]
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim strFile As String
Dim dbs As DAO.Database

Set dbs = CurrentDb()
strFile = Dir("C:\Market\DMG\BRC Keyer Info\BRC Image Library\*.jpg")
While strFile <> ""
dbs.Execute "INSERT INTO tblImageTable (ImagePath) VALUES('" & strFile & "')"
strFile = Dir()
Wend
End Sub

Up until that form open action, I'm doing good, but the table is not getting updated for any new images and I have to manually add each image...could get tedious with 200 images on the network folder. Any suggestions....ading on some points for this one.

shanesuebsahakarn

Ah ok, you need append the file path as well, sorry - an oversight on my part, I forgot that Dir only returns a filename not a full path:

dbs.Execute "INSERT INTO tblImageTable (ImagePath) VALUES('C:\Market\DMG\BRC Keyer Info\BRC Image Library\" & strFile & "')"

ASKER

Wow, is this what they mean by running really slow? I only have nine images out there that I am testing with, but the DB seems a bit "frozen".

Also, since I will be dealing with mainly pdf documents, but do have some jpgs, gifs and possibly some bmps, is it feasible to write out the strFile as:
strFile = Dir("C:\Market\DMG\BRC Keyer Info\BRC Image Library\*.jpg")
strFile = Dir("C:\Market\DMG\BRC Keyer Info\BRC Image Library\*.bmp")
strFile = Dir("C:\Market\DMG\BRC Keyer Info\BRC Image Library\*.gif")
strFile = Dir("C:\Market\DMG\BRC Keyer Info\BRC Image Library\*.pdf")

or can I specify it as:
strFile = Dir("C:\Market\DMG\BRC Keyer Info\BRC Image Library\*.*")
since I want any image as long as it isn't duplicate - note, my index is set to Yes (no duplicates) on the ImagePath.

shanesuebsahakarn

Whereabouts is it being slow? Is it when you are opening the form?

If you just want any files in the folder, you can certainly use *.*. There are also a couple of other minor modifications to the code that you can make to speed up entry into the table if that's the part that's causing the slowness although I have a similar function that scans for something like several hundred files, and it works almost instantaneously.

ASKER

Yes, the form will not open at all now and I can't get Access to respond. Have to do that task manager thing to end it.

ASKER

Interesting, I went into the VBA code and put in the old code for the dbs.Execute and then just added the path name as you specified in your code, it seems to working now.

Only thing that I noticed now is that I added that strFile for pdf docs, it gave me the path name on the test image, but kept the previous image that was a jpg. In other words, it did not display the pdf image in the image area of the form. Of coures there is nothing executing the launch of Acrobat Reader either. Do I have to convert the image from pdf to show up on the form?

shanesuebsahakarn

OK, first of all compact your database. Change the code to this:

Private Sub Form_Open(Cancel As Integer)
Dim strFile As String
Dim rst As DAO.Recordset

Set rst.OpenRecordset("tblImageTable")
strFile = Dir("C:\Market\DMG\BRC Keyer Info\BRC Image Library\*.*")
While strFile <> ""
rst.FindFirst "[ImagePath]='" & C:\Market\DMG\BRC Keyer Info\BRC Image Library\" & strFile &"'"
If rst.NoMatch Then
rst.AddNew
rst!ImagePath="C:\Market\DMG\BRC Keyer Info\BRC Image Library\" & strFile
rst.Update
End If
strFile = Dir()
Wend
rst.Close
Set rst=Nothing
End Sub

Is that any better?

shanesuebsahakarn

OK, ignore my last post about changing the code :)

Yes, you'll have to convert the pdf if you want it to show in the image control - that control is not able to handle PDF files.

ASKER

Oh the fun, oh the joy. Noting a policy change coming up for Table Maintenance then when they add these files to the folder. They take the pdf image, cut it down, mark it up and then save it to the folder. I think that I may want to deal with the conversion there then at the folder level to make it easier than having Access convert it.