Convert MDF to MDB

A legacy Microsoft Access application may require an .mdb file instead of a SQL Server database, or a database may need to be delivered to a user without SQL Server. This is a database migration: the SQL Server data and selected schema must be exported into an Access database, not renamed from .mdf to .mdb.

What the MDF format is

MDF is SQL Server's primary data-file format. It stores tables, indexes, views, stored procedures, and other database objects for Microsoft SQL Server, including SQL Server Express and LocalDB. A database commonly also has an .ldf transaction-log file and may contain additional .ndf data files.

SQL Server normally opens an MDF through a compatible server instance; file utilities and Access cannot reliably read it as a standalone database. Attach the database with all required files, or restore a SQL Server backup, before starting the migration. A newer SQL Server database might not attach to an older server version.

What the MDB format is

MDB is the Microsoft Access 2000–2003 database format, based on the Jet 4.0 database engine. It is used by older Access applications and programs that support the Jet or ACE OLE DB and ODBC providers.

An MDB file is convenient as a self-contained desktop database, but it has a 2 GB maximum file size and does not support every SQL Server data type, security feature, server-side object, or concurrency pattern. Access versions from 2007 onward can usually create or save an older Access 2000–2003-format database, subject to installed drivers and application compatibility.

How to convert MDF to MDB

The most controlled method is the SQL Server Import and Export Wizard. The wizard is installed with applicable SQL Server client or Integration Services components; SSMS alone does not necessarily install it. Microsoft Access can import SQL Server tables directly, but it provides less control over a large migration.

1. Install a SQL Server version compatible with the source database. SQL Server Express is sufficient for many migrations. Install SSMS for administration and the Import and Export Wizard if it is not already available.
2. In SSMS, connect to the instance. Right-click Databases → Attach, select the MDF, and verify the paths for its LDF and any NDF files. The SQL Server service account must have read and write access to the database files. If a .bak backup exists, use Databases → Restore Database instead.
3. Right-click the attached database and select Tasks → Export Data, or start the SQL Server Import and Export Wizard separately.
4. Choose the SQL Server Native Client or Microsoft OLE DB Driver for SQL Server as the source. Select the server, authentication method, and source database.
5. Choose Microsoft Access as the destination. Select the Microsoft ACE OLE DB provider, create a destination file with the .mdb extension, and select the Access 2000–2003 or Jet 4.0 format when the provider offers that choice. The provider's 32-bit or 64-bit architecture must match the wizard.
6. Select Copy data from one or more tables or views. Use Edit Mappings to select objects, rename tables, change destination types, map columns, and preserve identity values where supported. A view is normally exported as its result set, not as an equivalent Access query definition.
7. Run the package, review its execution report, open the MDB in Access, and compare row counts and key values. Test relationships, indexes, dates, Unicode text, decimal precision, null values, binary fields, identity columns, and representative queries.

For a smaller migration, create a blank MDB in Access and use External Data → New Data Source → From Database → From SQL Server. Choose to import the required tables rather than link them if the result must be self-contained. Access can also save a newer database as an Access 2000–2003 file through File → Save As, but imported SQL Server objects still require type and schema review.

For recurring migrations, a desktop product such as Full Convert Enterprise can automate SQL Server-to-Access transfers after the MDF database has been attached to SQL Server; it is not a utility that safely converts an inaccessible MDF by changing its extension. Check its current provider and MDB-output support before deployment.

Compatibility and quality limits

The export transfers relational data and whatever table structure the selected mappings support. Stored procedures, triggers, SQL Server Agent jobs, permissions, linked servers, full-text indexes, and many SQL Server-specific views require manual recreation or have no MDB equivalent.

Review conversions involving nvarchar(max), varbinary(max), xml, geography, hierarchyid, datetime2, computed columns, and high-precision decimal values. Access may map these to Long Text, OLE Object, ordinary text or binary fields, Date/Time values with lower precision, or unsupported types. SQL Server decimal precision can exceed the precision available in an Access field.

Verify foreign keys and indexes instead of assuming that the export reproduced them. Large tables may exceed the MDB 2 GB limit, and Access has lower field, row, query, and multi-user concurrency limits than SQL Server. Keep the original MDF, LDF, and backup unchanged until validation is complete.

Browser-based converters are generally unsuitable for MDF-to-MDB migration. A service would need access to the database contents and would not reliably preserve SQL Server objects, permissions, relationships, or server-side behavior; uploading confidential production data also creates a security risk. Use a local SQL Server instance, Access, the Import and Export Wizard, or a desktop migration utility instead.