Picture links broken by moving accdb

[sqrepants]

Hi guys.

I have a MS access db with a report that displays a list of students with a pic of their face on the side.
The pics are linked to a folder thats inside the folder where the .accdb file is stored.

The accdb is often moved around, but when it does, all the links become broken and the images are no longer displayed on the list.

How can i adjust the link path so that regardless of where the accdb is moved, the links remain valid?

Keep in mind im using ms2007 and also its not a networked area but rather a bunch of standalone computers.

 

[JHB]

How is the picture linked - path in a table or?

 

[sqrepants]

its the actual file path stored as a text on a table

 

[JHB]

Can't you only store the picture name in the table?
Else post the database with some sample data, (zip it).

 

[nanscombe]

Could you store a relative path, then programatically add a root?

Example 1:
Root - "C:\Docs\"
RelativePath - "2013\201307\20130709_001.jpg"

Root & RelativePath = Full path
"C:\Docs\" & "2013\201307\20130709_001.jpg" = "C:\Docs\2013\201307\20130709_001.jpg"

Move the database to a 'G' drive

Example 2:
Root - "G:\pix\"
RelativePath - "2013\201307\20130709_001.jpg" (The same)

Root & RelativePath = Full path
"G:\pix\" & "2013\201307\20130709_001.jpg" = "G:\pix\2013\201307\20130709_001.jpg"

 

[sqrepants]

Can't you only store the picture name in the table?

How exactly can I store the picture name instead of the link?

 

[sqrepants]

Could you store a relative path, then programatically add a root?

Example 1:
Root - "C:\Docs\"
RelativePath - "2013\201307\20130709_001.jpg"

Root & RelativePath = Full path
"C:\Docs\" & "2013\201307\20130709_001.jpg" = "C:\Docs\2013\201307\20130709_001.jpg"

Move the database to a 'G' drive

Example 2:
Root - "G:\pix\"
RelativePath - "2013\201307\20130709_001.jpg" (The same)

Root & RelativePath = Full path
"G:\pix\" & "2013\201307\20130709_001.jpg" = "G:\pix\2013\201307\20130709_001.jpg"

I will give this a try.
My only question would be how am i going to get the root to be processed and placed on the form.

would i have to create a module and do something like ?getDir() +"\2013\..."

 

[Mihail]

This should be enough for you.

 

[nanscombe]

I would use a function like this ...

Public Function fullPath (byval fpRelativePath as string)
[COLOR="Red"]' A static string will remember the information each time the routine is called[/COLOR]
  Static strRoot as String

[COLOR="red"]' If strRoot has no value then go and get it[/COLOR]
  If Len(strRoot & vbNullString) = 0 Then

[COLOR="red"]' Finds value in a field [B]pictureRoot[/B] from table [B]tblPictureRoot[/B][/COLOR]
    strRoot = DLookup("pictureRoot","tblPictureRoot")

[COLOR="red"]' If there is no "\" at the end of the path then add one[/COLOR]
    If Right(strRoot, 1) <> "\" then strRoot = strRoot & "\"

  End If

[COLOR="red"]' Concatenate the relative path onto the root[/COLOR]
  fullPath = strRoot & fpRelativePath
End Function

A cleaner version of the code above

Public Function fullPath (byval fpRelativePath as string)
  Static strRoot as String

  If Len(strRoot & vbNullString) = 0 Then

    strRoot = DLookup("pictureRoot","tblPictureRoot")
    If Right(strRoot, 1) <> "\" then strRoot = strRoot & "\"

  End If

  fullPath = strRoot & fpRelativePath
End Function

You could just use a line like strRoot = "c:\myRoot\" but if you ever needed to change it you would have to go in and edit the code instead of simply changing a field in a table.

 

[sqrepants]

Hi guys. With some of your help i was able to figure this out. im posting this here for the sake of the community and also if you have any suggestions on how to make this code more efficient, feel free.

the problem was that the access file when moved around lost the links to the images (the images were stored as text data which linked to a jpg file in a directory)

when the access file moved to a flash drive, the links were invalid because the "Student Pictures" folder with the jpgs still contained a path to the users C:/ drive

now, with this code the paths can be amended. this is going to end up as a autoexec.

Private Sub findDir_Click()

Dim picRst As DAO.Recordset
Dim strSQL As String
Dim strVari As String
Dim backSlashPos As Integer
Dim oldDirString As String
Dim newDirString As String
Dim newStrVari As String

On Error GoTo ErrorHandler
    
strSQL = "Select fakeDir from tbl1"
Set picRst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

