Database Code
  Home arrow Database Code arrow MySQL to Access Transfer
Codewalker Forums 
  Tutorials  
Database Articles  
Miscellaneous  
Navigation Usability  
PEAR Articles  
Programming Basics  
Server Administration  
XML Tutorials  
  Reviews  
Database Book Reviews  
Linux Book Reviews  
Miscellaneous Reviews  
PHP Book Reviews  
PHP Software Reviews  
Server Admin Reviews  
SQL Tool Reviews  
  Code Gallery  
Content Management Code  
Contest Code  
Counters Code  
Database Code  
Date Time Code  
Discussion Board Code  
Email Code  
File Manipulation Code  
GUI Code  
Link Farm Code  
Miscellaneous Code  
Search Code  
Site Navigation Code  
User Management Code  
Mobile Linux 
App Generation ROI 
IBM® developerWorks 
Download TestComplete 
Forums Sitemap 
Weekly Newsletter 
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
DATABASE CODE

MySQL to Access Transfer
By: Codewalkers
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 3
    2002-01-18

    Table of Contents:

    Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    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

     

    IBM® developerWorks developerWorks - FREE Tools!


    NEW! A Layered approach to delivering security-rich Web applications

    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!


    NEW! Best Practices in Integrated Requirements Management

    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!


    NEW! BlammoSplat: Build a community Web site of OpenLaszlo animations, Part 3: The community animation

    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!


    NEW! Rational Modeling Extension for Microsoft.Net

    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!


    NEW! Test terminal-based applications with Rational Functional Tester

    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!


    NEW! Trial download: IBM Lotus Forms V3.0

    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!


    NEW! Try IBM Rational Asset Manager V7.0 online!

    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!


    NEW! Try the IBM SOA Sandbox for People

    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!


    NEW! Webcast: Calling All Testers! Find Application Vulnerabilities Early in the Development Process Where they are Easier to Fix and Less Risky to your Business

    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!


    NEW! Webcast: WebSphere Process Server

    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!

    DATABASE CODE ARTICLES

    - Examples and Tools for Database Design
    - Relationships, Entities and Database Design
    - Modeling and Designing Databases
    - Data extract to Excel
    - Oracle database class 0.76
    - The opposite of mysql_fetch_assoc
    - On line Thermal Transmitance Calculation
    - pjjTextBase
    - PHP Object Generator
    - FastMySQL
    - RC4PHP
    - SQL function with integrated sprintf()
    - DB Interaction Classes v1.1
    - deeMySQLParser
    - CSV to SQL convertor





    © 2003-2009 by Developer Shed. All rights reserved. DS Cluster 3 Hosted by Hostway
    For more Enterprise Application Development news, visit eWeek