Postgres Pro
Downloads
Home   >   mailing lists

Thread: Question about PostgreSQL's bytea compatibility with MS Access as front-end

Question about PostgreSQL's bytea compatibility with MS Access as front-end

From
Kristina Yamamoto
Date:
Hi,
 
I'd really appreciate any input for this issue we've been having.
 
I work for an environmental non-profit. We have a database that is used to store our wildlife tracking data, which includes photos of wildlife taken from the motion-detector cameras set up near Vail, CO. The database was originally created in the 2003 version Microsoft Access (not by me), and because of the large number of records (4000+), the database is now at its 2 gb limit set by Microsoft. The non-profit employees are set on storing the photos in the database.
 
We decided to migrate the database to PostgreSQL, because of the ability to still use Access as the front-end (there are forms, queries, etc that are easier for the volunteers to use in Access than learning SQL.) We bought the DBConvert program, which stores the photos in bytea format in PostgreSQL. However, when we try to link to the PostgreSQL database using Access, the pictures are no longer recognized as OLE - instead they are binary and are no longer visible using Access' image viewer.
 
I realize this could be an issue on any of the three fronts: DbConvert, MS Access, or PostgreSQL. I'm currently in contact with the DBConvert company, but I'd like some feedback from the bright minds on this mailing list.
 
Has anyone encountered this sort of problem before? Short of taking the photos out of the database and just saving a link to their folder(s), can anyone think of another solution?
 
Thanks very much!
 
-Kristina
 
 

Looking for last minute shopping deals? Find them fast with Yahoo! Search.

Re: Question about PostgreSQL's bytea compatibility with MS Access as front-end

From
Richard Huxton
Date:
Kristina Yamamoto wrote:
>
> We decided to migrate the database to PostgreSQL, because of the
> ability to still use Access as the front-end (there are forms,
> queries, etc that are easier for the volunteers to use in Access than
> learning SQL.) We bought the DBConvert program, which stores the
> photos in bytea format in PostgreSQL. However, when we try to link to
> the PostgreSQL database using Access, the pictures are no longer
> recognized as OLE - instead they are binary and are no longer visible
> using Access' image viewer.

You can work around it by creating a temporary file and streaming the
data to it, then displaying that file on the form. That's a common
(though perhaps fiddly) option.

This site describes things, but bear in mind they're selling a plugin to
handle this problem:
  http://www.ammara.com/articles/accesspictureole.html
More here too:
  http://www.jamiessoftware.tk/articles/handlingimages.html

--
   Richard Huxton
   Archonet Ltd