Article ID: 281950 - View products that this article applies to.
This article was previously published under Q281950
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.
This article applies only to a Microsoft Access database (.mdb).
When upsizing to an existing Access database, the tables are unexpectedly upsized to the Master database on your SQL Server.
When upsizing to an existing database, the Upsizing Wizard opens a Select Data Source dialog box to identify a data source pointing to the destination SQL Server. By default, the destination database in a NEW Data Sources is the SQL Server Master database. If not changed, the tables will be upsized to the Master database.
To move the objects from the Master database into a different SQL Server database, you can copy the objects by using Data Transform Services from SQL Server Enterprise Manager to a different SQL Server database. Then, you can delete the upsized tables, views, and stored procedures from the Master database.
If you do not have access to Enterprise Manager, you can create an Access project (.adp) that connects to the Master database. Then you can open that project and manually delete the tables, views, and stored procedures. Next, modify the Data Source to point to the database you want to upsize to instead of the Master database. Finally, run the Upsizing wizard again to create the tables, views, and stored procedures in the correct database.
To prevent this from happening again, change the default database before you finish upsizing. After selecting the SQL Server driver in the Select a Data Source dialog box, the Create a New Data Source to SQL Server dialog box is displayed. After the Data Source Name, Description, and SQL Server name are specified, clicking Next allows the security type to be selected. Clicking Next again, presents an option to change the default database. The SQL Server database Master is the selected default. To upsize to a different database, click to select the Change the default database to the check box, and select the appropriate SQL Server destination database.
For more information about the Data Transform Services, search for DTS Overview in the SQL Server Books Online.
Contact us for more help
Connect with Answer Desk for expert help.