Upsize Access Tables to SQL Server

Microsoft Professional Advisory Services is a support option that provides short-term, proactive, consultative support beyond break-fix product maintenance needs. This includes working with the same technician for assistance with issues like product migration, code review, or new program development and is a remote, phone-based support option. This service is typically used for shorter engagements, and is designed for developers and IT professionals who do not require the traditional onsite consulting or sustained account management services that are available from other Microsoft support options.

For additional information on Microsoft Advisory Services, including on how to engage, refer to this Microsoft web page:

http://support.microsoft.com/gp/AdvisoryService

This Advisory Services scenario is designed to assist customers with upsizing their Access database tables from an .accdb or .mdb database file to SQL Server and then assist them with linking their frontend Access database file to these backend SQL tables via an ODBC connection. This will allow customers to continue to use their Access database applications while also allowing them to host their data on a SQL Server to optimize database and application performance, scalability, availability, security, reliability, and recoverability.

Key deliverables

• Install SQL Server Express if it is applicable.
• Database will be reviewed for known upsizing compatibility issues.
• Access tables will be upsized to SQL Server tables.
• Access database will be updated to contain linked tables to the SQL Server database.
• Will ensure that upsized tables are assessable and updatable from the Access client.

Scenarios

Statement of Work for Upsizing Access Data to SQL Server

Scoping questions

• Do you have Microsoft SQL Server available to use with your Access database?
• If the answer is "yes," is it already installed?
• Do you have someone in your organization that is familiar with SQL Server Management Studio to maintain the tables when they are upsized?
• Is there someone in your organization that can administer the tables when they are upsized to SQL?
• How large is your Access database � what size is the .mdb file?
• How many tables exist within your Access database?
• How many reports exist in your database?
• How many forms exist in your database?
• How many queries exist in your database?
• How many macros exist in your database?
• How many lines of code exist in your Visual Basic project file?

Included with scope

• Database will be reviewed for known upsizing compatibility issues.
• Install SQL Server Express if it is needed.
• Access tables will be upsized to SQL Server tables.
• Access database will be updated to contain linked tables to the SQL Server database.
• Will ensure that upsized tables will be assessable and updatable from the Access client.
• Basic functionality testing is performed to ensure basic connectivity and operation against the SQL Server.

Out of scope

• In depth data analysis is not included.
• Only very basic functionality testing is performed to ensure basic connectivity and operation against the SQL Server. Further rigorous testing should be completed by the developer before putting the solution into production.
• SQL Server Training will not be provided. The developer will need to have or obtain basic SQL Server knowledge on their own.
• SQL Server Security will not be provided. We will create a basic domain admin account on the SQL Server to use, if there are no other accounts already setup to use. We will not be creating multiple SQL accounts and going over SQL Server security.
• We will not be providing ongoing application support. This will need to be done via the developer, a consultant, or by opening break / fix incidents

Below is a list of self-help resources or this scenario. These resources may also be used by Microsoft Support Engineers during an Advisory Services engagement.

Referenced articles

When to upsize a Microsoft Access database to Microsoft SQL Server

Before you upsize a Microsoft Access database

Use the Upsizing Wizard

294407"Access 2002 Upsizing Tools" white paper is available in Download Center

Access migration using SSMA (Whitepaper)

Other general articles

When to Migrate from Microsoft Access to Microsoft SQL Server

When to upsize a Microsoft Access database to Microsoft SQL Server

Use Access with a database server

Before you upsize a Microsoft Access database

Choose how to upsize your application

Building a SQL Server System Architecture to Support Multiple Migrated Microsoft Access Databases

How to Migrate from Access to SQL Server 2000

237980 INF: How to Convert an Access Database to SQL Server

SQL Server Migration Assistant SQL Server Migration Assistant for Access (SSMA Access)

Access migration using SSMA (Whitepaper)

Learn About SSMA for Access

SQL Server Migration Assistant (SSMA) for Microsoft Access (Download)

294407 Upsizing Wizard "Access 2002 Upsizing Tools" white paper is available in Download Center

330468 HOW TO: Use the Microsoft Access Upsizing Wizard

Converting an Access Database with the Upsizing Wizard

Use the Upsizing Wizard

Move Access data to a SQL Server database by using the Upsizing Wizard

130166 Data Type Mapping in the Upsizing Wizard

325017 HOW TO: Use the Microsoft Access Upsizing Wizard

330468 HOW TO: Use the Microsoft Access 2002 Upsizing Wizard

328319 Issues with the Access 2002 Upsizing Wizard

Optimization

Optimizing Microsoft Office Access Applications Linked to SQL Server

892490 How to create a DSN-less connection to SQL Server for linked tables in Access

Troubleshooting

Troubleshooting Performance Problems in SQL Server 2008

294398 Tables Not Upsized When Query or Table Name Contains Apostrophe

295231 Upsizing Wizard fails to upsize data in large tables

838594 Error message when you try to upsize your Access database by using the Upsizing Wizard: "The Upsizing Wizard only works with Microsoft SQL Server (Versions 6.50 SP5 or higher). Please log in to a SQL Server data source."

288300 "Microsoft Access Can't Find the Wizard..." Error [...]

282380 Bad File Name or Number [...]

281950 Database Unexpectedly Upsized [...]

272384 "Overflow" Error Message When You Try [...]

269824 Incompatibility Issues Between Access 2000 Projects [...]

165827 "Overflow" or "Division by Zero" Error Upsizing Table

153034 Table Is Not Exported Using the Upsizing Tools

244309 MOD2000: Permissions Required to Upsize a Microsoft Access Database to SQL Server

Upsizing Design Issues

Additional upsizing resources

http://office.microsoft.com/en-us/access/results.aspx?qu=upsize&sc=9&av=ZAC110

http://office.microsoft.com/en-us/access/results.aspx?qu=upsize&sc=9&av=ZAC120

http://msdn.microsoft.com/en-us/library/bb545450(v=MSDN.10).aspx

http://technet.microsoft.com/en-us/library/bb418440(SQL.10).aspx

http://blogs.msdn.com/sqlexpress/articles/616581.aspx

http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx

http://msdn.microsoft.com/en-us/sqlserver/default.aspx

http://www.fmsinc.com/MicrosoftAccess/Strategy/index.asp

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.