Import Access data into MySQL using PHP

Question

How to load .mdb file into mysql database using php code.

I have a .mdb file and location is /example/employee.mdb. I need to read record from attendance table and insert it into mysql's emp_attendance table.

What will be PHP logic for that?

Answer 1

Like most relational databases, PHP can connect to MS Access and MySQL with PDO. Consider doing so with a dual connection with MySQL. No external, third-party software is required. Below example uses a mock table and fields. Adjust as needed:

Requirements include (no MSAccess.exe installation required):

1. php_pdo.dll extension enabled in .ini file
2. MS Access ODBC Driver (usually already pre-installed)
3. Any Windows PC (pre-built with Jet/ACE SQL engine -Access' underlying engine)

PHP Dual PDO Connection

$accdatabase="C:\Path\To\database.accdb";

$host="localhost";
$mydatabase="****";
$username="****";
$password="****";

try { 
  # OPEN BOTH DATABASE CONNECTIONS
  $accConn = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBq=$accdatabase;Uid=Admin;Pwd=;"); 

  $myConn = new PDO("mysql:host=$host;dbname=$mydatabase",$username,$password); 
  $myConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $sql = "SELECT * FROM emp_attendance"; 
  $accstmt = $accConn->query($sql);
  $accstmt->setFetchMode(PDO::FETCH_ASSOC); 

  // FETCH ROWS FROM MS ACCESS
  while($row = $accstmt->fetch()) { 
    // APPEND TO MYSQL
    $mystmt = $myConn->prepare("INSERT INTO emp_attendance (empid, `date`, status, notes) VALUES (?, ?, ?, ?)");

    # BIND PARAMETERS 
    $mystmt->bindParam(1, $row['empid'], PDO::PARAM_STR, 50); 
    $mystmt->bindParam(2, $row['date'], PDO::PARAM_STR, 50);    
    $mystmt->bindParam(3, $row['status'], PDO::PARAM_STR, 50); 
    $mystmt->bindParam(4, $row['notes'], PDO::PARAM_STR, 50);

    # EXECUTE QUERY
    $mystmt->execute();
  }
} 
catch(PDOException $e) {         
    echo $e->getMessage()."\n"; 
    exit; 
}

// CLOSE CONNECTIONS
$accConn = null;
$myConn = null;