Microsoft Access to SQL Server Upsizing Resource Center

Microsoft Access is a wonderful platform that empowers information workers and professional developers to create powerful solutions on the Windows desktop. Over time, good applications evolve and become more mission critical. At some point, you begin to consider migrating your Access application to a more powerful database platform. It could be based on scalability, security, the need for a web solution, legal requirements, performance, or a variety of other reasons. When that time comes, a natural option is Microsoft SQL Server.

Before you attempt to convert an Access database to SQL Server, make sure you understand:

• Which applications belong in Microsoft Access vs. SQL Server? Not every database should be modified.
• The reasons for upsizing your database. Make sure SQL Server gives you what you seek.
• The tradeoffs for doing so. There are pluses and minuses depending on what you're trying to optimize.
  • Make sure you are not migrating to SQL Server solely for performance reasons.
    • In many cases, performance decreases when an application is upsized, especially for relatively small databases (under 200 MB).
    • Some performance issues are unrelated to the backend database. Poorly designed queries and table design won't be fixed by upsizing.
  • Microsoft Access tables offer some features that SQL Server tables do not such as an automatic refresh when the data changes. SQL Server requires an explicit requery.

There are several options for hosting SQL Server databases:

• A local instance of SQL Express, which is a free version of SQL Server can be installed on each user's machine
• A shared SQL Server database on your network
• A cloud host such as SQL Azure. Cloud hosts have security that limit which IP addresses can retrieve data, so fixed IP addresses and/or VPN is necessary.

There are many ways to upsize your Microsoft Access databases to SQL Server:

• Move the data to SQL Server and link to it from your Access database while preserving the existing Access application.
  • Adjustments may be needed to support SQL Server queries and differences from Access databases.
• Convert an Access MDB database to an Access Data Project (ADP) that connects directly to a SQL Server database.
  • Since ADPs were deprecated in Access 2013, we do not recommend this option.
• Use Microsoft Access Web Apps in Access 2013.
  • With Office365, your data is uploaded into a SQL Server database hosted by SQL Azure with an Access Web front end
  • Appropriate for basic viewing and editing of data across the web
  • Unfortunately, Access Web Apps do not have the customization features comparable to VBA in Access desktop solutions
• Migrate the entire application to the .NET Framework, ASP.NET, and SQL Server platform, or recreate it on SharePoint.
• A hybrid solution that puts the data in SQL Server with another front-end plus an Access front-end database.
• SQL Server can be the traditional version hosted on an enterprise quality server or a free SQL Server Express edition installed on your PC
  • Read our Microsoft SQL Server Express Version Comparison and Free Downloads whitepaper for details

Here are some of the whitepapers we've written to help you make the right decision about converting and migrating Access databases to SQL Server.

• When and How to Upsize Microsoft Access Databases to SQL Server
• Microsoft Access or Microsoft SQL Server: What's Right in Your Organization? (Published on Microsoft's SQL Server Site)
• Database Evolution: Microsoft Access within an Organization's Database Strategy
  How to use Access strategically within an organization, and when/why to go to SQL Server

Microsoft SQL Server on Azure

• Microsoft Access and Cloud Computing with SQL Azure Databases (Linking to SQL Server Tables in the Cloud)
• Use Microsoft SQL Server Database Users and Permissions Instead of Server Logins Updated!
• Microsoft Azure Security Holes with SQL Server Databases Updated!
• Monitor SQL Server Usage on Microsoft Azure and Setting DTU Limits
• Convert Azure SQL Server Databases to Elastic Pools

Our Microsoft Access Resources and Help Center

• Microsoft SQL Server Home
• SQL Server Migration Assistant (SSMA) for Access, Version 7.7 (32 and 64-bit versions)
• Optimizing Microsoft Office Access Applications Linked to SQL Server

Not all Access databases should or will migrate to SQL Server. FMS offers a wide range of Access products to help you enhance and maintain your Access databases. Here are a few to help system administrators and database administrators better manage Access databases across a network or enterprise:

Make sure your Access databases are compacted, backed up, etc. on a regular schedule

Monitor who's going in and out of a database in real time

Centrally manage the deployment and launching of the right version of your Access database with the right version of Access for all your users

Our Professional Solutions Group offers consulting services to migrate your existing Microsoft Access databases to SQL Server. Our experienced staff has upsized Access databases to maximize your existing investment, take advantage of SQL Server's features, and minimize costs. Whether it's a simple data upsizing to a complete rewrite of the application to Microsoft .NET or SharePoint, we can help with our world class Microsoft Access and Visual Studio experts on ASP.NET, C#, and VB.NET.

Learn more about our:

• Microsoft SQL Server Consulting Services
• Visual Studio .NET programmers for Internet and Windows development
• General Microsoft Access consulting services

Headquartered in Tysons Corner in Northern Virginia, FMS has completed Access to SQL Upsizing projects and general programming services for commercial and government organization across the country from Alaska to California to Florida to New England. Of course, many of our customers are located locally in the Washington DC metropolitan region including Virginia and Maryland. Contact us today to discuss your needs.