If picRst.EOF And picRst.BOF Then
Else
    Do While Not picRst.EOF
        For i = 0 To picRst.Fields.Count - 1
            strVari = picRst.Fields(i)
            backSlashPos = InStrRev(strVari, "\")
            oldDirString = Left(strVari, backSlashPos)
            newDirString = CurrentProject.Path & "\Student Pictures\"
            newStrVari = Replace(strVari, oldDirString, newDirString)
            picRst.Edit
            picRst.Fields(i) = newStrVari
            picRst.Update
            
            
        Next i
        picRst.MoveNext
    Loop
picRst.Close
End If

Exit Sub

ErrorHandler:
 MsgBox "Error:  " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

 

[nanscombe]

Just as an aside, in Adobe Lightroom the file locations are stored in four separate fields:

(Using one of my files as an example)

absolutePath - The path from the root to a fixed point in the directory structure
h:\nja_s\dms\documents\001-02\

pathFromRoot - The path from the fixed point to where the picture is stored
015\03\

basename - The part of the filename to the left of the "."
001-02015302

extension - The part to the right of the "."
jpg

I wouldn't quite go that far but would still advocate the storing of files as a RootPath, which might change, and a RelativePath which would not. Again using one of my files:

RootPath (Subject to change)
RelativePath (Will not change)

h:\nja_s\dms\
documents\001-02\015\03\001-02015302.jpg

Or in your case

CurrentProject.Path & "\"
Student Pictures\picture.jpg

I don't know how your application works but I assume it may be too difficult to rewrite where any image boxes get their pictures from?

Maybe even write a function to change the path on the fly?

Public Function newPath(byval picturePath as string)
  Dim plFound as Long

  newPath = picturePath

  plFound = Instr(picturePath,"\Student Pictures\")

  if plFound >0 Then newPath = CurrentProject.Path & Mid(picturePath, plFound)
End Function

The code would look for the string "\Student Pictures\" in the picture path and replace anything to the left of it with CurrentProject.Path whilst keeping the rest intact.

 

[sqrepants]

Yes, it is a bit difficult to rewrite some of the code at this point. im certain that your method would be more efficient.

the only stumbling block with that however, is that i have no idea how to have that code run on each row of the pictures column.

this is why i used a record set and backyard programming skills.

 

[nanscombe]

A little change but, if the new string is the same as the old string why bother changing it.

Private Sub findDir_Click()

Dim picRst As DAO.Recordset
Dim strSQL As String
Dim strVari As String
Dim backSlashPos As Integer
Dim oldDirString As String
Dim newDirString As String
Dim newStrVari As String

On Error GoTo ErrorHandler
    
strSQL = "Select fakeDir from tbl1"
Set picRst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

If picRst.EOF And picRst.BOF Then
Else
    Do While Not picRst.EOF
        For i = 0 To picRst.Fields.Count - 1
            strVari = picRst.Fields(i)
            backSlashPos = InStrRev(strVari, "\")
            oldDirString = Left(strVari, backSlashPos)
            newDirString = CurrentProject.Path & "\Student Pictures\"
            newStrVari = Replace(strVari, oldDirString, newDirString)

' Only change it if newStrVari is different from picRst.Fields(i)
            [COLOR="Red"]If picRst.Fields(i) & vbNullString <> newStrVari & vbNullString Then[/COLOR]

              picRst.Edit
              picRst.Fields(i) = newStrVari
              picRst.Update

            [COLOR="red"]End if[/COLOR]
            
        Next i
        picRst.MoveNext
    Loop
picRst.Close
End If

Exit Sub

ErrorHandler:
 MsgBox "Error:  " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

 

[sqrepants]

If picRst.Fields(i) & vbNullString <> newStrVari & vbNullString Then

i know what the code does. but i dont understand the syntax. can u explain?

 

[nanscombe]

vbNullString is there to catch if either field is Null.

I used to just add on & "" but now it seems to be better to use the constant vbNullString instead.

So basically, it only makes the change if picRst.Fields(i) is not the same as newStrVari

 

[nanscombe]

A bit more streamlining ...

Private Sub findDir_Click()

Dim picRst As DAO.Recordset
Dim strSQL As String
Dim strVari As String
Dim backSlashPos As Integer
Dim newDirString As String
Dim newStrVari As String

On Error GoTo ErrorHandler
    
  strSQL = "Select fakeDir from tbl1"
  Set picRst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

  picRst.MoveLast
  picRst.MoveFirst

  While Not picRst.EOF
' [COLOR="Red"]You are only using 1 field so I have used the Recordset!Fieldname syntax[/COLOR]
    strVari = picRst!fakeDir
    backSlashPos = InStrRev(strVari, "\")
    newDirString = CurrentProject.Path & "\Student Pictures"

' [COLOR="red"]backSlashPos tells you where the filename starts, so I use it to append[/COLOR]
' [COLOR="red"]the filename to the new directory[/COLOR]
    newStrVari = newDirString & Mid(strVari, backSlashPos)

' [COLOR="red"]Only change it if newStrVari is different from picRst!fakeDir[/COLOR]
    If picRst!fakeDir & vbNullString <> newStrVari & vbNullString Then

      picRst.Edit
        picRst!fakeDir = newStrVari
      picRst.Update

    End if
            
    picRst.MoveNext

  Wend

  picRst.Close

Exit Sub

ErrorHandler:
 MsgBox "Error:  " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub