Migrating from Access to a High-End DBMS{2}

by Jonathan N
This article talks about the users which are ready to make the next step forward and advance from the simplicity of Microsoft Access to a much more multifaceted Microsoft SQL server.  The author speaks of the many reasons why you should be willing to migrate your shared access database to a client/ server.  Access is less able to manage file sharing efficiently. When network traffic increases, access’s responsiveness decreases.  The author also states that Access can handle databases only up to 2 GB and has only  a few high availability features. This could be a big reason why companies will look for other solutions, veering away from Access. 2GB of information is very minimal for some organizations database. Microsoft Access does not backup and restore files dynamically while the database is in use.  This makes using a client/ server for your database much beneficial  because the desire for better security and a dynamic web back end makes it much worth it to go in the route to a SQL server. If you believe you need to make the approach t Sybase Adapative Server Enterprise, you should allow the phased implementations. You should follow these that are listed in Matthew Sarrel’s guidelines below to make the migration easier.


  • Back up your existing database and store the backup in a safe place.
  • Make sure your accounts have adequate permissions and you have the necessary passwords to all involved files and directories.
  • Gather and clearly document information on table structures, particularly field names, field types, default field values, table names, and table relationships. The Access Database Documenter (Tools | Analyze Documenter) is an invaluable resource for this.
  • Make sure all original field and table names comply with what the server requires in terms of length and legal characters and that all relationships in the original database are legal in the server version. You must understand table dependencies to know the order in which to migrate them: If you load a child before the parent, the migration wizard cannot populate the new tables.
  • Install the new database server and create the database and logs. You should allocate at least five times as much space for the server database as for each MDB file.
  • Create another backup.
  • Run whatever migration/upsizing wizard is appropriate. It’s usually a good idea to migrate the table structure and data in two separate steps, so that you don’t lose data as a result of a flawed table structure or relationship. Make sure to read status messages as they appear and print or export application logs afterwards, so that you can trace problems and correct them. Once you believe that table structures are correct, back up the database again.
  • Now rerun the migration/upsizing wizard to transfer only the data.
  • Write the SQL triggers that you’ll need for validation.
  • Rewrite data entry forms, queries, and reports into either a proprietary format or a combination of HTML and Java. (These last two steps will probably take longer than all other steps combined.)
  • Don’t forget to test your new database application!

Matthew Sarrell ( May, 2012) Migrating from Access to a High-End DBMS