Access 2003 Database Upgrade to SQL Server 2005

Is there a wizard or established procedure to upgrade an Access 2003 database to SQL Server 2005 (either standard or express)?

Hi.

I would use the import wizard (right-click a database, tools->Import data).
Also, the MS Access Upsizing wizard should work.

Happy sizing.|||Hi,

I am new to VB2005 and SQL Server 2005 Express, so please be patient. I want to upgrade one of my MS Access 2003 databases. I have the Express version of SQL server. Could you be more specific to explain how to convert it to SQL Server 2005 Express version please? Thank you.
|||

Hi Athena,

There are a bunch of resources about migrating from Access to SQL Server that you should probably look over before you do this. A good page to start on is http://www.microsoft.com/sql/solutions/ssm/access/accessmigration.mspx. One of the questions you need to answer is why you want to move to SQL Server.

Specific information about using the Upsizing Wizard, which the tool I suggest you use is available in this paper: http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx as well as in the Access help. Even though the paper talks about SQL 2000, the Upsizing Wizard is the same and all the information in the paper still applies to SQL 2005.

One special consideration for SQL 2005 Express is that you will need to enable TCP/IP connection in order for Access to talk to SQL Server. This is off by default in Express Edition. You can configure this using the SQL Configuration Manager as documented at http://msdn2.microsoft.com/en-us/library/ms181035.aspx. At the same time you will likely also want to Start the SQL Browser as this will let you find the named instance of your server. (Assuming you did a default installation, your server is named <machinename>\SQLEXPRESS.)

It is best to do all this with Access and SQL Express installed on the same computer. If you are working between two different computers you have to consider if the computer with SQL Express installed on it is running a firewall, such as the Windows XP Firewall. The firewall will likely block communication between SQL Express and other computers, so you need to create an Exception for both SQL Server and the SQL Browser. You can find more information about how to do this in the SQL Express team blog at http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx.

Hope this helps,

Mike