mouldy.org

Converting from Access to PHP/MySQL can be a real drag, done by hand. Here's how I automate away much of the work.

Use CakePHP

CakePHP is a framework for PHP which implements the MVC (Model-View-Controller) pattern. It's fairly robust and well-designed. It actually fits Access quite well - Access forms and reports become CakePHP views, queries become controllers, and macros can be translated to PHP or JavaScript code as appropriate. CakePHP in general favours putting variables in arrays, so you can use the exact same names that the original Access database used. I use conventions like storing query results in $query, a single record in $r, and so on.

Tables

Tables are pretty easy to port across. There's a MySQL ODBC driver, so Access can talk directly to MySQL via Linked Tables. The mechanism to perform this is a bit non-obvious; go to File->Get External Data->Link Tables. In the Files Of Type field, scroll down and choose ODBC Databases. You can then configure the MySQL database as described in the MySQL ODBC driver documentation. Copy the structure and data across, and you're done.

Converting Access function syntax to PHP

It's surprisingly easy to convert Access expressions to PHP. I achieved this through two mechanisms: regular expressions to fix the syntax, and an emulation library that implements Access functions in the PHP namespace.

Regexes

The most complicated expressions that I saw were something like:

[Calculated Field] =IIf([Some Field] = "foo", "Yep, it's foo", "No, use this: " & [Another Field])

The field names need to be converted to PHP syntax. With CakePHP, I'm storing all of the field names in a dictionary, so I want [field] to become $query['Field']. The regex for this is:

s/\[\([^'].\{-}\)\]/$query['\1']

Incidentally, all of the regexes here are intended for vim. The syntax will vary slightly depending on whatever regex processor you're using. Notably, the mechanism for greedy matching will be different, because vim is weird. Vim's regex for "match as few characters as possible" looks something like:

.\{-}

grep and emacs use a different syntax; I presume that you can figure that out for yourself.

When implementing queries, I often needed to copy entries directly between two dictionaries. I listed the field names one per line and applied the following regex.

s/.*/^I^I^I$query['&'] = $r['&'];

This turns:

Field Name

into:

            $query['Field Name'] = $r['Field Name'];

To convert the 'Is Not Null' construct into something that PHP understands, I used:

 s/\(\$[^$]\{-}\) Is Not Null/!is_null(\1)/g

This changes:

$blah Is Not Null

into:

!is_null($blah)

Watch out for '=' used as an equality test. It would be easy to write a regex to replace all of them, but you really need to know the context that the '=' operator is being used in. I performed this check manually and replaced it with '==' where appropriate.

The '&' concatenation needs to become '.'.

You also need to stick semicolons on the end of each line. I'm sure you can handle that yourself.

Emulation library

Rather than rename all of the functions that Access has and PHP doesn't, I just created aliases for them. PHP doesn't have equivalents for a lot of Access functions - notably inline flow control - so an emulation library can fix that too.

You can download the library here.

There's a VBA Switch() function which conflicts with the 'switch' construct in PHP, so I renamed it to vbaSwitch. Again, I used a regex to convert these references automatically:

 s/Switch/vbaSwitch/g 

The Between...And construct is a bit tricky in PHP. I wrote a function for it in my library, so it needs restructuring. I only saw it once during my conversion project, so I didn't bother writing a regex to convert it.