Routine to upload gif images to OLE field?
Davo
I'm using Access 2002.
I know that uploading images directly to an OLE field within the
database itself is bad for bloating issues - and I usually use links
to the file path outside the dbase, but I've come unstuck trying to
display images in a continuous form (so I need a different image for
each record). To enable linked images with continuous forms I know
requires some workarounds - but the coding required may be a bit
beyond my knowledge - but I'm open to any other left-field ideas that
may work for me.
But I may have to resort to uploading these gif files directly to an
OLE field in one of my tables - so each record can display its correct
image on the form. Not ideal on the bloat front, but I can wear it
for now.
From skulking around these forums and elsewhere I'm finding this
"AppendChunk" method may do the trick? - I dont know much about it -
but there seems to be all sorts of other weird binary type coding
required to go with it - not to mention the other issues associated
with image files in Access - I'm getting a bit lost in the haze!
So basically I need a routine to grab gif files from a certain
directory on the hard drive and append them to the appropriate record
in a table. Ie: go to "C:\MyPics", grab first file "1314.gif",
append this to the ole field in Tbl_Image where Id = 1314, loop to
next gif file.
Can anyone point me in the right direction or suggest another
alternative for me?
Ta guys,
Davo
Rich P
I did a little checking around and have not found any samples in VBA for
uploading filestreams to a binary large object (blob) which is what
would be required for loading an image to a table since OLE objects are
essentially binary objects.
I have some routines in C# for uploading and downloading filestreams
(Images, word docs, Excel files in their native format) to and from
binary large objects in sql server (2005), but that is a little bit
outside of Access country.
As far as automating the loading of binary objects into Access tables
goes - I sense this is not a feature of Access presently. You will be
resigned to loading the images by hand. If you need this operation to
be automated you may have to consider a slightly more sophisticated
platform like VB.Net/C# with Sql Server (2005). Note: I hate it when
people say that - "this is beyond the capabilities of ..."
So here is a sample of my C# routine for reading an Image to a
filestream to a binary large object (blob):
-----------------------------------
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Windows.Forms;
SqlConnection conn1; SqlDataAdapter da; DataSet ds;
private void Form2_Load(object sender, EventArgs e)
{
conn1 = new SqlConnection();
conn1.ConnectionString = "Data Source=myServer;Initial
Catalog=myDB;Integrated Security=True";
da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand();
da.SelectCommand.Connection = conn1;
ds = new DataSet();
}
private void btnWriteImgToSqlSvr_Click(object sender, EventArgs e)
{
string FileNameAndpath = Application.StartupPath + @"\image1.gif";
FileStream fs = new FileStream(FileNameAndpath, FileMode.Open,
FileAccess.Read);
byte[] b = new byte[fs.Length];
fs.Read(b, 0, b.Length);
fs.Close();
fs = null;
string strQuery;
strQuery = "insert into dbo.myTable(FileName, binFile)
values('image1.gif', @img)";
da.SelectCommand.Parameters.Add("@img", SqlDbType.VarBinary, b.Length,
"binFile");
da.SelectCommand.Parameters["@img"].Value = b;
da.SelectCommand.CommandText = strQuery;
conn1.Open();
da.SelectCommand.ExecuteNonQuery();
conn1.Close();
}
-----------------------------------
The VB.Net version of this probably wouldn't look much different -- less
the curly braces and semicolons. And as far doing this in com goes
(Access is a com object) - not sure if you could do this in VB6, would
probably require C++ which would be way less fun looking than the sample
above.
Anyway, you would run this routine in a loop - the loop would read some
Image folder and then load all the images to the server as blobs (binary
large objects - yes - a gif is a small object - but in binary you still
have thousands of 1s and 0s).
Rich
*** Sent via Developersdex http://www.developersdex.com ***
Tony Toews [MVP]
>Can anyone point me in the right direction or suggest another
>alternative for me?
There might be some useful sample code at http://lebans.com/oletodisk.htm or poke
about elsewwhere on his site.
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/
Jens Schilling
Davo wrote:
> So basically I need a routine to grab gif files from a certain
> directory on the hard drive and append them to the appropriate record
> in a table. Ie: go to "C:\MyPics", grab first file "1314.gif",
> append this to the ole field in Tbl_Image where Id = 1314, loop to
> next gif file.
>
> Can anyone point me in the right direction or suggest another
> alternative for me?
You could take a look at Jamie Czernik demo "Get Binary" that is available
in Rogers Access Library:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=205
Regards
Jens
Rich P
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=205
so I stand corrected about Access not being able to automate binary
operations. However, I have Access 2003 here, and cannot display OLE
embeded images from a table on a form.
According to an article at
http://support.microsoft.com/kb/832508
this requires PhotoED to be installed on the target machine otherwise
Access can't find the OLE server. Thus, I have not experimented with
this feature of Access, and I was not able to view any ole images
contained in the table where automated imported or manually imported.
But, apparently, if your machine is using older MS Office, then it IS
able to automate the retrieval of images to OLE and display them on the
form from the table.
Davo
Yes that example from Rogers Access Library is pretty much what I'm
after - and I can just tweak the code to a loop so I dont have to
select every individiual file from the dialog box. But, I too am
getting stuck at actually being able to display the image when I try
to open it from the table - as per Rich I'm getting this "problem
communicating with the OLE server - close the OLE Server and
restart..." type message.
I re-installed PhotoEd from the XP disk but I still get the error.
Have done some other searches on this and am running into a few dead-
ends.
Any other suggestions for how to fire up an "OLE server??"
Cheers,
Davo
Jens Schilling
You could try this:
http://www.lebans.com/loadjpeggif.htm
Sometimes it's enough to install additional image filters:
http://office.microsoft.com/en-us/ork2003/HA011513581033.aspx
Regards
Jens
Davo
I think we're getting closer - having the OLE stored as this "Long
binary data" type is not working re being able to display the image.
But if I manually insert the gifs into the OLE field (via the menus -
ie: click on the OLE field for a record, Insert -> Object -> Create
from file -> myfile.gif etc), its reads it in as "Microsoft Photo
Editor 3.0 Photo" - and these images do display properly.
So the question is then, what sort of code do I need to automate the
manual insertion of these files into the OLE field as a simple
"Microsoft Photo Editor 3.0 Photo" type? I presume its something like
what I have below, but I just dont know what the correct syntax is re
dealing with a picture file.
With RS
.AddNew
.Fields("FileName") = strGifFileName
.Fields("MyOLEfield") = **** what do I type in here to
make the magic happen??? ****
.Update
Any ideas guys - I feel like we're almost there!
Davo
Rich P
I have experimented a little bit further with the suggestions posted
here, and also did not have any luck being able to display the images
that were inserted through automation.
As I had mentioned earlier, I was able to automate binary loading of
images using .Net and display them in a datagridview control (which
would be the Access equivalent of a continuous form. Of course, this is
not Access. I believe that Access does not support the kind of
functionality you are seeking.
If you need this functionality my suggestion would be to step up to the
newer platforms of .Net. The learning curve isn't as bad as trying to
learn C++ (although a background in C++ would definitley make it
easier), and .Net has successfully simplified (greatly) the coding of
sophisticated procedures like what you are seeking which would otherwise
require some major C++ coding.
I used to refer to situations like this as limitations of Access. Well,
my new description is this - functionality that has not been designed
into the Access model. Microsoft apparently did not include this kind
of functionality into Access. This functionality could be added to
Access individually, but it would require a major programmer geek (I'm
sure I qualify as a geek - just not a major programmer geek - don't have
enough brain cells :). What is cool about .Net is that it can make an
average programmer look like a stud programmer where things that can be
done in .Net would formerly have required a brainiac maniac in the com
world (the Access world).
Davo
Thanks again Rich - I really appreciate you trying to nut this case
out.
I might have to almost concede defeat on this one (or should that be
"resign myself to functionality that has not been designed into the
Access model!"). Although, I have now come up with an alternative
method that works for me, albeit not *exactly* what I wanted. I've
come up with a sub-form with 24 different image objects (luckily
there's a max limit of 24 images I require for a single event so its
ok - and these can obviously be linked images now so no bloat) - and
I've got some update queries behind this to automate it all rather
easily. Bit of a work-around but it'll have to do.
I'm not a progammer at all - not average, and definitely not stud! - I
dabble in Access development just for my own needs now and everything
I've needed to code in Access over the last 6 years I've been able to
wrangle from these forums - this is actually the first time I've ever
posted as I really was stuck - admittedly I do feel a little
vindicated there is no real straightforward solution to it! ...but its
a credit to the expert contributors such as yourself and the other
MVPs that I've got by for so long without ever having to make a post -
and more to the point not actually require any background training or
education in anything IT-related!
Thanks again...
Davo
Jens Schilling
Hi,
Sorry for the poor outcome, maybe my lack of english is responsible for it.
It's hard to discus a problem in a strange language, because it's very easy
to miss some details. I think I'm standing at this point now.
So for a last chance let me point you to two further examples.
The demo "ConvertPics_OLEtoExternal.mdb" contains a form named
"F_PicsEmbeddedInOLE" that displays images stored in a OLE field. In a quick
test I could change it to a continuous form, but did not tested it with more
than the containing four images.
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=129
At last let me suggest to take a look at Larry Linsons images sample that
also contains a Word document discussing the handling of images in an Access
database.
http://accdevel.tripod.com/imaging.htm
Regards
Jens
Rich P
http://accdevel.tripod.com/imaging.htm
This sample does appear to store an image file as a blob in a table, but
it does not really display the blob in an image control on a form. I
stepped through the code and it looks like the blob gets written to the
disk first as a temp file and the image control is actually displaying
the image from the disk. I believe the OP wants to display the image
directly from the table.
This sample does show how to store binary (image) objects as blobs in an
Access table using a form of automation. So, again, I was incorrect to
suggest that Access did not posses this functionality. But I was
correct to suggest that at the current time, it does not appear that you
can automate the storing of blobs (Images) and be able to display them
from directly from the table. I do, however, recall being able to
display images stored as blobs in earlier versions of Access directly
from the table. So I am not clear how now you can automate the storing
of blobs but not be able to display them directly from the table. Thus,
I have stepped up to the .Net platform (actually several years ago)
which has tremendously simplified complex operations like this one.