Figuring out how to talk to MS Access from PHP

_msaccess_from_php.md

Installation

Downloaded Windows XP / IE8 image from http://www.modern.ie/en-us/virtualization-tools

curl -O "https://az412801.vo.msecnd.net/vhd/IEKitV1_Final/VirtualBox/OSX/IE8_XP/IE8.XP.For.MacVirtualBox.ova"

• Virtualbox -> Import > "IE8.XP.For.MacVirtualBox.ova"
• Changed it to have 2GB RAM, 2 CPU, 2d acceleration
• Installed guest additions, messed with display settings (found that seamless mode is most stable)
• Installed google chrome: http://www.google.com/chrome/intl/en/landing_chrome.html
• Install gvim (for winows): ftp://ftp.vim.org/pub/vim/pc/gvim74.exe
• Installed PHP: http://windows.php.net/downloads/releases/php-5.3.27-nts-Win32-VC9-x86.msi

Resources:

• http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html
• http://stackoverflow.com/questions/1605473/php-and-microsoft-access-database-connection-and-crud
• http://www.sitepoint.com/using-an-access-database-with-php/

Sample access database:

• http://databases.about.com/od/access/a/zipcodedatabase.htm (download link)
• http://databases.about.com/library/databases/zipcodes.mdb (description)

VBScript:

• http://windowsitpro.com/windows/connecting-microsoft-office-access-database-vbscript
• http://technet.microsoft.com/en-us/scriptcenter/dd940112.aspx | VBS Scripting: Your First Steps

Tips for using Windows:

• http://superuser.com/questions/25538/what-is-the-windows-equivalent-of-wget (install powershell to get wget)
• php -i | findstr "PDFO" - findstr is equivalent of grep

Running from Linux (alternative to this approach):

• http://stackoverflow.com/questions/13473149/pdo-how-to-access-microsoft-access-file-on-linux
• http://www.coderelic.com/2011/09/how-to-work-with-microsoft-access-mdb-databases-with-php-on-linux/

access.php

<?php

// otherwise PHP gives scary warning messages about default.timezone not being set.
date_default_timezone_set('America/Los_Angeles');
 
// not sure this got pickd up; I needed to edit C:\Program Files\PHP\php.ini
ini_set('display_errors', 1);
 
$mdbFilename = $_SERVER['USERPROFILE'] . '\My Documents\test-php\zipcodes.mdb';
 
echo "File: $mdbFilename\n";
 
try {
  $db = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)}; Dbq=$mdbFilename;Uid=Admin");
  echo "PDO connection object created\n";
 
  $sql = "SELECT * From Class";
  $stmt = $db->query($sql);
  $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
 
  echo "$sql\n";
  print_r($result);
 
} catch(PDOException $e) {
  echo $e->getMessage();
}

access.vbs

' run me with ...

Wscript.echo "My very first script."

Set conn = CreateObject("ADODB.Connection")
' Connect to the database
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\IEUser\My Documents\Downloads\zipcodes.mdb"

conn.Open strConnect

StrSQL = "Select `Zip Code` from `Zip Codes`"
Set rs = conn.Execute(StrSQL)
Do While not rs.EOF
    wscript.echo "The information is " & RS(0)
'    intFAQNumber = RS(0)
    rs.MoveNext
Loop