2

Since the latest versions of Access (2007/2010) it's possible to add attachments to rows. I have such a database with members and their photos, but i would like to export them (including photos) and use them for a new webapplication. I have made a XML-export with Access 2007. The photos which i have attached are encoded in the XML export.

Please find below an example of the file smiley.jpg, which i have attached in a random row in my database. (The original file smiley.jpg you may find back here: http://img812.imageshack.us/img812/8248/smileyq.jpg)

<Foto>
<FileData>FAAAAAEAAAAEAAAAagBwAGcAAAD/2P/gABBKRklGAAEBAQBkAGQAAP/bAEMAAwIC
AwICAwMDAwQDAwQFCAUFBAQFCgcHBggMCgwMCwoLCw0OEhANDhEOCwsQFhARExQV
FRUMDxcYFhQYEhQVFP/bAEMBAwQEBQQFCQUFCRQNCw0UFBQUFBQUFBQUFBQUFBQU
FBQUFBQUFBQUFBQUFBQUFBQUFBQUFBQUFBQUFBQUFBQUFP/AABEIACgAKAMBIgAC
EQEDEQH/xAAbAAACAgMBAAAAAAAAAAAAAAAABgUHAQgJA//EADIQAAIABAMFBgYC
AwAAAAAAAAECAwQFEQAGEgcTFSExCBRRYYGRCSIkMkGxM3GCodH/xAAYAQADAQEA
AAAAAAAAAAAAAAAEBQgHBv/EACgRAAIBAwMDBAIDAAAAAAAAAAECEQADBAUhMQYS
QQciYYETURRxkf/aAAwDAQACEQMRAD8A6nxoyS8Mu5soxAz1VnI9xBPd4fiBdj64
KpP7+omCD8kHl/l+f+Y0T+KR2m9ofZ+puziHkKomi8Um5qPOTywEi69wIOiCdakB
W3rEjqdAF7XxmWp6te1TLOlafd7GkiZiSASdxuAIjbmjUthF73FbnzMSoKxZZ+YD
eG9NvbBI5yqFMihZ0d8l782AAdR+j6++FSo5xqSbIouaBTTxhaEakKaQf5+77zdW
6/d8vjjUT4aHaJz92gKDtAXPdQasmlzktElJ5oCQiN+IpeCNCgWTdqQOo126WxO7
6h1FgWcnWsDLITHZAwLEz3tA9pkHfmfqmwS0xW2y810ekp2DUZZJiXiCJCcXDDBh
GyZUGp1ZMizfTzNyoPQOBe/qBb2wYp/o/qW31VpKZ6jtee1x+mETHwQQR8GKS5Fk
2LhWstMlavPq33CO/tqNsQGYMv5U2nU+Xh1qj0vMsnIzpiwUqEqkwkGZguyFlDgg
OrBluPMYlNodBVp12imOslO21NLx3gsHFrjWhDC9gevPmOmKRg54idngvR80S802
zxXZqTmaWgvHh02ETcSs6FBaGqE6UjkFCmkOVZbvKGvPn4Gr5eEs/mVyVA2LKSSC
vkkggwN4mJinloK1tW8VeUaNqwq0LLGVtmdOiwKLSKZlqRnJwRIkOnyqS8OLMRWC
BmCAAszFVuevIeGIM9oHZeKXxI7RsqcP06u88altFv714Uouc37Q8SHS8tSs1D2d
7xYlUzJNQXgJU0Uhu6ySuAzo5ADx7BdGpULM2pM2/i5+QzNkq1u1PuJBA2/uAW57
V5J480Z3IONzV0St+P0zT92/T2uL/wCsGPfZ3l9EnUeFv2k5LUEaYjvGdnN+RdyW
NrnqeXIdMGK49HtOvYehXL9wELdclZ2lQAJ+yD/lIdQcNdAHgVYU9IwKlKvLzCCJ
Cccwf2PPCNUMm1Gmuxk/rZf8C4DgeYPI+ntgwY7vqjo/Sep7U56HvUbOphgOYmCC
Pggx4ihrGRcsn2mkYbI8vCrcQGz2liqatffODQt7q8d5ovfzvh5p+TajUnUzv0Uv
+VuC5HkOg9fbBgxknTvp1o2bktczGuXRb4VmEfcAGPgEUdey7irCwJp6kZGDTpVJ
eXQQ4SCwA/Z88GDBikbVpLKLatKFVRAA2AA4AH6pQSSZNf/Z
</FileData>
<FileName>smiley.jpg</FileName>
<FileType>jpg</FileType>
</Foto>

It seems to be encoded with base64, but as soon as i try to decode it using the PHP function base64_decode(), i don't get a valid output back. I have found some additional data back in the XSD-file, which is automatically generated when you make an XML-export in Access. There it seems to refer to base64, so i don't know what's going wrong.

<xsd:element name="FileData" minOccurs="0" od:jetType="oleobject" od:sqlSType="image">
<xsd:simpleType>
<xsd:restriction base="xsd:base64Binary">
<xsd:maxLength value="1476395008"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>

I have tried the following code, to get the image back ($data is filled with the encoded stuff from the XML-export):

<?php
header('Content-Type: image/jpeg');
echo base64_decode($data);
?>

I have looked on several websites, but i can't find a solution for this issue. It seems that Access is also compression the data, because it's impossible to get the original file back. I hope someone on StackOverflow will have a solution for this issue!

The main goal is to export approx. 2500 members (with photos attached) from Access 2007. The data can be exported easily, but it's all about the photos which make it complicated.

SCA
  • 23
  • 3
  • Are you aware of the fact that the Attachment field data type was added to the ACCDB format for compatibility with Sharepoint and for no other reason? If you can program, you really shouldn't be depending on a multi-value field like this, because it will just get in the way of working with the data. Since you're not using Sharepoint, there's really no reason you should be using an Attachment field at all. Plus, storing binary files in a database is very often a bad idea to begin with. – David-W-Fenton May 28 '11 at 20:53

1 Answers1

4

It's seems, that 1st 20 bytes of decoded file is some service info.

Try something like this (this is not ideal, but you understand the idea):

echo substr(base64_decode($data), 20);
gaRex
  • 4,144
  • 25
  • 37
  • Hi, I would kindly thank you for this solution, i have just tried a few images and it works excellent! I have been working on this issue quite some hours, so i am happy that you found the perfect solution! – SCA May 28 '11 at 20:22
  • HI, I want to export data from access db of 2007 and each record has photo and used unixODBC with PHP now I can get the data but couldn't get the photo, and my question is how to export data from access in xml format ? – jamal Jun 01 '17 at 09:26