Not sure if this is even slightly possible.

[Wes Conner]

Ok, before reading this, understand it is my employers request, I do
not see this as a viable option.

What "we" would like to happen is this. Employee A inserts an SD card
into the PC, the card is then scanned for files, the files sizes are
noted, if any are "to large", lets say more then 10MB, they will be
reduced in size, most likely by reducing the quality of the image.
Once this resizing has been completed a prompt is to appear,
requesting the Job Name, Location, or Number, which is already in the
database, once this defining field has been completed by Employee A,
the recently re-sized photos will be attached to the database as an
OLE Object, or any other mode of storage acceptable by MS Access.

Now, before you tell me how impossible this is, I know. What I am
hoping to gain is an alternative, some other way to accomplish
something remotely similar. The point of this is to have photos
attached to a record. What I need you to keep in mind that the end
user of this database is going to have minimal computer knowledge.

I would like to thank everyone in advance for their assistance, this
is my last resort before trying to explain to my boss that this is not
a possibility.

 

[Jeff Boyce]

Not sure this is even an MS Access database question...

(just remember, and remind your boss, "You can have this good, fast,
cheap -- pick two!"

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Wes Conner]

Not sure this is even an MS Access database question...

(just remember, and remind your boss, "You can have this good, fast,
cheap -- pick two!"

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

LOL thank you, sadly if it is not possible in MS Access (he thinks it
is) I will need to find another database app that could handle it.
Any suggestions?

 

[Dirk Goldgar]

Ok, before reading this, understand it is my employers request, I do
not see this as a viable option.

What "we" would like to happen is this. Employee A inserts an SD card
into the PC, the card is then scanned for files, the files sizes are
noted, if any are "to large", lets say more then 10MB, they will be
reduced in size, most likely by reducing the quality of the image.
Once this resizing has been completed a prompt is to appear,
requesting the Job Name, Location, or Number, which is already in the
database, once this defining field has been completed by Employee A,
the recently re-sized photos will be attached to the database as an
OLE Object, or any other mode of storage acceptable by MS Access.

Now, before you tell me how impossible this is, I know. What I am
hoping to gain is an alternative, some other way to accomplish
something remotely similar. The point of this is to have photos
attached to a record. What I need you to keep in mind that the end
user of this database is going to have minimal computer knowledge.

I would like to thank everyone in advance for their assistance, this
is my last resort before trying to explain to my boss that this is not
a possibility.

I'm inclined to think that it's all *possible*, but some elements may be
harder to accomplish than others.

1. Storing/tracking pictures in an Access database.

Your approach to this will depend on what version of Access you are running
and how many pictures you need to keep track of. Before Access 2007,
storing pictures in the database was prohibitively inefficient,
storage-wise, and it was generally conceded to be much better to store the
pictures in a known folder and store only the names (and paths, if
necessary) as text in the database. One would use an unbound image control
on a form to display each picture when desired.

Access 2007 allows pictures to be stored in the database much more
effficiently, so it is now practical to store images in the database, so
long as the maximum number you expect to store doesn't push the size of the
database file up toward the 2GB size limit that is the maximum size of an
Access database. If each picture might be as big as 10MB, then you may have
a problem storing a whole lot of them in the database. My inclination would
be to store them externally anyway.

2. Processing all the pictures on the SD card.

Assuming that the SD card would be accessible as a simulated hard disk and
known to the operating system by a drive letter, this is quite doable. VBA
code can loop through the folder(s) on the disk, and for each image file
found, prompt the user for the necessary information to store it and record
it properly. The code could copy the file to a designated folder used by
the application to store such things, or (if you really want to) store the
picture in the database itself.

3. Resizing images where needed.

There are image-processing libraries that could be used for this, but it
might be just as effective (and cheaper) to use an image-processing program
like IrfanView and invoke it in batch mode to resize one or more images.
VBA code can use the Shell function to invoke a program like IrfanView and
pass command-line arguments to make it do things.

4. Automatically have the photo-import process start up when the SD card is
inserted?

I don't know how to do this, though I expect it could be done. Much easier
would be to put the card in the slot, then manually invoke the database
application's Import Photos process (which you would have written).

 

[John W. Vinson]

Ok, before reading this, understand it is my employers request, I do
not see this as a viable option.

What "we" would like to happen is this. Employee A inserts an SD card
into the PC, the card is then scanned for files, the files sizes are
noted, if any are "to large", lets say more then 10MB, they will be
reduced in size, most likely by reducing the quality of the image.
Once this resizing has been completed a prompt is to appear,
requesting the Job Name, Location, or Number, which is already in the
database, once this defining field has been completed by Employee A,
the recently re-sized photos will be attached to the database as an
OLE Object, or any other mode of storage acceptable by MS Access.

Now, before you tell me how impossible this is, I know. What I am
hoping to gain is an alternative, some other way to accomplish
something remotely similar. The point of this is to have photos
attached to a record. What I need you to keep in mind that the end
user of this database is going to have minimal computer knowledge.

I would like to thank everyone in advance for their assistance, this
is my last resort before trying to explain to my boss that this is not
a possibility.

Well... I'm afraid it IS possible, albeit probably not all that easy. You
would need some VBA code using the Dir() function and some system calls to
loop through the directory on the SD card (which would need to be treated as a
removable disk drive). The code would need to call some other program to do
the resizing, Access is certainly not the right tool for that job!

I would NOT store the pictures (or other external binary files) in an Access
database at all. You could store them in a folder on a shared disk drive
(copying them from the card), and store either the path and filename in an
Access table, or if you prefer, store a Hyperlink field to the file. This link
would be in a table related one-to-many to the Job table by the unique Job ID
(which hopefully already exists). Since you're not storing the files internal
to the database, and since disk is cheap, you might not even need to resize
them.

It would be a pretty substantial project, especially if there are a wide
variety of file types. If they're all images and if you have some
corporate-standard image handling software it would ease the complexity a bit.

 

[Wes Conner]

I'm inclined to think that it's all *possible*, but some elements may be
harder to accomplish than others.

1. Storing/tracking pictures in an Access database.

Your approach to this will depend on what version of Access you are running
and how many pictures you need to keep track of.  Before Access 2007,
storing pictures in the database was prohibitively inefficient,
storage-wise, and it was generally conceded to be much better to store the
pictures in a known folder and store only the names (and paths, if
necessary) as text in the database.  One would use an unbound image control
on a form to display each picture when desired.

Access 2007 allows pictures to be stored in the database much more
effficiently, so it is now practical to store images in the database, so
long as the maximum number you expect to store doesn't push the size of the
database file up toward the 2GB size limit that is the maximum size of an
Access database.  If each picture might be as big as 10MB, then you mayhave
a problem storing a whole lot of them in the database.  My inclination would
be to store them externally anyway.

2. Processing all the pictures on the SD card.

Assuming that the SD card would be accessible as a simulated hard disk and
known to the operating system by a drive letter, this is quite doable.  VBA
code can loop through the folder(s) on the disk, and for each image file
found, prompt the user for the necessary information to store it and record
it properly.  The code could copy the file to a designated folder used by
the application to store such things, or (if you really want to) store the
picture in the database itself.

3. Resizing images where needed.

There are image-processing libraries that could be used for this, but it
might be just as effective (and cheaper) to use an image-processing program
like IrfanView and invoke it in batch mode to resize one or more images.
VBA code can use the Shell function to invoke a program like IrfanView and
pass command-line arguments to make it do things.

4. Automatically have the photo-import process start up when the SD card is
inserted?

I don't know how to do this, though I expect it could be done.  Much easier
would be to put the card in the slot, then manually invoke the database
application's Import Photos process (which you would have written).

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)

