# Microsoft Access to SQL Server Migration: Why Your Access DB Will Break at 2GB Microsoft Access has a hard 2GB file size limit. Not a recommendation. Not a best practice guideline. A hard ceiling baked into the Jet and ACE database engines that Microsoft has never raised sinc... ## Microsoft Access to SQL Server Migration: Why Your Access DB Will Break at 2GB Access database migration to SQL Server, PostgreSQL, and modern platforms — from a Zeeland, MI company that has migrated hundreds of Access databases since 2004. We extract your tables, queries, forms, reports, VBA modules, and linked table chains into production-grade systems that handle 10,000+ concurrent users instead of 10. --- ## Our Process 1. **Access Database Audit & Dependency Mapping (1–2 Weeks)** — We open every MDB and ACCDB in your environment and catalog the full scope: table count, row counts, total data volume, data types requiring special migration handling, query count and complexity, VBA module line count, form and report inventory, linked table chains with source paths, ODBC connections, external references, and macro definitions. We identify every dependency — which forms use which queries, which queries reference which tables, which VBA modules call which forms, which reports depend on which parameter queries. Deliverable: a comprehensive migration scope document with complexity rating per component, risk assessment, recommended migration sequence, and detailed cost/timeline estimate. 2. **Schema Design & Data Type Mapping (1–2 Weeks)** — We design the SQL Server schema based on your Access table structures but optimized for SQL Server. This is not a direct copy. Access databases almost never have proper foreign key relationships defined (they rely on the Relationships window which is informational, not enforced by default). We normalize tables that Access developers denormalized for form convenience, add proper constraints, design indexes based on your actual query patterns, map every data type with documented conversion rules, and create the migration scripts. We review the schema with your team before any data moves. 3. **Data Migration & Validation (1–3 Weeks)** — We migrate the data in a staged process: schema creation, bulk data load, constraint application, and row-by-row validation. Access data has quirks that cause migration failures if not handled: null strings vs empty strings (Access treats them interchangeably, SQL Server does not), trailing spaces in Text fields, date values outside SQL Server's datetime range (Access stores dates from 100 AD, SQL Server datetime starts at 1753), and currency precision differences. We write validation queries that compare Access source data against SQL Server target data for every table, every column, every row. The migration is not complete until validation shows zero discrepancies. 4. **Query & Business Logic Migration (2–6 Weeks)** — Every Access query gets translated to T-SQL and validated for output equivalence. Make-table queries become SELECT INTO or stored procedures. Append queries become INSERT statements. Update queries and Delete queries get translated with careful attention to Access-specific join behavior (Access handles outer joins in WHERE clauses differently than SQL Server). VBA business logic gets extracted, documented, and reimplemented as stored procedures, SQL Server functions, application-layer code, or a combination depending on where the logic belongs architecturally. We test every migrated query and procedure against the original Access output. 5. **Front-End Replacement & Report Migration (3–8 Weeks)** — Access forms become web or desktop application interfaces. Access reports become SSRS reports, Power BI dashboards, or web-based reports with PDF export. We rebuild the UI module by module, matching the existing workflow so your users do not need extensive retraining. Each module gets a user acceptance testing period where your team verifies that the new interface does everything the Access form did — same fields, same validations, same navigation flow, same output. We iterate based on feedback until your team signs off. 6. **Parallel Running, Cutover & Decommission (2–4 Weeks)** — Old and new systems run simultaneously with data synchronization. Your team works in both environments, comparing results and flagging discrepancies. Once a module passes parallel validation, we cut it over and disable the Access equivalent. After all modules are cutover and the team has operated solely on the new system for a minimum of two weeks, we archive the Access databases (read-only, backed up) and decommission them. Post-migration support runs 60 days to catch any edge cases that did not surface during parallel running. --- ## Frequently Asked Questions ### How much does it cost to migrate an Access database to SQL Server? Migration cost depends on four factors: table count and data volume, query complexity and count, VBA codebase size, and whether you need new forms and reports or can temporarily keep the Access front end linked to SQL Server via ODBC. A simple migration — under 50 tables, minimal VBA, no form/report replacement — typically runs $8,000–$20,000. This covers schema design, data type mapping, data migration, query translation, and validation. A mid-complexity migration — 50–200 tables, moderate VBA (1,000–5,000 lines), some form replacement — runs $25,000–$75,000. Complex migrations — 200+ tables, extensive VBA codebases (10,000+ lines), full form and report replacement, multiple linked backend files, ODBC connections to other systems — run $75,000–$200,000+. The single biggest cost variable is form and report replacement. If you can keep the Access front end temporarily while moving the backend to SQL Server, you cut the initial migration cost by 40–60%. We recommend this phased approach for most clients: move the data first, replace the UI second. ### Can we keep using the Access front end after migrating data to SQL Server? Yes, and this is the approach we recommend for most migrations. Access can use ODBC linked tables to connect forms and reports to SQL Server backend tables instead of local MDB tables. Your users keep the same forms, the same reports, the same workflow — but the data lives in SQL Server with all the performance, security, and scalability benefits. This is the fastest and cheapest first phase of migration. The catches: some Access-specific features do not work over ODBC. Certain action queries need modification. Form performance changes because network round-trips replace local file reads. Pass-through queries may be needed for complex operations. VBA code that manipulates tables directly needs updating to use the ODBC connection. We handle all of this during the migration. The long-term plan should still be replacing the Access front end — Microsoft's investment in Access has been minimal for years, and every Office update risks breaking something — but ODBC linking buys you time to plan and budget the front-end replacement separately. ### How long does an Access to SQL Server migration take? Timeline follows the same complexity tiers as cost. Simple migrations (under 50 tables, minimal VBA, no UI replacement) take 3–6 weeks from kickoff to production cutover. Mid-complexity migrations (50–200 tables, moderate VBA, partial form replacement) take 2–4 months. Complex migrations (200+ tables, extensive VBA, full UI replacement) take 4–8 months. These timelines include discovery, schema design, data migration, query translation, VBA reimplementation, testing, parallel running, and cutover. The parallel running phase alone takes 2–4 weeks minimum — we do not skip it regardless of project size. The most common timeline risk is VBA reverse-engineering. When the original developer is gone and the code is undocumented, understanding what 10,000 lines of VBA actually do takes longer than rewriting it. We always pad the estimate for VBA discovery because there are invariably business rules buried in event handlers that nobody remembers exist until they stop working. ### Our Access database has been running fine for 15 years. Why migrate now? Because Access databases do not fail gradually. They fail catastrophically, and the failure usually comes without warning. The 2GB file size corruption is the most dramatic example, but it is not the only one. Network interruptions during write operations corrupt the file. Windows updates change SMB protocol behavior and break linked tables. Office updates modify the ACE engine and break VBA code. Microsoft has not made a significant investment in the Access database engine in over a decade — the product is in maintenance mode. Each year you run on Access, you accumulate risk: the file grows larger, the VBA codebase becomes more fragile, the developer knowledge becomes more diffuse, and the blast radius of a failure becomes more catastrophic because more business processes depend on it. Companies that migrate proactively spend 30–50% less than companies that migrate reactively after a corruption event, because reactive migrations are emergency projects with compressed timelines, data recovery costs, and lost productivity during downtime. ### What about migrating to PostgreSQL or MySQL instead of SQL Server? PostgreSQL is an excellent migration target and we recommend it for companies that want to avoid SQL Server licensing costs, are already running Linux infrastructure, or plan to deploy to cloud platforms where PostgreSQL is a first-class managed service (AWS RDS, Azure Database for PostgreSQL, Google Cloud SQL). PostgreSQL handles everything SQL Server does for typical Access migration scenarios: full ACID compliance, row-level security, comprehensive audit logging, point-in-time recovery, and tens of thousands of concurrent connections. The trade-offs: SQL Server has better integration with the Microsoft ecosystem (SSRS, Power BI, Azure, .NET), which matters if your company is already a Microsoft shop. PostgreSQL has no licensing cost, which matters significantly for companies running multiple database instances. MySQL is a viable option for simpler databases but lacks some of the advanced features (row-level security, lateral joins, advanced window functions) that complex Access migrations need. We assess your infrastructure, budget, and technical requirements and recommend the target platform during the discovery phase. ### Will our reports and queries work the same after migration? They will produce identical results, but the underlying code will be different. Access SQL and T-SQL (SQL Server) are different dialects. Every query gets manually translated and then validated by comparing output against the original Access query results row-by-row, column-by-column. Common translation issues we handle: Access uses IIF() for inline conditionals while SQL Server uses CASE WHEN. Access Nz() for null handling becomes ISNULL() or COALESCE(). Access crosstab queries (TRANSFORM/PIVOT) have completely different syntax in T-SQL. Access parameter queries that reference form controls need conversion to stored procedure parameters. Access-specific string concatenation using & needs to become + or CONCAT(). Date functions (DateSerial, DateDiff, DatePart) use different syntax and interval codes. Wildcard characters differ (Access uses * and ?, SQL Server uses % and _). Every one of these translations is tested. We do not ship a migrated query until it produces byte-identical output to the Access original. --- **Canonical URL**: https://freedomdev.com/solutions/access-database-migration _Last updated: 2026-05-12_