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.
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.
All tables, relationships, and indexes move to SQL Server for better performance and scalability.
Primary keys, foreign keys, and referential integrity constraints are preserved and enforced at the database level.
Can be converted to SQL Server views or stored procedures for faster execution.
All existing forms, reports, and user interfaces remain exactly as they are.
Your automation, macros, and business logic continue to work without changes.
Users continue using the same familiar Access interface—no retraining required.
Before starting, assess your database for compatibility issues:
Verify current size and identify tables that will benefit most from migration.
Identify Access-specific data types (AutoNumber, Yes/No, Memo) that need special handling.
Map all table relationships, foreign keys, and referential integrity rules.
Benchmark query performance to compare against post-migration results.
Set up your SQL Server instance (Express, Standard, or Azure SQL):
Install SQL Server Express (free) or Standard edition on your server or use Azure SQL Database.
Create a new database with appropriate collation settings (usually SQL_Latin1_General_CP1_CI_AS).
Set up Windows Authentication or SQL Authentication based on your security requirements.
Grant appropriate permissions to users who will access the database.
Microsoft Access includes a built-in Upsizing Wizard to automate the migration:
Open your Access database in Access 2016 or later.
Go to Database Tools → Move Data → SQL Server. This opens the Upsizing Wizard.
Choose which tables to migrate. Start with non-critical tables for testing.
Select your SQL Server instance and database name.
Choose to link tables (recommended) or export data. Linking keeps the connection active.
Execute the wizard and review the migration report for any errors or warnings.
Access data types don't always map perfectly to SQL Server. Here's how to handle common conversions:
AutoNumber fields become INT IDENTITY(1,1) in SQL Server. The wizard handles this automatically.
Yes/No fields convert to BIT (0 or 1). Access forms will still display checkboxes correctly.
Memo fields become NVARCHAR(MAX) to support Unicode text of any length.
Currency fields map to MONEY data type, preserving precision.
Date/Time fields become DATETIME2 for better precision and range.
After migration, Access creates linked tables that connect to SQL Server. Verify the links:
Linked tables show a globe icon in the Navigation Pane.
Open each linked table to verify data displays correctly.
If needed, use External Data → Linked Table Manager to refresh connections.
For manual configuration, connection strings use ODBC format: Driver={SQL Server};Server=YourServer;Database=YourDB;Trusted_Connection=yes;
Queries that worked in Access may need optimization for SQL Server:
Create indexes on frequently queried columns, especially foreign keys and date fields.
Complex Access queries can be converted to SQL Server views for better performance.
For heavy calculations, create stored procedures and call them from Access VBA.
Compare query execution times before and after migration.
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;
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.
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.
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.
Always perform a full migration in a test environment before touching production data.
Create full backups of your Access database and SQL Server before and after migration.
Start with non-critical tables, verify functionality, then migrate remaining tables.
Keep detailed notes of all data type conversions, query changes, and configuration settings.
While the interface stays the same, inform users about potential performance improvements and any new features.
Test every form to ensure data displays and saves correctly.
Run all reports to confirm data accuracy and formatting.
Test all VBA procedures, especially those that interact with tables or queries.
Track query execution times and user feedback for the first few weeks.
Document the new SQL Server connection details and any changes made during migration.
For more advanced topics, consider learning about SQL Server stored procedures, optimizing linked table performance, and implementing connection pooling for better scalability.
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 →