MS Access is a user-friendly database management system having intuitive interface and rich documentation. Due to those benefits even non-professional users can work with this DBMS. 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, ByVal colName 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 values must be converted from #MM/DD/YY# to MySQL form ‘YYYY-MM-DD’
- convert MS Access boolean values to MySQL so that ‘Yes’ becomes b’1′, ‘No’ becomes b’0′
Finally, all built-in system functions must be converted from MS Access to MySQL equivalents according to the table below:
| MS 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.
There are special tools to automate migration of primary database entries from MS Access to MySQL. One of these tools is offered by Intelligent Converters, a software company providing solutions for database migration and synchronization for more than 20 years.
This tool supports all versions of MS Access files and works with all modern version of MySQL including forks (MariaDB and Percona) and DBaaS platforms (Azure, Amazon RDS, Google Cloud). MS Access queries are converted into MySQL views. For those cases when direct connection to MySQL is not allowed, the program can migrate data from MS Access to MySQL script file. Support for command line arguments allows to call the tool from scheduling scripts and run the migration from 3rd party software.
Be the first to comment