A Look at How to Migrate an Access Database to SQL Server Part 1 of 2
Recently I was tasked with migrating a Microsoft Access 2010 database to SQL Server 2008 R2 while preserving the form functionality built into Access. While the migration using Microsoft Access’ ‘Upsizing Wizard’ was easy enough for a majority of the objects, I found that a few of the more complex Access queries and tables needed manipulation or outright manual conversion to objects that both SQL Server and Access agreed with. In this two part blog series I will go through how to use the upsizing wizard to migrate an Access mdb file to an Access Project file connected to a SQL Server backend. In the second part of this series I will go over some possible roadblocks and issues that could arise, as well as their solutions.
This sample application will have a a simple form opening two parameterized queries, one of which having a few columns formatted as lookups to other tables. We will be migrating both tables and queries, while retaining the form and and VBA code tied to the form.
- First we will open the the sample access_to_sql_tutorial.mdb file and select the ‘SQL Server’ button in the ‘Move Data’ group of the ‘Database Tools’ Tab.
Note: Make sure there are no forms or objects open when using the Upsizing Wizard, as those objects may fail to be migrated to SQL Server.
- Select ‘Create New Database’, then select ‘Next’.
- Enter the destination SQL Server name and instance name, and name the database to be created. Select ‘Next’.
- The next screen will prompt to select the tables to export and link to SQL Server. Select the relevant tables then select ‘Next’.
- The default values in the next screen should be sufficient for simple migrations. The only option which may be relevant would be the checkbox: ‘Only create the table structure; don’t upsize any data’. Select ‘Next’.
- In the next screen, you will get the option to either link all of the tables to SQL Server to the existing application (mdb), or create a new ‘Access Project’ file which truly functions as a client / server application. For this tutorial we will go with the later option ‘Create a New Access client/ server application’. Give the new application a name and click ‘Next’
- If prompted if you want to save an unencrypted password select ‘yes’. For the purposes of this simple demo we will not worry too much about security. Though there are many security options available for Access Project (adp) project types, for more information on the security features available in Microsoft Access go to: http://office.microsoft.com/en-us/access-help/introduction-to-access-2010-security-HA010341741.aspx
- In the next screen you can choose to either open the new adp file (if you selected the option to create a new project file in the previous screen), or keep the current pre migrated access file open. Select ‘Finish’.
- The next screen will show the progress of migrating to SQL Server.
- When completed, you will be prompted with the Migration report which will list every object migrated and the migration status.
- After the migration has finished you will be brought to the adp project. You can now go into SQL Server Management Studio and verify the new SQL Server database has been created.
- You can see from the list of database objects that the tables have been successfully migrated, though the ‘PRODUCT DATA’ query has not. Simple non parameterized queries are migrated as views in SQL Server, though since this query used parameters it would have to be migrated as am stored procedure or table-valued function.
In the next blog post in the series I will discuss how to migrate the parameterized ‘PRODUCT DATA’ Access query to a compatible table-valued query in SQL Server, and I will also go into possible pitfalls and roadblocks that may appear during migration.
Note: Attached to this article is the sample Access 2010 application used during this tutorial.