Copy and rename image files and return the relativepath of the images and back-end file (1 Viewer)

josephbupe

Registered User.
Local time
Today, 22:52
Joined
Jan 31, 2008
Messages
247
Hi.

From the code below, I want to programmatically copy and rename the files I am adding into the table tblObjects to be same as the primary key which is ObjectID, i.e. /Images/1.jpeg etc and then return the relative filepath containing all the images to be in a folder called "Images" within the current project folder.

Currently, I cant even populate the table; When i select a file through the file dialog to populate the table, i am getting this error:
error 75 in cmdBrowse_Click procedure: path/file access error.

Below is the code:

Code:
Private Sub cmdBrowse_Click()
On Error GoTo Err_Handler

Dim PathStrg As String
Dim relativePath As String
Dim dbPath As String
'Code compatible with both 32-bit & 64-bit Office
' Set options for the file dialog box.
 Dim F As FileDialog
Set F = Application.FileDialog(msoFileDialogFilePicker)
F.Title = "Locate the image file folder and click on 'Open' to select it"

' Clear out any current filters, and add our own
F.Filters.Clear
F.Filters.Add "Image files", "*.jpg;*.jpeg"

' Set the start folder. Open in default file folder if blank
F.InitialFileName = Nz(Application.CurrentProject.Path & "\Images\",   "C:\") 'modify this as appropriate

' Call the Open dialog procedure.
F.Show

' setup new file name and appropriate DB subfolder
      relativePath = "\Images\" & Me.txtObjectID & ".jpg"
      dbPath = Application.CurrentProject.Path
    
    'copy selected file with new name and subfolder
      FileCopy LCase(PathStrg), dbPath & relativePath
    
    'update the table field with the new file name and relative location
      Me!ImagePath.Value = relativePath
    'display the image from the subfolder of the DB
      Me.Requery

  Exit_Handler:
  Exit Sub

  Err_Handler:
If Err <> 5 Then 'err=5 user cancelled
    MsgBox "Error " & Err.Number & " in cmdBrowse_Click procedure: " & Err.Description
End If
Resume Exit_Handler

End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:52
Joined
Oct 29, 2018
Messages
21,358
Hi. I am thinking either Debug.Print the file path or step through the code and examine the variable content to see exactly why you're getting that error.
 

josephbupe

Registered User.
Local time
Today, 22:52
Joined
Jan 31, 2008
Messages
247
Hi. I am thinking either Debug.Print the file path or step through the code and examine the variable content to see exactly why you're getting that error.
Actually, the error is pointing me to this part of the code:
'copy selected file with new name and subfolder
FileCopy LCase(PathStrg), dbPath & relativePath
 

josephbupe

Registered User.
Local time
Today, 22:52
Joined
Jan 31, 2008
Messages
247
And what is inside those variables? Make sure they are valid paths.
OK, please, I have attached the databse file for expert perusal, if you don't mind to look. Sorry i am a total amateur.
 

Attachments

  • mdb_file.accdb
    508 KB · Views: 223

theDBguy

I’m here to help
Staff member
Local time
Today, 13:52
Joined
Oct 29, 2018
Messages
21,358
OK, please, I have attached the databse file for expert perusal, if you don't mind to look. Sorry i am a total amateur.
Hi. Okay, I ran your code and here's the deal. Take a look at the following screenshot.

1649460597726.png


In arrow #1, you declared the variable you used in the FileCopy code (the highlighted line), but you never populated it with any value. That's why in arrow #3, you can see the value of the variable (when you hover your mouse over it) is an empty string ("").

Also, you used the FileDialog object but never really assigned the filepath the user picked from the file browser to any variable. That's probably because you commented that line out as shown in arrow #2.

Hope that helps...
 

josephbupe

Registered User.
Local time
Today, 22:52
Joined
Jan 31, 2008
Messages
247
Ok that has been corrected and now images can be uploaded,

What i need now is to only populate the table with a relative path contaning the images, not the full path. Right now i can only scratch my head wondering how to achieve that.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:52
Joined
Oct 29, 2018
Messages
21,358
Ok that has been corrected and now images can be uploaded,

What i need now is to only populate the table with a relative path contaning the images, not the full path. Right now i can only scratch my head wondering how to achieve that.
Relative (path) to what? Please show us some examples.
 

josephbupe

Registered User.
Local time
Today, 22:52
Joined
Jan 31, 2008
Messages
247
Relative (path) to what? Please show us some examples.
Ah, sorry for not including more information earlier. I meant to say that the database would be mobile on a thumb drive, and so I would like to have all its folders and files in them to stay linked to the front-end within the same folder. The first time i tried to move the database on a thumb drive away from the PC where I had populated it with about 200 records with images, i couldn't link back the images because i had populate the full path to the images in the table.

I think instead of E:\WORKS OF ART DATABASE\Images\_600.jpg, it should be something like \Images\_600.jpg

I just don't know how to achieve that.
 

bastanu

AWF VIP
Local time
Today, 13:52
Joined
Apr 13, 2010
Messages
1,401
Add the following function to a standard module and use it in a update query to update the field to just the file name (i.e. "_600.jpg"):
Code:
Public Function StripLast (path As String) As String
    Dim x As Long
    Dim y As Long
    y = Len(path) + 1
    x = 1
    x = InStr(x, path, "\", vbDatabaseCompare)
    Do While x > 0
        y = x
        x = InStr(x + 1, path, "\")
    Loop
    StripLast = Right(path, Len(path) - y)
End Function

Then code your forms\reports, etc. to use your relative path using CurrentProject.Path

Cheers,
 

josephbupe

Registered User.
Local time
Today, 22:52
Joined
Jan 31, 2008
Messages
247
Add the following function to a standard module and use it in a update query to update the field to just the file name (i.e. "_600.jpg"):
Code:
Public Function StripLast (path As String) As String
    Dim x As Long
    Dim y As Long
    y = Len(path) + 1
    x = 1
    x = InStr(x, path, "\", vbDatabaseCompare)
    Do While x > 0
        y = x
        x = InStr(x + 1, path, "\")
    Loop
    StripLast = Right(path, Len(path) - y)
End Function

Then code your forms\reports, etc. to use your relative path using CurrentProject.Path

Cheers,
Hello bastanu.

Thank you for your advise and the module you just posted for me. However, let me confess to you that I have limited knowledge of VB programming.

Please, see the attached copy of the the accdb file for your perusal.
 

Attachments

  • accdb_5.accdb
    468 KB · Views: 229

theDBguy

I’m here to help
Staff member
Local time
Today, 13:52
Joined
Oct 29, 2018
Messages
21,358
I just don't know how to achieve that.
I think you do. You're already doing it.
Code:
relativePath = "\Images\" & Me.txtObjectID & ".jpg"
 

bastanu

AWF VIP
Local time
Today, 13:52
Joined
Apr 13, 2010
Messages
1,401
Exactly, all you need is to use the function I provided to extract the file name only from the full name (and path).
Here is an updated sample with the update query.

Cheers,
 

Attachments

  • accdb_5.accdb
    616 KB · Views: 240

Users who are viewing this thread

Top Bottom