Storing images in Access

[Dale Fye]

I've read lots of posts recommending not to store images in an Access
database, and I usually just store pointers to the images. However, I'm
trying to figure out whether it is possible to store the images in the db and
extract them from the db at runtime.

Specifically, I am working on an application which, among other things,
creates several different PowerPoint slides (this is working pretty well so
far). Now, I have to figure out a way to put these images in the upper left
and right corners of the document, without deploying a .ppt template.

So, the first step is to figure out how to store and retrieve these images.

I also use these images in multiple Access reports and forms, so storing the
image and then filling (somehow) the Access report and form image controls
when these forms or reports are loaded would significantly reduce the size of
my Access application.

Is this a stupid idea? If not, what are my options?

 

[Jeff Boyce]

Which version of Access?

Regards

Jeff Boyce
Microsoft Access MVP

 

[Dale Fye]

2007

----
HTH
Dale

Which version of Access?

Regards

Jeff Boyce
Microsoft Access MVP

 

[Sylvain Lafontaine]

I never tried it personally but DBPix from Ammara (http://www.ammara.com/)
looks good.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Jeff Boyce]

Access 2007 offers a way to store images in Access databases without the
bloat that accompanied this process in previous versions. I have not had
the opportunity to test this, but other newsgroup readers may be able to
offer their perspectives on this 'enhancement'.

I've been using SQL-Server as a back-end and storing PDF images there,
without incident (or complaint from the SQL-Server DBAs).

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

2007

 

[Mark Andrews]

Here's some code I use to save files in an Access 2007 attachment field. It
might have a few extra things you don't need but you sound like a smart guy
(so I think you can figure it out). I'm assuming that a max of 9 files are
in each field. I didn't include the variables. I think I also later
display the attachment info (so extra variables for that etc...). You can
also do the reverse and add files from the file system to an attachment
field (I think microsoft's site has some examples).

'Open recordset of attachments, save each one to disk and assign to
attchments() array
For iii = 1 To 9
Attachments(iii) = ""
Next iii
filepath = CurrentDBDir() & "Attachments999\"
If Len(Dir(filepath, vbDirectory)) = 0 Then
MkDir filepath
End If
iii = 1
Set RSAttachments = rs.Fields("Attachments").Value
While Not RSAttachments.EOF
If (FileExists(filepath &
RSAttachments.Fields("FileName").Value)) Then
Kill filepath & RSAttachments.Fields("FileName").Value
End If
RSAttachments.Fields("FileData").SaveToFile filepath
Attachments(iii) = filepath &
RSAttachments.Fields("FileName").Value
iii = iii + 1
RSAttachments.MoveNext
Wend
RSAttachments.Close

HTH,
Mark
RPT Software
http://www.rptsoftware.com

 

[SF]

You mean storing PDF file in SQL Server? What field do you use?

Could you plaese share some idea here . I have a lot of document in MS word
and PDF as well to bind to each record

SF

 

[Dale Fye]

Thanks, Mark. I'll give it a try.

Here's some code I use to save files in an Access 2007 attachment field.
It might have a few extra things you don't need but you sound like a smart
guy (so I think you can figure it out). I'm assuming that a max of 9
files are in each field. I didn't include the variables. I think I also
later display the attachment info (so extra variables for that etc...).
You can also do the reverse and add files from the file system to an
attachment field (I think microsoft's site has some examples).

'Open recordset of attachments, save each one to disk and assign to
attchments() array
For iii = 1 To 9
Attachments(iii) = ""
Next iii
filepath = CurrentDBDir() & "Attachments999\"
If Len(Dir(filepath, vbDirectory)) = 0 Then
MkDir filepath
End If
iii = 1
Set RSAttachments = rs.Fields("Attachments").Value
While Not RSAttachments.EOF
If (FileExists(filepath &
RSAttachments.Fields("FileName").Value)) Then
Kill filepath & RSAttachments.Fields("FileName").Value
End If
RSAttachments.Fields("FileData").SaveToFile filepath
Attachments(iii) = filepath &
RSAttachments.Fields("FileName").Value
iii = iii + 1
RSAttachments.MoveNext
Wend
RSAttachments.Close

HTH,
Mark
RPT Software
http://www.rptsoftware.com

 

[Jeff Boyce]

In SQL-Server, the datatype is "Image", and it appears as an "OLE Object"
using a linked table in your Access front-end.

I adapted some procedures offered by Ken Getz for handling binary large
objects (AKA, "BLOB"s; AKA "images"; AKA "PDFs"). Search on "ReadBLOB",
"WriteBLOB" and "CopyFile" for more leads and ideas.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

 

[Sylvain Lafontaine]

Never used it personally but you could try DBPix from Ammara.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)