Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1686
  • Last Modified:

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

thanks
0
huy302
Asked:
huy302
3 Solutions
 
GawaiCommented:
THERE IS THIRDPARTY SOFTWARE FOR THAT . but for php code u need to wait
0
 
albee01Commented:
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.
0
 
soapergemCommented:
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.

<?php

//      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);
mysql_select_db($dbname);

$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"
      }
}
else
{
      echo "<p>No data was exported</p>$endl";
}

?>
0
 
huy302Author Commented:
Excellent answers from you three

I can not expect something more

thanks alot
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now