Export to mdb file

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

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

THERE IS THIRDPARTY SOFTWARE FOR THAT . but for php code u need to wait
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: http://dev.mysql.com/downloads/query-browser/1.1.html 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.
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";


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
huy302Author Commented:
Excellent answers from you three

I can not expect something more

thanks alot
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.