Export to mdb file

Posted on 2006-04-26
Last Modified: 2012-08-14
Hi, I want to use php code to export some tables of my MySQL database to a Microsoft Access file (*.mdb)

Is there any good idea to do that?

I'll really appreciate some sample codes

Question by:huy302
    LVL 20

    Assisted Solution

    THERE IS THIRDPARTY SOFTWARE FOR THAT . but for php code u need to wait
    LVL 1

    Assisted Solution

    There are a couple of relatively easy ways to do this depending on what you want to do. One in PHP and one not.

    1. Use PHP to write a text file. Use php to print the data in a comma separated values (csv) or tab delimited (us \t)format. It should match the layout of your database. You can add headers to tell the browser to download the file or simply view the source and save that to a file. Then in Access, open the database. Under the file menu select get external data, import. Locate the file (you'll need to change the type) and work through the wizard selectting that it's a delimited file and the actual delimiter (tab or comma).

    2. If you just want to view the live data in Access for say reporting purposes, you can use Access as a front end for MySQL, without using PHP. You'll need the odbc connector from here: Set up a dsn (varies with your version of windows) to use the mysql odbc connector. Then open your access database and select file, get external data, link tables. The link_type will be odbc databases. Find the dsn you created and open it. You should see the mysql tables. Click on one or more and press ok to import them. You can work with them just as if they are native tables in your Access database. The downside to this is that the database can't be moved to another machine very easily.

    Good Luck.
    LVL 6

    Accepted Solution

    Microsoft Access uses a proprietary format for their MDB files. This means that it would be illegal for us to give you any code that would convert your data directly into these formats. Thus, gawai is correct when he says that you need third-party software. However, as pointed out by albee01, you could export your data into a "free format"--a format that Microsoft Access understands; you could use PHP code to export all of you data as a CSV file, for instance (this is a very simple, free format that MS Access is capable of understanding). Then, you could simply open up Microsoft Access and "import" this new file you have created. By pressing File -> Save As, you would end up with a nice and clean MDB file. But, as pointed out, to execute all of this directly without having to open up MS Access, you would NEED to be third-party software--software made by people who have already paid all the fees to Microsoft, and so they're allowed to do this.

    The following is code that will export data from "table_name" in your database as a CSV file. You would simply upload this file to your webserver, open it in a web browser, and it would prompt you to download the CSV file. Then, you could open that in MS Access to convert it yourself. Please be sure to change all the configuration settings (your database username, password, etc.). You can probably leave the value of "delimiter" and "endl" alone, though.

    Also, change the MySQL query to reflect what data you actually want to retrieve from your database.


    //      Configure these options
    $filename = 'sql_export.csv';
    $delimiter = ',';
    $endl = "\r\n";
    $dbhost = 'localhost';
    $dbuser = 'your_username';
    $dbpass = 'your_password';
    $dbname = 'name_of_your_database';
    $sql_query = 'SELECT * FROM table_name';

    mysql_connect($dbhost, $dbuser, $dbpass);

    $result = mysql_query($sql_query);
    $rowset = mysql_fetch_array($result);

    if ( count($rowset) )
          header("Content-Type: text/x-delimtext; name=\"$filename\"");
          header("Content-disposition: attachment; filename=$filename");

          $line = '';
          //      First output column names
          foreach ( $rowset[0] as $column => $value )
                $line .= $delimiter . $column;
          echo substr($line, 1) . $endl;      //      example: "Col1,Col2,Col3"
          //      Then output the data
          for ($i = 0; $i < count($rowset); $i++)
                $line = '';
                foreach ( $rowset[$i] as $value )
                      $line .= $delimiter . $value;
                echo substr($line, 1) . $endl;      //      example: "Val1,Val2,Val3"
          echo "<p>No data was exported</p>$endl";


    Author Comment

    Excellent answers from you three

    I can not expect something more

    thanks alot

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
    Part of the Global Positioning System A geocode ( is the major subset of a GPS coordinate (, the other parts being the altitude and t…
    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 count occurrences of each item in an array.

    732 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

    17 Experts available now in Live!

    Get 1:1 Help Now