Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-14-2018, 06:06 AM   #1
eshai
Newly Registered User
 
Join Date: Jul 2015
Posts: 59
Thanks: 6
Thanked 4 Times in 4 Posts
eshai is on a distinguished road
insert image path to a table automatically

hi:
i have a table to store file path for images
"lastname" "firstname" "city" "image"
is there a way to auto insert the path of the image with name to the image field
(c:\students images\John Doe california.gif) by the value from the table

in regards eshai

eshai is offline   Reply With Quote
Old 06-14-2018, 06:30 AM   #2
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,301
Thanks: 77
Thanked 1,294 Times in 1,209 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: insert image path to a table automatically

Try this:
Code:
"c:\students images\" & '" & firstname & "' & " " & '" & lastname & "' & " " & '" & city & "' & ".gif"
However, I suggest you remove the spaces e.g. c:\StudentImages\JohnDoeCalifornia.gif
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old 06-14-2018, 06:36 AM   #3
eshai
Newly Registered User
 
Join Date: Jul 2015
Posts: 59
Thanks: 6
Thanked 4 Times in 4 Posts
eshai is on a distinguished road
Re: insert image path to a table automatically

Quote:
Originally Posted by ridders View Post
Try this:
Code:
"c:\students images\" & '" & firstname & "' & " " & '" & lastname & "' & " " & '" & city & "' & ".gif"
you mean to put at in a query as expression on the image field?

eshai is offline   Reply With Quote
Old 06-14-2018, 06:46 AM   #4
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,301
Thanks: 77
Thanked 1,294 Times in 1,209 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: insert image path to a table automatically

Not quite.
Use an update query to populate the image field with that expression in the Update To line
Check that you have data in both name fields & the city field for each student.
If there are any gaps, either omit that record or handle the issue using Nz functions.

However, just in case I've made a mistake ... or you do so copying the expression, first try it in a select query & check the results BEFORE running the update

Otherwise just concatenate the fields in an expression as you suggested. If so, you don't need to use the image field
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old 06-14-2018, 09:36 AM   #5
eshai
Newly Registered User
 
Join Date: Jul 2015
Posts: 59
Thanks: 6
Thanked 4 Times in 4 Posts
eshai is on a distinguished road
Re: insert image path to a table automatically

Quote:
Originally Posted by ridders View Post
Not quite.
Use an update query to populate the image field with that expression in the Update To line
not working It puts the code into the field as is
eshai is offline   Reply With Quote
Old 06-14-2018, 09:53 AM   #6
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,301
Thanks: 77
Thanked 1,294 Times in 1,209 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: insert image path to a table automatically

Post the query sql and I'll modify it for you
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old 06-14-2018, 11:03 AM   #7
eshai
Newly Registered User
 
Join Date: Jul 2015
Posts: 59
Thanks: 6
Thanked 4 Times in 4 Posts
eshai is on a distinguished road
Re: insert image path to a table automatically

Quote:
Originally Posted by ridders View Post
Post the query sql and I'll modify it for you
Code:
UPDATE test SET test.image = "C:\StudentsManager\data\Pictures\" & '" & lastname & "' & " " & '" & firstname & "' & " " & '" & city & "' & ".gif";
thank you

eshai is offline   Reply With Quote
Old 06-14-2018, 11:29 AM   #8
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,301
Thanks: 77
Thanked 1,294 Times in 1,209 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: insert image path to a table automatically

Sorry. A few basic errors in my original version which i'll blame on answering on my phone. Corrected version which does work

Code:
UPDATE test SET test.[Image] = "C:\StudentsManager\data\Pictures\" & [lastname] & " " & [firstname] & " " & [city] & ".gif";
2 things:
1. I believe Image is a reserved word. Change it to ImagePath or similar
2. I strongly advise against having spaces in the image path file name to avoid paths like this
Code:
C:\StudentsManager\data\Pictures\White Jane Weston Super Mare.gif
This is better
Code:
C:\StudentsManager\data\Pictures\WhiteJaneWestonSuperMare.gif
But its still 'clunky'
Better still would be to use a unique StudentID field for the file name
e.g. if the studentID = "S11234" then the image path would be
Code:
C:\StudentsManager\data\Pictures\S11234.gif
HTH
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by ridders; 06-14-2018 at 11:45 AM.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
eshai (06-14-2018)
Old 06-14-2018, 11:45 AM   #9
eshai
Newly Registered User
 
Join Date: Jul 2015
Posts: 59
Thanks: 6
Thanked 4 Times in 4 Posts
eshai is on a distinguished road
Re: insert image path to a table automatically

perfect work great

i'm rebuilding a db the picture folder is 4gb so i can't change the structure

thank you for your help
best regards eshai
eshai is offline   Reply With Quote
Old 06-14-2018, 11:50 AM   #10
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,301
Thanks: 77
Thanked 1,294 Times in 1,209 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: insert image path to a table automatically

Glad to have helped.
Good thing you weren't trying to store these using the dreaded attachment field.

BTW its really not too late to rename the image files
There are several free utilities which will allow you to batch rename files automatically even if you have many thousands of them
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old 06-14-2018, 12:17 PM   #11
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 137
Thanks: 1
Thanked 22 Times in 21 Posts
MajP is on a distinguished road
Re: insert image path to a table automatically

Quote:
There are several free utilities which will allow you to batch rename files automatically even if you have many thousands of them
Yes I use the free Irfanview that will batch rename to include taking out spaces.
https://www.irfanview.com/
Free, light weight, and very functional. Batch conversion and batch rename.

MajP is offline   Reply With Quote
Reply

Tags
vba access

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
automatically insert data to another table by completing checkboxes deamond Forms 7 06-03-2015 05:53 AM
Image upload to server and store image path mysql claude.kelly Forms 0 05-30-2013 07:48 PM
Insert an image into a table carrybag93 Tables 2 05-25-2012 04:54 AM
How to insert image into the table? accessman2 SQL Server 1 01-01-2007 09:07 PM
insert image path adi32 General 2 12-17-2006 08:27 AM




All times are GMT -8. The time now is 10:58 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World