MS Access Blob to MYSQL

Posted on 2007-10-03
Last Modified: 2013-12-13
Six years ago I built an .asp based site where teachers could upload their lesson plans into an MS Access Database .  This was my first site where I utilized a database and while it was my best work at the time in hind-sight I would have done a lot of things differently.


Currently I am developing a .php based site where teachers can upload their lesson plans , but the files will be stored in a directory and the file path will be stored in a sql database that I administer with phpmyadmin 2.10.2.


The problem I am having is that I would like to take all of the files that are stored in the Access database as BLOBs on the first site and save them to a directory for my new site.  I think I have figured out how to move some of the database information; I can move author, lesson_name, keywords, file_path, etc, but I cannot figure out how to get all of the files stored as BLOBs out of Access and into a directory.  Currently the only way I have of doing this is going to my site an where all of the files are displayed and I can save them one by one and rename them, but there has to be a better way, an automated way to deal with over 1700 files.  I tried DB Blob Editor and MS Access Extract Images, but with no luck.


The actual database is too large to email so I have attached a copy of the database with only 10 records in it so that you can see the structure and layout. My server  blocks .mdb file attachments so you will have to rename the attached file with the .mdb extension.  The table is fairly intuitive, but&


File Name = The name of the file they uploaded

File Size = Numeric file size

File Data = The Long Binary Data, the actual file

Content Type = The type of file

Type of File = Either a lesson plan, test, quiz, etc&

Any help would be appreciated..

Thanks, Dan

Question by:dk4210
    LVL 24

    Accepted Solution

    Probably the best way to do it is to

    1. Make the access database a system DSN (
    2. Connect to it by PHP (
    3. have PHP save the files in systematic way.

    if (!$conn)
      {exit("Connection Failed: " . $conn);}
    $sql="SELECT * FROM table";
    if (!$rs)
      {exit("Error in SQL");}
    while (odbc_fetch_row($rs))
    $handle = fopen(odbc_result($rs,"file name"), "w");
    fwrite($handle, odbc_result($rs,"file data")) ;

    Expert Comment

    I had a similar Problem reading Office Documents in LongBinary format. my Solution was a bit diffrent, and so probably interesting for others:

    if (!$conn) exit("Connection Failed: " . $conn);

    $query = "set textsize 2147483647";
    $query = "SELECT * ";
    $query .= "FROM tblVAttach ";
    $query .= "WHERE VA_VZaehler=".$_GET['verbid']."; ";

    if (!$rs) exit("Error in SQL");

    while (odbc_fetch_row($rs)) {
       $fileHandle = fopen("temp\\".odbc_result($rs,"VABezeichnung").odbc_result($rs,"VAExt"), "wb");
       while (($chunk=odbc_result($rs,'VAAttach'))!==false) {
       //do something with the file
    // change to ODBC_BINMODE_CONVERT for comparison


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    MySQL 11 48
    Basic Membership Section (Wordpress) 2 42
    GPS save in database 19 20
    CodeIgniter XSS confusion 5 18
    Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to dynamically set the form action using jQuery.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now