Bottlenecks of MS Access to MySQL Migration

MS Access to SQL Server Migration | Convert Access to SQL Server

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.