Wow, simply wow. I want to thank you, I would buy you a steak dinner
if I could. Now I need to work on this some more, you have broken it
down where I can wrap my head around the task at hand. How exactly
would you get Access to read a sd card, lets say it was assigned
letter D, and attach all photos to a record? Assuming there are no
more then 30 photos?

 

[Dirk Goldgar]

How exactly would you get Access to read a sd card, lets say it was
assigned letter D, and attach all photos to a record? Assuming there are
no more then 30 photos?

For the moment, let's ignore the details of what you want to do for each
photo, and concentrate on the file-management aspect. You might have a
procedure like this to import the photos in a given folder:

'------ start of example code -------
Sub ImportPhotos(SourceFolder As String)

' Import all the image files in the folder passed as <SourceFolder>.

On Error GoTo Err_Handler

Dim strFileName As String

strFileName = Dir(SourceFolder)

Do While Len(strFileName) > 0

' We'll accept JPG, PNG, and BMP files.

If strFileName Like "*.jpg" _
Or strFileName Like "*.png" _
Or strFileName Like "*.bmp" _
Then
ImportOnePhoto(SourceFolder, strFileName)
End If

' Get next file in folder
strFileName = Dir()

Loop

Exit_Point:
Exit Sub

Err_Handler:
MsgBox _
"An error occurred while importing photos from '" & _
SourceFolder & "'. The current or most recent file " & _
"name processed was '" & strFileName & _
"'. The error reported was:" & _
vbCr & vbCr & _
Err.Number & ": " & Err.Description, _
vbExclamation, _
"Error Importing Photos"

Resume Exit_Point

End Sub
'------ end of example code -------

That is just "air code", of course. You might want to make this procedure
recursive, to import all the files in any subfolders of the current folder.
If you do that, then I think you'd want to create a FileSystemObject and use
its related methods and objects instead of Dir(), because Dir() is likely to
get confused when called recursively.

 

[Jeff Boyce]

Wes

I'm not saying it is not possible, I'm saying (all humor aside), it will
cost (or it will be cheap but slow, ... or cheap and poor quality, or ...).

I YOU are on tap to do this in Access, you have up to four learning curves
to surmount:
1. normalization/relational database design
2. Access tricks/tips
3. Graphical User Interface design
4. Application development

Best of Luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Not sure this is even an MS Access database question...

(just remember, and remind your boss, "You can have this good, fast,
cheap -- pick two!"

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

LOL thank you, sadly if it is not possible in MS Access (he thinks it
is) I will need to find another database app that could handle it.
Any suggestions?

 

[Jeff Boyce]

(that should have been ... 'If YOU are on tap ...')

Jeff

 

[Tony Toews [MVP]]

I'm inclined to think that it's all *possible*, but some elements may be
harder to accomplish than others.

I would've typed pretty much the exact same thing right down to using
IRFanview.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/