Storing large amounts of pictures (1 Viewer)

Yatiman Idara · Apr 24, 2016

:banghead:

Hi everyone,

I am currently working on a database. There are about 25,000 to 50,000 entries in this database and due to management requirements we need to store a profile picture for each of the entries

Now obviously this presents a difficult problem due to Access's 2gb size limit.

I am exploring some work arounds and wanted people's guidance and any ideas.

At the moment I have two ideas in mind:

1) Linking picture files - Saving all the profile pictures in a separate folder according to the ID number and then simply linking them to the field. That way they don't take up any space in Access as are stored externally. One drawback is that I expect the path to change sometimes so that would mean re-linking all of them everytime I change the network path.

2) Separate DB for pictures - Another method would be to create a separate DB for only containing profile pictures and then link that DB to my frontend. Drawbacks, never done it before and wonder if it will work smoothly with my main backend. Also as the pictures pertain to my main tables but are stored separately, does that mean I need to run my main forms off a query instead of directly from the table?

Thoughts, guidance and ideas are welcome.

Thanks

pbaldy · Apr 24, 2016

I would do 1, storing a path to the picture. Changing the network path could be done with an update query.

Yatiman Idara · Apr 25, 2016

pbaldy said:
I would do 1, storing a path to the picture. Changing the network path could be done with an update query.

Thanks for that. I have decided on going with the linking method especially after you brought my attention to the update query method

Does anyone know a good video tutorial or written tutorial on making a solid method for linking images ????

I looked at a couple of linking method videos and implemented them but only problem was that for a record that doesn't have a valid path, the form displays the image of the previous valid path.

Is this a general issue or specific to me?

Thanks

The_Doc_Man · Apr 25, 2016

An image control has a .Picture property. Load it with the correct fully qualified path and name (probably in a Form_Current routine) and the picture will be there when you need it. The trick isn't displaying a picture. It is FINDING the picture in the first place.

When you say "One drawback is that I expect the path to change sometimes so that would mean re-linking all of them every time I change the network path." ...

Do you mean that people will do copies or renames sometimes so that files will not be consistently located over time? Or were you referring to the idea that the pictures might be mapped based on different drive letters, always with the same path but not always on the same drive-letter?

If it is the latter case, don't use drive letters. Use "\\server\root\level1\level2....\" format and don't worry about changing the path. If it is the former case (actually moving the files to different locations), I cannot offer you any wisdom off-hand.

As to saving the pictures in a folder, don't forget that you can do a file rename using the File System Object or the VBA syntax related to the NAME file1 AS file2 statement (either of which you can find with any good Search engine). If you need to do file copies as well as renames, the File System Object is the way to go.

Also, don't forget that if you have room on the server where the database resides, you can do a CurrentDB.Name to get fully-qualified name AND PATH of the database, and there is a corresponding method for linked back-end tables. In that case, you would use the name of any table and get CurrentDB.TableDefs(table-name).Connect, then take the part of the result that is to the right of the first equals-sign. (I.e. do an InStr() to find the equals sign and then do a Right$() to pick out the right-hand side of the string.) For local tables (i.e. residing in the front-end database segment), the connect string is blank.

You said this related to the size of an Access database, so my comments apply to your table locations. This would NOT be the case for SQL back-end files, which would have a different type of .Connect string.

By the way, you didn't say it, but I would recommend a split DB if you have a place at work where you can store this file and its associated pictures. If you can use the file location string in "\\" format as described earlier, then you can manipulate the path by finding the right-most "\" (see InStrRev function) to split the filename from the rest of the path and adding path elements as needed.

Yatiman Idara · Apr 25, 2016

Hi DocMan,

Thanks for your post offering very enlightening and 'out of the box' method.

Yes I am in the near final stages of my database development and did intend to depoy the split model. As this is my first database development project some of the advice you gave was a bit puzzling

but it did point me in the right direction and I hope to be utilizing some of your ideas very soon.

At the moment I am in the final stages of my image linking enterprise and have run into a problem

:

