When your Access database approaches the 2GB limit or performance degrades with multiple concurrent users, migrating the backend to SQL Server while keeping your Access front-end is an effective solution. This tutorial walks you through the complete process of upsizing your Access database to SQL Server without breaking existing forms, reports, or VBA code.
Understanding Access Upsizing
Upsizing (also called split-database migration) means moving your Access data tables to SQL Server while keeping your Access front-end (forms, reports, VBA) intact. The front-end connects to SQL Server using ODBC or OLE DB, giving you enterprise-level performance without changing the user interface.
What Gets Migrated vs. What Stays
Migrated to SQL Server
Data Tables
All tables, relationships, and indexes move to SQL Server for better performance and scalability.
Data Integrity
Primary keys, foreign keys, and referential integrity constraints are preserved and enforced at the database level.
Complex Queries
Can be converted to SQL Server views or stored procedures for faster execution.
Remains in Access Front-End
Forms & Reports
All existing forms, reports, and user interfaces remain exactly as they are.
VBA Code
Your automation, macros, and business logic continue to work without changes.
User Experience
Users continue using the same familiar Access interface—no retraining required.
Step-by-Step Migration Process
Step 1: Pre-Migration Assessment
Before starting, assess your database for compatibility issues:
Check Database Size
Verify current size and identify tables that will benefit most from migration.
Review Data Types
Identify Access-specific data types (AutoNumber, Yes/No, Memo) that need special handling.
Document Relationships
Map all table relationships, foreign keys, and referential integrity rules.
Test Current Performance
Benchmark query performance to compare against post-migration results.
Step 2: Prepare SQL Server
Set up your SQL Server instance (Express, Standard, or Azure SQL):
Install SQL Server
Install SQL Server Express (free) or Standard edition on your server or use Azure SQL Database.
Create Database
Create a new database with appropriate collation settings (usually SQL_Latin1_General_CP1_CI_AS).
Configure Authentication
Set up Windows Authentication or SQL Authentication based on your security requirements.
Set Permissions
Grant appropriate permissions to users who will access the database.
Step 3: Use Access Upsizing Wizard
Microsoft Access includes a built-in Upsizing Wizard to automate the migration:
Open Your Database
Open your Access database in Access 2016 or later.
Launch Wizard
Go to Database Tools → Move Data → SQL Server. This opens the Upsizing Wizard.
Select Tables
Choose which tables to migrate. Start with non-critical tables for testing.
Choose SQL Server
Select your SQL Server instance and database name.
Configure Options
Choose to link tables (recommended) or export data. Linking keeps the connection active.
Run Migration
Execute the wizard and review the migration report for any errors or warnings.
Step 4: Handle Data Type Conversions
Access data types don't always map perfectly to SQL Server. Here's how to handle common conversions:
AutoNumber → IDENTITY
AutoNumber fields become INT IDENTITY(1,1) in SQL Server. The wizard handles this automatically.
Yes/No → BIT
Yes/No fields convert to BIT (0 or 1). Access forms will still display checkboxes correctly.
Memo → NVARCHAR(MAX)
Memo fields become NVARCHAR(MAX) to support Unicode text of any length.
Currency → MONEY
Currency fields map to MONEY data type, preserving precision.
Date/Time → DATETIME2
Date/Time fields become DATETIME2 for better precision and range.
Step 5: Update Linked Tables
After migration, Access creates linked tables that connect to SQL Server. Verify the links:
Check Table Icons
Linked tables show a globe icon in the Navigation Pane.
Test Connections
Open each linked table to verify data displays correctly.
Refresh Links
If needed, use External Data → Linked Table Manager to refresh connections.
Update Connection Strings
For manual configuration, connection strings use ODBC format: Driver={SQL Server};Server=YourServer;Database=YourDB;Trusted_Connection=yes;
Step 6: Optimize Query Performance
Queries that worked in Access may need optimization for SQL Server:
Add Indexes
Create indexes on frequently queried columns, especially foreign keys and date fields.
Convert to Views
Complex Access queries can be converted to SQL Server views for better performance.
Use Stored Procedures
For heavy calculations, create stored procedures and call them from Access VBA.
Test Query Speed
Compare query execution times before and after migration.
Common Migration Challenges and Solutions
Challenge 1: AutoNumber Field Issues
Problem: AutoNumber fields may not increment correctly after migration.
Solution: Ensure the IDENTITY seed and increment are set correctly. In SQL Server, use: ALTER TABLE YourTable ALTER COLUMN IDColumn INT IDENTITY(1,1) NOT NULL;
Challenge 2: Query Syntax Differences
Problem: Some Access SQL syntax doesn't work in SQL Server (e.g., IIF, DateAdd with different syntax).
Solution: Update queries to use SQL Server syntax. Replace IIF with CASE WHEN, and verify DateAdd syntax matches SQL Server standards.
Challenge 3: Connection String Configuration
Problem: Users can't connect to SQL Server from their Access front-ends.
Solution: Ensure SQL Server allows remote connections, configure firewall rules, and use consistent authentication (Windows or SQL). Test connection strings on each user's machine.
Challenge 4: Form Performance Issues
Problem: Forms load slowly after migration.
Solution: Use pass-through queries for large datasets, limit record sources to necessary fields only, and add WHERE clauses to reduce data transfer.
Best Practices for Successful Migration
Test in Development First
Always perform a full migration in a test environment before touching production data.
Backup Everything
Create full backups of your Access database and SQL Server before and after migration.
Migrate in Phases
Start with non-critical tables, verify functionality, then migrate remaining tables.
Document Changes
Keep detailed notes of all data type conversions, query changes, and configuration settings.
Train Users
While the interface stays the same, inform users about potential performance improvements and any new features.
Post-Migration Checklist
Verify All Forms Work
Test every form to ensure data displays and saves correctly.
Test All Reports
Run all reports to confirm data accuracy and formatting.
Check VBA Code
Test all VBA procedures, especially those that interact with tables or queries.
Monitor Performance
Track query execution times and user feedback for the first few weeks.
Update Documentation
Document the new SQL Server connection details and any changes made during migration.
Additional Resources
For more advanced topics, consider learning about SQL Server stored procedures, optimizing linked table performance, and implementing connection pooling for better scalability.
Don't Risk Data Loss During Migration
We offer a 'Migration Assessment' to scan your database for compatibility issues. Get a detailed analysis of potential risks and a clear migration roadmap before you commit.
Get Your Migration Assessment →