The company I'm doing a database for wants a picture to come up with each record in an employee records form. It's in single record mode and they cycle through the records by clicking in an EmployeeNo combo box, choosing the employee they want and voila.
What I need is a good method of linking their pictures to files in a subfolder of the folder the database data tables are in.
I'm going to have them take pictures with a digital cam or scan them or whatever they want to do. Since every employee has a unique employee no., I was going to have them rename the picture to [employee no].jpg or .gif, whichever format I decide on, and then whenever the form chose a record, it would open the picture based on the record's employee no and tack on a .jpg/.gif. Seems the most straight-forward way.
However, I have also contemplated having an open file dialog box come up, they choose the location of the file and then store that in a picture location field and then have code to put that location in the picture source property of that picture control. That way it could be ANY file ANYwhere and I wouldn't have to worry about format. Of course, there's ways to get around the file extension in the first way I described (if there's an error with .gif then try .jpg, but anyways, not my point).
What I'm asking for is some input on which way would be the best way to go for a split database, where the front-end is on each PC and the back-end is on the server. I see the problem with picking which one they want because, without understanding, they could pick one on their own machine and then that would prevent others from seeing it and probably inducing an error. But, all in all, just some input would be nice and I'd be surely grateful for it.
Also, any suggestions about which control to use for the picture and what to make sure of (like having a proper OLE server for .gif and .jpg files). Thanks a million, guys and gals!
How exactly would you store the location in a table? Do you mean just the location of the pictures' directory as one record in a table? Or each employee in the table have a field for its location? I'm not thinking the latter makes sense because if you change the location, it'd have to go through every record in the table to change the location.
Well, you can store the "location" as a text value in the data table. Have some code read the text value from that field (hidden on the form) and check to see if the file is found (using the DIR command). If the file is found, set it as the Source of an image control. You're right, though, about having to update it.
You could have a global setitng for the directory and only the filename varies. You could have a global location and use the employee ID as the name like you first mentioned.
There is also a method I developed that brings up a dialog box to select a file, verifies the format and filesize and then saves the image as binary in a data table. When the record is requested, it writes the binary data to a file in the user's temp directory and then links an image control to it.
Unlike storing the images as OLE objects, which can be very slow and consume a large ratio of space (as much as 5 to 1) in the backend database, this method stores the files at 1 to 1 and is very fast for small to medium sized files.
This allows the user to supply the file from anywhere, ensures the files are available to all users, secures the files from deletion and alteration, allows control over the size and format and makes it so that you don't have to fiddle with OLE servers.
Storing the data within the back-end obviously makes the database larger. At 1:1, that's not a big problem since that space would be taken up on the hard drive anyway. However, how would this affect queries and overall speed of the program dealing with that table?
The raw binary data should be stored in a seperate table so that it doesn't get dragged through queries - therefore the performce would be unaffected.
My last client wanted to clearly see how much space images were taking up, so I placed the images table in a seperate backend and linked to that table. This probably slightly improves the performance by removing bulk from the primary back end.
So far, it's running quite smoothly - we limited the image size to 250kb - which is plenty for a small, clear JPG. I have a label become visible that states "Please wait while the data is pulled from the server" but it's so fast, you never get to see the label.
It is pretty involved. Getting back to your original question, the quickest way to accomplish this would be to setup the following:
1. Make a subdirectory on the server under the backend database (don’t let the users see it or know about it)
2. Give the user a dialog box to select the file they want
3. Use code to verify size and type
4. In code, copy the file to your directory and rename it with the record key as the filename.
Then, as each record is pulled up:
1. Use the DIR command to see if there is a filename matching the record key. By using an “implicit” reference to the sub-directory, you can move the database – just make sure the sub-directory moves with it.
2. If the file exists, then assign it to the image control. If not, assign a generic image (perhaps a “No Picture” image).