My question is posted in the following link *(forums/showthread.php?p=1484908&posted=1#post1484908) and any help in solving it would be greatly appreciated.

Thanks once again

Yatiman Idara · Apr 25, 2016

Actually as you suggested, I am considering upgrading to a SQL backend database sometime in the future but I don't want to rush into given my limited DB experience.

A couple of questions.

Can I make an SQL local server (not connected to the internet) ?

What would be the advantage of such a model compared to a split access model?

Thanks

Simon_MT · Apr 25, 2016

SQL Server is great for 100 of thousands of records. I have stored about 30,000 images using 500 x 500 pixel files without any problems. The master images are over 50MB. I did introduce a Image Management System to avoid problems of 1MB or 2MB files being used and Access found these difficult.

Although this may seem convoluted there are different images for different purposes hence similar Functions doing the same thing for the different type of Images.

Code:

Function GetPicturePath()

    With CodeContextObject
        GetPicturePath = GetPictureDir & .[Image File]
    End With
End Function

Function GetPicturePathSmall()
        
    With CodeContextObject
        GetPicturePathSmall = GetPictureDir & .[Image File Small]
    End With
End Function

Function GetPicturePathLarge()
        
    With CodeContextObject
        GetPicturePathLarge = GetPictureDir & .[Image File Large]
    End With
End Function

Function GetPicturePathNew()
        
    With CodeContextObject
        GetPicturePathNew = GetPictureDir & .[Image File New]
    End With
End Function

Function GetPictureExist()

        If Dir(GetPicturePath) <> Empty Then
            GetPictureExist = -1
        Else
            GetPictureExist = 0
        End If
End Function

Function GetPictureExistSmall()

        If Dir(GetPicturePathSmall) <> Empty Then
            GetPictureExistSmall = -1
        Else
            GetPictureExistSmall = 0
        End If
End Function

Function GetPictureExistLarge()

        If Dir(GetPicturePathLarge) <> Empty Then
            GetPictureExistLarge = -1
        Else
            GetPictureExistLarge = 0
        End If
End Function

Function GetPictureExistNew()

        If Dir(GetPicturePathNew) <> Empty Then
            GetPictureExistNew = -1
        Else
            GetPictureExistNew = 0
        End If
End Function

Function GetPictureWidth()

    With CodeContextObject
        GetPictureWidth = CInt(.[ImageControl].ImageWidth / 15)
    End With

End Function

Function GetPictureWidthSmall()

    With CodeContextObject
        GetPictureWidthSmall = CInt(.[ImageControlSmall].ImageWidth / 15)
    End With

End Function

Function GetPictureWidthLarge()

    With CodeContextObject
        GetPictureWidthLarge = CInt(.[ImageControlLarge].ImageWidth / 15)
    End With

End Function

Function GetPictureWidthNew()

    With CodeContextObject
         GetPictureWidthNew = CInt(.[ImageControlNew].ImageWidth / 15)
    End With

End Function

Function GetPictureHeight()

    With CodeContextObject
        GetPictureHeight = CInt(.[ImageControl].ImageHeight / 15)
    End With

End Function

Function GetPictureHeightSmall()

    With CodeContextObject
        GetPictureHeightSmall = CInt(.[ImageControlSmall].ImageHeight / 15)
    End With

End Function

Function GetPictureHeightLarge()

    With CodeContextObject
        GetPictureHeightLarge = CInt(.[ImageControlLarge].ImageHeight / 15)
    End With

End Function

Function GetPictureHeightNew()

    With CodeContextObject
        GetPictureHeightNew = CInt(.[ImageControlNew].ImageHeight / 15)
    End With

End Function

Function GetPictureSize()

Dim fs, F
    
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set F = fs.GetFile(GetPicturePath)
        GetPictureSize = F.Size
        Set F = Nothing
        Set fs = Nothing

End Function

Function GetPicture()

    With CodeContextObject

        If GetPictureExist = True Then
            .[ImageControl].Visible = True
            .[ImageControl].Picture = GetPicturePath
        Else
            .[ImageControl].Visible = False
        End If
    End With

End Function

Function GetPictureSmall()

    With CodeContextObject
            
        If GetPictureExistSmall = True Then
            .[ImageControlSmall].Visible = True
            .[ImageControlSmall].Picture = GetPicturePathSmall
        Else
            .[ImageControlSmall].Visible = False
        End If
    End With

End Function

Function GetPictureLarge()

    With CodeContextObject
            
        If GetPictureExistLarge = True Then
            .[ImageControlLarge].Visible = True
            .[ImageControlLarge].Picture = GetPicturePathLarge
        Else
            .[ImageControlLarge].Visible = False
        End If
    End With

End Function

Function GetPictureNew()

    With CodeContextObject
            
        If GetPictureExistNew = True Then
            .[ImageControlNew].Visible = True
            .[ImageControlNew].Picture = GetPicturePathNew
        Else
            .[ImageControlNew].Visible = False
        End If
    End With

End Function

Function SetPicture()

    With CodeContextObject
        If GetPictureExist = True Then
            If .[Image Width] <> GetPictureWidth Or .[Image Height] <> GetPictureHeight Then
                .[Image Width] = GetPictureWidth
                .[Image Height] = GetPictureHeight
            End If
        Else
            .[Image Width] = 0
            .[Image Height] = 0
        End If
    End With

End Function

Function SetPictureSmall()

    With CodeContextObject
        If GetPictureExistSmall = True Then
            If .[Image Small Width] <> GetPictureWidthSmall Or .[Image Small Height] <> GetPictureHeightSmall Then
                .[Image Small Width] = GetPictureWidthSmall
                .[Image Small Height] = GetPictureHeightSmall
            End If
        Else
                .[Image Small Width] = 0
                .[Image Small Height] = 0
        End If
    End With

End Function

Function SetPictureLarge()

    With CodeContextObject
        If GetPictureExistLarge = True Then
            If .[Image Large Width] <> GetPictureWidthLarge Or .[Image Large Height] <> GetPictureHeightLarge Then
                .[Image Large Width] = GetPictureWidthLarge
                .[Image Large Height] = GetPictureHeightLarge
            End If
        Else
                .[Image Large Width] = 0
                .[Image Large Height] = 0
        End If
    End With

End Function

Function SetPictureNew()

    With CodeContextObject
        If GetPictureExistNew = True Then
            If .[New Width] <> GetPictureWidthNew Or .[New Height] <> GetPictureHeightNew Then
                .[New Width] = GetPictureWidthNew
                .[New Height] = GetPictureHeightNew
            End If
        Else
            .[New Width] = 0
            .[New Height] = 0
        End If
    End With

End Function

Function SetPictureClear()

    With CodeContextObject
        .[ImageControl].Visible = False
    End With

End Function

Function SetPictureClearLarge()

    With CodeContextObject
        .[ImageControlLarge].Visible = False
    End With

End Function

Function SetPictureSize()

    With CodeContextObject
        If GetPictureExist = True Then
            .[Image Size] = GetPictureSize
        End If
    End With
End Function

Function SetPictureFlag()

    With CodeContextObject
              
        If GetPictureExist = False And .[Image] = -1 Then
            .[Image] = 0
        ElseIf GetPictureExist = True And .[Image] = 0 Then
            .[Image] = -1
        End If
    End With

End Function

Function SetPictureWeb()

    With CodeContextObject
        If GetPictureExist = False Then
            .[Web] = "X"
        ElseIf GetPictureExist = True And .[Web] = "X" Then
            .[Web] = "P"
        End If
    End With
End Function

Function SetPictureOther()

    Call GetPicture
    Call SetPicture

End Function

Function GetPictureDir() As String

    GetPictureDir = Forms![Menu]![Image Directory]

End Function

Simon

Yatiman Idara · Apr 26, 2016

Hi everyone,

First of all thank you Simon for providing that info in SQL and the differential image function code.

I was thinking about what DOCMAN said regarding having two separate fields for the image display. One containing the folder path and the other for individual images. I think that set up would be very useful in my situation.

Would anyone here happen to know how I can set this up?

Thanks

arnelgp · Apr 26, 2016

attachment field is a candidate.

Simon_MT · Apr 26, 2016

Don't attach just use the Image Control.

Simon

Yatiman Idara · Apr 26, 2016

arnelgp said:
attachment field is a candidate.

Thanks for that.

Our current deployment does utilize the attachment field but unfortunately as the record/entries are quite large (25,000 to 50,000 entries) this made the DB large and cumbersome. Hence my search into the image linkage method.

arnelgp · Apr 26, 2016

you dont need linkage, on form or report when you use attachment.
just drag an attachment control and set its recordsource to the field on your table.

Yatiman Idara · Apr 26, 2016

Simon_MT said:
Don't attach just use the Image Control.

Simon

Yes I decided to use the Image Control and it works perfect.

At the moment my Image path is: \\server\foldername\imagename.bmp

I am currently using the entries ID number as the image name. For example in the first entry the path is something like: D:\DB Folder\Images\1.bmp

I was thinking of breaking up my path into two fields. One would contain the path i.e. \\server\DB Folder\Images\Orphan Images\

and the other will contain the file names i.e. 1.bmp

This way I can easily update the first path field without much hassle.

My DB is in final phases and I expect to set up a local network in the deployment stage and hence I feel the need to set up an easy method for image path updates.

Any thoughts guys?

Simon_MT · Apr 26, 2016

NEVER EVER use bitmaps. 60% Jpegs are preferred although pngs can be used.

Bitmaps are just rubbish as there footprint is astronomical.

Simon

Yatiman Idara · Apr 26, 2016

Simon_MT said:
NEVER EVER use bitmaps. 60% Jpegs are preferred although pngs can be used.

Bitmaps are just rubbish as there footprint is astronomical.

Simon

Oh really. I thought it way the other way around.

Access always seems to suggest to use bitmap formats.

Also a saw an article that argued that bitmap and DIB (I think it was called) had the smallest footprint since they were windows own versions or something.

Guess I will change them back to jpg...

Simon_MT · Apr 26, 2016

Here are some examples: The King of Hearts

jpg: 126Kb
png: 273Kb
bmp: 1,030Kb

All the same Size all produced in Photoshop. I can't load the png or bmp because of upload file restrictions. Fair enough.

Simon

Yatiman Idara · Apr 26, 2016

Simon_MT said:
Here are some examples: The King of Hearts

jpg: 126Kb
png: 273Kb
bmp: 1,030Kb

All the same Size all produced in Photoshop. I can't load the png or bmp because of upload file restrictions. Fair enough.

Simon

Wow that's a huge difference between bmp and the other two.

Guess will just have to stick to jpg then

The_Doc_Man · Apr 28, 2016

The .JPG format gets its benefits from a little trick... the JPG specification allows for "pixel compression" via polygons. If you have a typical employee picture, it is a person's shoulders, neck, and head on a constant-color background. That background can be specified as though several thousand actual pixels in a box merged to form a SINGLE HUGE pixel that only needs box size, position, and the color that would be applied to all pixels within the box. The typical employee ID picture is probably 40% or more compressed on that trick alone.

As to the number of records: I am currently running a "pure" Access split FE/BE database for the U.S. Navy that tracks 600,000 work items and another 300,000 audit log entries. A mere 50,000 records is less than what I get added each month, and I have to hope I can get around to archiving that information often enough to do some good. If you have speed issues for your 50,000 records, I might suggest looking to setting up indexes for the fields you track most often.

If you have a prime key on a table, it automatically has an index for that, but you can define 9 more indexes. It might be wasteful to declare a full 10 indexes on each table that you have, but two or three commonly used fields might benefit from being indexed, particularly if you have reports that frequently sort based on those fields.

Due to work issues, I am currently in a mode where I can't offer a lot of time on problems, but I can still offer suggestions that might lead you to do web searches via your favorite search engine. You are getting advice from others so I know you are in good hands right now.

Simon_MT · Apr 29, 2016

Doc Man,

I have a file that increments by 25,000 per month.

I have been implementing Sql Server with an Access Front End.

The upsizing was pretty good obviously there were issues but no show stoppers.

I'm very tempted to put all the files into SQL Server and be done with it.

Simon

The_Doc_Man · Apr 29, 2016

If I were allowed to stand up SQL server on my own, I might have done so a couple of years ago, but working with the U.S. Government...

First, you have to jump through a LOT of hoops.

Second, they never tell you ahead of time when they are going to ignite the hoops.

Third, I don't thing that my old beat-up, falling-apart body looks that good in the circus tights any more.

Storing large amounts of pictures (1 Viewer)

Registered User.

Wino Moderator

Registered User.

Immoderate Moderator

Registered User.

Registered User.

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

Registered User.

Immoderate Moderator

Registered User.

Immoderate Moderator

Similar threads

Users who are viewing this thread