Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
When you connect to SQL Server or SQL Azure, you need to specify a target database for migration. If you have multiple Access databases you can map them to multiple SQL Server databases (or schemas) or to multiple schemas under the connected Azure SQL Database.
SQL Server databases use the concept of schemas to separate objects within a database into logical groups. For example, a library database could use three schemas named books, audio, and video to separate book, audio, and video objects from each other. By default, the access database is mapped to master database and dbo schema in SQL Server and to connected database and dbo schema in SQL Azure.
Unless you customize the mapping between each Access database and the SQL Server database and schema, SSMA will migrate all the schemas and data associated with the access database to the default database mapped.
SSMA lets you map each Access database to SQL Server or Azure SQL Database. The following procedure describes how to customize the mapping per database.
To modify the target database and schema
In the Access Metadata Explorer pane, select access-metadata.
Schema mapping is also available when you select the Databases node or any database node. The schema mapping list is customized for the selected object.
In the right pane, click the Schema Mapping tab.
You will see a table containing access database names and its corresponding ssNoVersion or Sql Azure schema. The target schema is denoted in a two part notation (database.schema).
Select the row that contains the mapping you want to customize, and then click Modify.
In the Choose Target Schema dialog box, you may browse for available target database and schema or type the database and schema name in the textbox in a two part notation (database.schema) and then click OK.
Modes of Mapping
You can map source database to any target database. By default source database is mapped to target SQL Server database with which you have connected using SSMA. If the target database being mapped does not exist on SQL Server, then you will be prompted with a message "The Database and/or schema does not exist in target SQL Server metadata. It would be created during synchronization. Do you wish to continue?" Click Yes. Similarly, you can map schema to non-existing schema under target SQL Server database which will be created during synchronization.
You can map the source database to the connected target SQL Server database or to the any schema in the connected target SQL Server database. If you map source Schema to any non-existing schema under connected target database, then you will be prompted with a message "Schema does not exist in target metadata. It would be created during synchronization. Do you wish to continue? " Click Yes.
If you customize the mapping between an Access database and a SQL Server or Azure SQL Database, you can revert the mapping back to the database that you specified when you connected to SQL Server or SQL Azure.
To reset to default database and schema
The next step in the migration process is converting database objects
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayTraining
Module
Migrate SQL Server workloads to Azure SQL Database - Training
Learn the essentials of Azure SQL Database deployment and migration. Explore its benefits, exclusive features, and migration options while optimizing performance and application connections for a smooth transition to the cloud.
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.