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)
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
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.
