This is the easiest way I know how to get data from a MySQL database to and Microsoft Access database. Its not automatic, but its by far the best way to move tables from MySQL to Access. A single function generates a CSV file (with Access string escaping) that can be easily downloaded and "imported" into Access.
By : woodys
<?
function mysql2access($dbconnection, $tablename,$outfile)
{
//this function produces a CSV text file (with Microsoft string escaping) that can be processed by Microsoft Access's data import funcitonality
//parameters:
// $dbconnection: this is a the MySQL connection handle returned by mysql_connect()
// $tablename: CSV files can only handle one table at a time, so we pass the table name here
// $outfile: output filename (remember that the subdir needs permissions that PHP can write to) (i.e. CHMOD 777)
//open the the output file
$fp=fopen($outfile,"w");
$res=mysql_query("select * from $tablename",$dbconnection);
$nr=mysql_num_rows($res);
//enumerate field types (and buffer them in an array for performance reasons)
$nf=mysql_num_fields($res);
for ($b=0;$b<$nf;$b++)
{
$c=mysql_field_type($res,$b);
switch($c)
{
case "int":
case "real":
case "unknown":
case "timestamp":
case "numeric":
case "decimal":
default:
$is_numeric[$b]=true; break;
case "string":
case "date":
case "datetime":
case "time":
case "blob":
case "char":
case "varchar":
case "text":
$is_numeric[$b]=false; break;
}
}
for ($a=0;$a<$nr;$a++)
{
$row=mysql_fetch_row($res);
for ($b=0;$b<$nf;$b++)
{
$c=mysql_field_type($res,$b);
if ($is_numeric[$b]==false)
{
$s=access_escape_string(strval($row[$b]));
fputs($fp,"\"$s\"");
}
else
{
fputs($fp,strval($row[$b]));
}
if ($b<($nr-1)) fputs($fp,", ");
}
fputs($fp,"\n");
}
fclose($fp);
}
function access_escape_string($s)
{
$s2="";
$sl=strlen($s);
for ($a=0;$a<$sl;$a++)
{
$c=substr($s,$a,1);
if ($c=="\"") $c="\"\"";
$s2.=$c;
}
return $s2;
}
?>
/* example */
<?
include("mysql2access.h");
//mysql2access needs the db connection handle
$con=mysql_connect("localhost","username","password");
$db=mysql_select_db("dbname",$con);
mysql2access($con,"promo","/home/sites/site90/web/backup/promo.txt");
mysql2access($con,"orders","/home/sites/site90/web/backup/promo.txt");
mysql2access($con,"users2","/home/sites/site90/web/backup/users2.txt");
mysql2access($con,"mailinglist","/home/sites/site90/web/backup/mailinglist.txt");
?>
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More Database Code Articles
More By Codewalkers
 developerWorks - FREE Tools!
|
As businesses grow increasingly dependent upon Web applications to provide services to customers, employees and partners, these complex applications become more difficult to secure. Although traditional security solutions protect Internet infrastructure layers, they do not guard against HTTP and HTML attacks. Many organizations that conduct security testing still deploy applications that allow attackers to manipulate their logic and wreak havoc on their business. To mitigate this risk, development and delivery teams must address Web application security throughout the lifecycle, addressing the many layers detailed in this paper.
FREE! Go There Now!
|
|
|
|
Poor Requirements Management capabilities in an Enterprise have been linked to excessive project failures, escalating IT costs, and failure to deliver competitive advantage into the marketplace. Join Brianna M Smith from IBM Rational and learn about how successful organizations align IT and Business stakeholders through collaborative processes and tools for effective requirements management, and how an integrated approach across the IT lifecycle can provide unparalleled visibility and traceability to ensure that project teams are delivering on the business vision by "doing the right things" and "doing things right."
FREE! Go There Now!
|
|
|
|
Learn to enable users to both rate existing animations and to combine existing animations into new snippets. This is the third in a series of three tutorials that chronicle the building of a site that enables collaborative discussion and animation building using Domino and OpenLaszlo.
FREE! Go There Now!
|
|
|
|
Rational Modeling Extension for Microsoft .NET enhances usability for code generation supporting a more intelligent refactoring. The latest enhancements enable organizations with Java and .NET systems and software development maintain architectural integrity across heterogeneous platforms.
FREE! Go There Now!
|
|
|
|
Regression testing -- in which code is thoroughly tested to ensure that changes have not produced unexpected results -- is an important part of any development process. But many testing environments neglect the terminal-based applications that still form the backbone of many industries. In this tutorial, you'll learn how the Rational Functional Tester Extension for Terminal-Based Applications works with other Rational Functional Tester to help test terminal-based applications quickly and easily.
FREE! Go There Now!
|
|
|
|
Get a free trial download of IBM Lotus Forms V3.0 (formerly Workplace Forms), which provides a zero-footprint eForms solution to help you automate and move forms-based business processes off the desktop and onto the Web. With Lotus Forms, you can extend applications beyond the firewall by creating a single electronic form document ready for use in both thick and Web 2.0 thin client format.
FREE! Go There Now!
|
|
|
|
You can now evaluate IBM Rational Asset Manager V7.0 online without installing or configuring it on your own system! Rational Asset Manager helps create, modify, govern, find, and reuse any type of development assets, including SOA and systems development assets. Rational Asset Manager helps you reduce software development costs and improve quality by facilitating the reuse of all types of software development-related assets. Visit developerWorks to learn more about this product and register to explore its capabilities online.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to try the IBM SOA Sandbox for people. The SOA Sandbox for people provides a trial environment with the necessary tooling and components required to enable consistent human and process interaction and collaboration, showing how you can improve user experience and business productivity.
FREE! Go There Now!
|
|
|
|
In this webcast, IBM Rational will discuss the importance of Web application security and will share techniques and best practices to introduce application security testing into current QA processes including: understanding common security vulnerabilities and techniques to integrate security testing with defect tracking and remediation systems in an effort to safeguard sensitive online information.
FREE! Go There Now!
|
|
|
|
WebSphere Process Server delivers a unique integration framework that simplifies existing IT resources. Often, as IT assets grow to support business demand, so too does their complexity and manageability. In this webcast, we’ll discuss how WebSphere Process Server helps deliver an SOA infrastructure that provides a common model to orchestrate, mediate, connect, map, and execute the underlying IT functions. Discover how WebSphere Process Server simplifies integration of business processes by leveraging existing IT assets as reusable services without the complexities of traditional integration methodologies.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
/ 3 
