CakePHP and Access sitting in a tree...

Introduction

A mentioned in a previous post, I'm currently working on a CakePHP powered website. One of the requirement for the project was the ability to create reports using a 3rd party reporting product.

We looked at several options but finally settled on Microsoft Access because a) the client already had it, and b) they had some experience of using Access to generate reports so the learning curve was less steep.

Getting the Data into Access

The CakePHP application uses a MySQL database so the natural step was to use ODBC (Open Database Connectivity) to link the tables into an Access MDB (Microsoft Database). From there, the client could use the Access GUI (Graphical User Interface) to create custom queries and reports.

I won't cover installing the MySQL ODBC Driver (otherwise know as MySQL Connector/ODBC) and linking the tables as there's plenty of documentation on the web that covers this step, and the instructions are slightly different for each version of Access.

Linking tables via ODBC is trivial in Access, but ODBC doesn't provide any schema information to Access for it to build the relationships between the (in this case 30+) tables. As the application is still in development the schema, although solid, may be subject to change. Adding the relationships between 30+ tables by hand using the relationship editor is both very time consuming and more importantly, very susceptible to human error.

Couple that with the need to keep updating the relationship diagram in access to reflect any changes in the MySQL database and you soon see this is going to be a nightmare to do by hand. Sounds like a job for automation!

It's been quite a while since I'd done any VBA (Visual Basic for Applications), the last being back in the days of Access2000 but it didn't take too long to get up to speed.

Automate it

My CakePHP application follows the CakePHP database naming conventions which might seem a little odd to someone who is used to Access, but I find them very intuitive.

• Tables have plural names (posts, programmes, cities, people)
• Primary keys are named 'id'
• Foreign keys are named the singular table name followed by _id (post_id, programme_id, city_id, person_id)

The only potentially tricky bit is inflecting the singular foreign key names to their plural table names. A simple "plural" function takes care of that. In this case, none of my tables had 'non-standard' plural names (all plurals were the singular with 's' appended: post_id -> posts, project_id->projects, etc.).

With this information, it was relatively simple to create a VBA function that runs through all the fields in the linked tables and builds relationship objects that relate the foreign keys to their respective tables:

The Code

Function buildRelations()

Dim rel                 As Relation ' the relationship we are building
Dim tbl                 As TableDef ' the table we are processing
Dim fld                 As Field    ' the field we are processing
Dim strForeignTable     As String   ' name of the foreign table
Dim intRelId            As Integer  ' counter used to create unique relationship names

  ' remove any exisiting relationships

  Do While CurrentDb.Relations.Count > 0

      CurrentDb.Relations.Delete (CurrentDb.Relations(0).Name)
    
  Loop


  ' rebuild relationships
  intRelId = 0

  For Each tbl In CurrentDb.TableDefs

      For Each fld In tbl.Fields
    
          If Right(fld.Name, 3) = "_id" Then
        
              ' found foreign key
            
              ' generate foreign table name (plural)
              strForeignTable = pluralise(Left(fld.Name, Len(fld.Name) - 3))
            
              ' build the relationship object
              intRelId = intRelId + 1
            
              Set rel = CurrentDb.CreateRelation("rel" & intRelId, tbl.Name, strForeignTable, dbRelationDontEnforce)
              rel.Fields.Append rel.CreateField(fld.Name)
              rel.Fields(fld.Name).ForeignName = "id"
            
              ' append the relationship to the database relationship collection
              CurrentDb.Relations.Append rel
            
          End If
        
      Next
        
  Next

  MsgBox "Relationships have been rebuilt"

End Function

Private Function pluralise(str As String) As String

  pluralise = str & "s"

End Function

You may need to expand the pluralise() function to deal with odd pluralisations (city_id->cities, person_id->people) in your own schema.

One word of caution. If you use Access' Query builder to build queries with linked tables, Access 'helpfully' tried to guess the primary and foreign keys for you which will mess things up royally. You can switch off this feature by unchecking 'Enable AutoJoin' in the Access Options dialog.