MS Access is very easy to use DBMS having intuitive interface and rich documentation. However, when some organization grows and collect more corporate data, it might be necessary to switch to more powerful and reliable database management system providing parallel access for many users and protecting the data with sophisticated security. MySQL is one of the best solutions for this task as it is easy to learn and use yet powerful enough to handle huge corporate scale data warehouses. That is why moving from Microsoft Access to MySQL becomes one of the most popular database migration tasks.
Although database migration from MS Access to MySQL is a quite straight forward process, there are some issues that require special attention:
- There is no direct mapping for some MS Access types such as ‘OLE Object’
- MS Access and MySQL have different sets of keywords, embedded functions
- MySQL has no equivalents for such MS Access objects as forms and reports
Data in MS Access column having type ‘OLE Object’ is wrapped in OLE serialization format that must be properly preprocessed before migration to extract actual image of data. For example, the Visual Basic code below extracts JPEGs from ‘OLE Object’ values:
Private Function GetImageFromOLEObject(ByRef row As DataRowView, ByValcolName As String) As Bitmap
Dim oImage As Bitmap = New Bitmap(“c:\picture.jpg”)
Try
If Not IsDBNull(row(colName)) Then
If row(colName) IsNot Nothing Then
Dim mStream As New System.IO.MemoryStream(CType(row(colName), Byte()))
If mStream.Len> 0 Then
Dim b(Convert.ToInt32(mStream.Len – 1)) As Byte
mStream.Read(b, 0, Convert.ToInt32(mStream.Len – 1))
Dim pos As Integer = 0
For idx As Integer = 0 To b.Len – 3
If b(idx) = &HFF And b(idx + 1) = &HD8 And b(idx + 2) = &HFF Then
pos = idx
Exit For
End If
Next
If pos> 0 Then
Dim jpgStream As New System.IO.MemoryStream(b, pos, b.Len – pos)
oImage = New Bitmap(jpgStream)
End If
End If
End If
End If
Catch ex As Exception
Throw New ApplicationException(ex.Message, ex)
End Try
Return oImage
End Function
Another important task of MS Access to MySQL migration is to make all SQL statements compatible with MySQL syntax.Follow the rules below to migrate MS Access queries and expressions to MySQL:
- replace MS Access operator ‘<>’ by MySQL equivalent ‘!=’
- convert MS Access concatenation operators ‘exp1 & exp2’ and ‘exp1 + exp2’ into MySQL ‘CONCAT (exp1, exp2)’
- all MS Access date constants must be converted from #MM/DD/YY# to MySQL form’YYYY-MM-DD’
- replace MS Access boolean constants ‘Yes’ by b’1′, ‘No’ by b’0′ in MySQL
Also, it is necessary to convert MS Access built-in functions into MySQL equivalents according to the table below:
| Microsoft Access | MySQL |
| asc | ascii |
| chr | char |
| date() | now() |
| iif(condition, expr1, expr2) | if(condition, expr1, expr2) |
| InStr(position, expr1, expr2) | locate(expr2, expr1, position) |
| int | floor |
| lcase | lower |
| lcase$ | lower |
| len | lenth |
| ltrim$ | ltrim |
| nz(expr1, expr2) | ifnull(expr1, expr2) |
| rtrim$ | rtrim |
| sgn | sign |
| ucase | upper |
| ucase$ | upper |
Forms and reports are migrated into PHP scripts bound to the appropriate tables. This task is quite complicated and requires deep knowledges of PHP programming, so it is not covered by this article.